IF Function

Overview: Trying to calculate the years of service for an employee sheet that contains two columns:

DOH = Date of Hire

DOT = Date of Termination (Note: most values in this field are blank)

Unable to figure out how to use the IF function when I'm comparing a Date of Termination (DOT) field that is populated to allow the if true & if false formulas to properly calculate.

With the formula below, if will calculate if the DOT is empty, but give an #INVALID OPERATION response if there is a value (i.e. date) in the DOT field.

=IF(DOT3 > 0, (DOT3 - DOH3) / 365, (TODAY() - DOH3) / 365)

I've tried using ISBLANK with similar results. Not sure what I'm missing here.

Tags:

Best Answer

  • Just_Chris
    Just_Chris ✭✭
    Answer ✓

    Hey Bert,

    Using column titles DOT, DOH, Today (these characters) I came up with the following - which worked for me.

    If you have these 3 same column titles (make sure your calculation column isnt set as date) this formula you can likely simply copy from here and paste in:

    =IF(ISBLANK(DOT@row), (Today@row - DOH@row) / 365, (DOT@row - DOH@row) / 365)


    If it works dont forget to right click cell with formula and "set to column"

    Let me know what you think.

Answers

  • Just_Chris
    Just_Chris ✭✭
    Answer ✓

    Hey Bert,

    Using column titles DOT, DOH, Today (these characters) I came up with the following - which worked for me.

    If you have these 3 same column titles (make sure your calculation column isnt set as date) this formula you can likely simply copy from here and paste in:

    =IF(ISBLANK(DOT@row), (Today@row - DOH@row) / 365, (DOT@row - DOH@row) / 365)


    If it works dont forget to right click cell with formula and "set to column"

    Let me know what you think.

  • Bert Hoffbeck
    Bert Hoffbeck ✭✭✭✭

    Thanks Chris.

    It worked w. a small modification, since I did not have a separate column for Today.

    I used the following and it worked!

    =IF(ISBLANK(DOT@row), (Today() - DOH@row) / 365, (DOT@row - DOH@row) / 365)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!