If statement using two dates

Options

This has got to be simpler than I am making it.

Column1 is a manufacturer date

Column 2 is a Replacement date (calculated by adding 1825 days to column 1)

Column 3 needs to be Fiscal year. So If Column 2 is greater than or equal to 10/1/2025 and less than or equal to 9/30/2026 then FY26

Help here?

Best Answer

  • topazfae
    topazfae ✭✭✭
    Answer ✓
    Options

    You need to make sure your column are set as date. For Column 1 which usually is primary column, and you can't change that type to date. It always will be in Text/Number.

    In another word -

    Column 2 is your Manufacturer date, Column 3 is your Replacement date 2. Make sure both columns are date based.

    Then in column 3, use this formula - = =[Manufacturer date]@row + 1825

    In Column - Fiscal Year - you can use the following formula -

    ="FY" + RIGHT(YEAR([Replacement date]@row) + IF([Replacement date]@row >= DATE(YEAR([Replacement date]@row), 10, 1), 1, 0), 2)

Answers

  • topazfae
    topazfae ✭✭✭
    Answer ✓
    Options

    You need to make sure your column are set as date. For Column 1 which usually is primary column, and you can't change that type to date. It always will be in Text/Number.

    In another word -

    Column 2 is your Manufacturer date, Column 3 is your Replacement date 2. Make sure both columns are date based.

    Then in column 3, use this formula - = =[Manufacturer date]@row + 1825

    In Column - Fiscal Year - you can use the following formula -

    ="FY" + RIGHT(YEAR([Replacement date]@row) + IF([Replacement date]@row >= DATE(YEAR([Replacement date]@row), 10, 1), 1, 0), 2)

  • Gonzo
    Gonzo ✭✭
    Options

    Thanks, Topzafae. The column 1 and column2 reference was just a reference. I got the Primary and date stuff as mentioned. Just needed help on the actual formula.


    I appreciate the help very much. Works great. It's a very different approach than I was thinking, but I am glad for it. it's dynamic and will work in perpetuity. Much better than the way I was approaching it.

  • Shonnify
    Options

    I have to change dates each quarter when I create a new project plan quarter to quarter. Its not exact but these work a little for me:

    =[start date]@row+90

    =[finish date]@row+90

    Add date column, add formula, then copy paste special in original date column, and delete the new one. 

    You may have to increase the day by one or two days to get it to sync up (90, 91, 92, etc.)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!