Prorate Required Training hours based on Start Date

SB MS ✭✭
edited 07/20/23 in Formulas and Functions

Hi -

Does anyone know how to calculate hours needed based on start date? We have a 40 hr yearly requirement for training hours, but the yearly requirement is prorated based on each person's start date. So if they started in the middle of the year they wouldn't be required to do a full 40 hours.

I have a column that lists the start date. Is there a simple formula that can be used to calculate the hours needed based on the start date for each row?

I tried to convert this Excel formula: =INT((40 * (365 - (DATE(YEAR(A2), 12, 31) - A2))) / 365), but its not working. Also, I don't need it to be a whole number, I would prefer the fractional number.

Best Answer

  • SB MS
    SB MS ✭✭
    Answer ✓

    @Itai @Paul Newcome Thank you both for responding. I was getting an error message. #Unparseable.

    After much trial and error, I was finally able to figure it out (with some help from ChatGPT). I used this formula:

    =IF(ISDATE([Start Date]@row), IF([Start Date]@row < DATE(YEAR(TODAY()), 1, 2), 40, (NETDAYS([Start Date]@row, DATE(YEAR([Start Date]@row), 12, 31)) / 365) * 40), "")

    Thank you both for checking in!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!