Create a date based on document type and issue date

Options

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    SimonH ✭✭
    Options

    Hello Kelly,


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

    Simon

  • SimonH
    SimonH ✭✭
    Options

    Hi Kelly,

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

    thanks


    Simon

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    SimonH ✭✭
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    SimonH ✭✭
    Options

    Morning,


    using smartsheet now but I get #unparseable


    I imported csv file from excel


    thanks


    Simon

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    SimonH ✭✭
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    SimonH ✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!