Create a date based on document type and issue date

SimonH

I have different document types and their review dates will vary according to type, 1 year or 2 years.

example, if a form or worksheet was issued today then the review date will be in 2 years. If a procedure then the review date will be 1 year from issue.

Col A is document type (currently 3, form, procedure and worksheet, ), Column B is Issued date and col C is revision due on.

Any help would be greatly appreciated.

Thanks

Kelly Moore

Hey @SimonH

Try this

IF([Document Type]@row<>"", IF([Document Type]@row="Procedure", DATE(YEAR([Issued Date]@row)+1, MONTH(Issued Date]@row, DAY(Issued Date]@row)), DATE(YEAR([Issued Date]@row)+2, MONTH(Issued Date]@row, DAY(Issued Date]@row))))

This formula first checks to make sure your Document Type column isn't blank. If not blank, it looks to see if the type is Procedure. If true, it will add 1 years to the issue date. If not true, it adds 2 years.

You will need to edit the formula above with the actual column names of your columns. Also make sure that Procedure is written exactly as shown in your dropdown list.

Will this work for you?

Kelly

SimonH

Hello Kelly,

thanks for that I will give it a go later and let you know how I get on

Simon

SimonH

https://community.smartsheet.com/discussion/comment/394005#Comment_394005

Hi Kelly,

sorry for sounding dumb but where do I enter the column names, Document Type being the first.

thanks

Simon

Kelly Moore

Hey @SimonH

I'm sorry, I should have explained that better. Overwrite the bold text with your column names IF I didn't name them correctly. If it's correct you don't have to do anything. I also highlighted the word Procedure. For this word, it must be an exact match, case sensitive, to how you have this in your dropdown column but enclosed by the double quotes. Please make sure the case is correct.

F([Document Type]@row<>"", IF([Document Type]@row="Procedure", DATE(YEAR([Issued Date]@row)+1, MONTH(Issued Date]@row, DAY(Issued Date]@row)), DATE(YEAR([Issued Date]@row)+2, MONTH(Issued Date]@row, DAY(Issued Date]@row))))

Let me know if you have any other questions, big or small.

Kelly

SimonH

https://community.smartsheet.com/discussion/comment/394011#Comment_394011

Hi Kelly,

the bold text is fine, I copy pasted to be certain.

I placed the code into a cell but not working, probably missed something, do the rows stay as you put them? Any need to specify cells?

Here is a snapshot of my sheet

I inserted this:

IF([Document Type]@row<>"", IF([Document Type]@row="Procedure", DATE(YEAR([Issued Date]@row)+1, MONTH(Issued Date]@row, DAY(Issued Date]@row)), DATE(YEAR([Issued Date]@row)+2, MONTH(Issued Date]@row, DAY(Issued Date]@row))))

into M1

Kelly Moore

Hello @SimonH

Two things

All formulas begin with an equal sign.

=IF([Document Type]@row<>"", IF([Document Type]@row="Procedure", DATE(YEAR([Issued Date]@row)+1, MONTH(Issued Date]@row, DAY(Issued Date]@row)), DATE(YEAR([Issued Date]@row)+2, MONTH(Issued Date]@row, DAY(Issued Date]@row))))

Your screenshot appears to be an excel spreadsheet, not a smartsheet. The formula is written for smartsheet. Are you planning on using smartsheet?

Kelly

SimonH

https://community.smartsheet.com/discussion/comment/394044#Comment_394044

Morning,

using smartsheet now but I get #unparseable

I imported csv file from excel

thanks

Simon

Kelly Moore

Hey @SimonH

Unparseables generally mean parentheses are missing/out of place, commas are missing/out of place and or column names are out of place.

I found a missing bracket and parentheses.

=IF([Document Type]@row <> "", IF([Document Type]@row = "Procedure", DATE(YEAR([Issued Date]@row) + 1, MONTH([Issued Date]@row), DAY([Issued Date]@row)), DATE(YEAR([Issued Date]@row) + 2, MONTH([Issued Date]@row), DAY([Issued Date]@row))))

Kelly

SimonH

https://community.smartsheet.com/discussion/comment/394222#Comment_394222

Hello Kelly

now getting INVALID COLUMN VALUE. Does it matter that I imported the lists from excel or does it need to be free text entries for document type?

thanks

Simon

Kelly Moore

Hey @SimonH

Please make sure that the column where you placed the formula is a DATE column. The formula produces a date. Also, the word "Procedure" is no where in your dropdown list. But that won't give you the error you are experiencing, it will just mean that all of your dates will always have 2 years added to them.

Can you share a screenshot of the column with the error

SimonH

https://community.smartsheet.com/discussion/comment/394276#Comment_394276

Hi Kelly,

all good now , I added Procedure to doc types and date changed as expected.

Thanks for all you help with this

Simon