COUNTIF Formula to Count Months Between Dates

I am new Smartsheet user and I'm working to convert some Excel files we use over to Smartsheet.

I need to count the number of months an employee has been employed based on their Hire Date but if they have a Term Date the count should stop at that date.

This is the Excel formula I am replacing (I didn't write this):

=IF([Term Date]2>0, (YEAR([Term Date]2)-YEAR([Hire Date]2))*12+MONTH([Term Date]2)-MONTH([Hire Date]2), (YEAR(NOW())-YEAR([Hire Date]2))*12+MONTH(NOW())-MONTH([Hire Date]2))

I know that this Smartsheet formula will give me the number of months from the Hire Date:

=ROUND((NETDAYS([Hire Date]@row, TODAY()) / 365) * 12)

I am unsure of how to stop the count at the Term Date if it exists.

Any help is appreciated!

Best Answer

  • Paul H
    Paul H ✭✭✭✭✭✭
    edited 04/27/22 Answer ✓

    @DSSSSALF Try it this way

    =ROUND((IF(ISDATE([Term Date]@row), IF(NETDAYS([Term Date]@row, TODAY()) > 0, NETDAYS([Hire Date]@row, [Term Date]@row)), NETDAYS([Hire Date]@row, TODAY())) / 365) * 12)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!