Prorate Required Training hours based on Start Date

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.

Find more posts tagged with

Accepted answers

SB MS

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

All comments

Itai Perez

Hey @SB MS

Can you share some screenshots please?

Paul Newcome

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?

SB MS

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.