# Prorate Required Training hours based on Start Date

Options
✭✭
edited 07/20/23

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.

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

Hey @SB MS

Can you share some screenshots please?

Itai Perez

Reporting and Project Manager

If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

• ✭✭✭✭✭✭
Options

You would use pretty much the same syntax. When you say it is not working, are you getting an error, or are you getting an unexpected number?

• ✭✭
Options

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