COUNTIF Formula to Count Months Between Dates

Options

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 ✓
    Options

    @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)

Answers

  • Paul H
    Paul H ✭✭✭✭✭✭
    Options

    Try this

    If Term Date is a date and its in the past, use hire to term, else use hire to today.

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

  • LFaraco
    LFaraco ✭✭
    Options

    Thank you! This partially works. It works for employees who have a Term Date but for employees with no Term Date (AKA currently employed) I get an #INVALID DATA TYPE error.

    I need to display the number of months employed between Hire Date and Today if there is no Term Date and I think that's the piece missing from the formula:

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

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

    @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)

  • LFaraco
    LFaraco ✭✭
    Options

    @Paul H Yes, this works! Thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!