Help Workday Calculcations


I try to put a date in a column 5.5 months before the end of my contract.

With the DATE function, it gives an INVALID VAL code for some dates and if I try with the WORKDAY function, it gives an INCORRECT code.

=IFERROR(IF([Fin contrat]@row >= TODAY(); DATE(YEAR([Fin contrat]@row); MONTH([Fin contrat]@row) - 5; DAY([Fin contrat]@row) - 15)); DATE(YEAR([Fin contrat]@row) - 1; MONTH([Fin contrat]@row) + 8; DAY([Fin contrat]@row) - 15))

=IFERROR(IF([Fin contrat]@row >= TODAY(); WORKDAY([Fin contrat]@row; -120); ""))

Can you help me.

I apologise for the mistakes. I don't speak english well.




  • Toufong Vang
    Toufong Vang ✭✭✭✭✭


    =[Fin contrat]@row - 167)

    Below is how I arrived at 167 days for 5.5 months.

    • 365 days in 1 year.
    • 12 months in 1 year.
    • (365/12) * 5.5 = 167

    As to why your formula is failing...

    1. The expression MONTH([Fin contrat]@row) - 5 will return values <1 when the month is January - May.
    2. The expression MONTH([Fin contrat]@row) + 8 will return values >12 when the month is May - December.
    3. The expression DAY([Fin contrat]@row) - 15 will return values <0 when the day of the month is less than the 16th.

    These will three will cause the function DATE() to fail. In addition, you are using incorrect syntax for the functions. You can find documentation on Smartsheet functions here,

    Good luck!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!