Create a date based on document type and issue date
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
Answers
-
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
-
Hello Kelly,
thanks for that I will give it a go later and let you know how I get on
Simon
-
Hi Kelly,
sorry for sounding dumb but where do I enter the column names, Document Type being the first.
thanks
Simon
-
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
-
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
-
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
-
Morning,
using smartsheet now but I get #unparseable
I imported csv file from excel
thanks
Simon
-
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
-
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
-
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
-
Hi Kelly,
all good now , I added Procedure to doc types and date changed as expected.
Thanks for all you help with this
Simon
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 434 Global Discussions
- 136 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 64 Community Job Board
- 481 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
