Need help determining prorated days off based on 90 days after hire date

**jbrottman** · 02-26-2019, 12:41 PM

I have a feeling this is an easy one, I just can't figure it out despite some googling. I have a spreadsheet that includes hire dates and 90 day marks. If someone is not a new hire and is employed on 1/1 of the year they would be entitled to 9 days off. For new hires, that number is prorated based on the 90 day mark. So if someone's 90 day mark fell on 7/1, they would be entitled to half of 9 days, equaling 4.5 days. Can someone please fill in the blanks for me so that I can prorate the time off based on the 90 day marks listed? Thank you!

**Roel Jongman** · 02-26-2019, 01:00 PM

My solution would be..

Formula:

Please Login or Register  to view this content.

but that will give "strange" fractions of days so you might want to round it to half days or full days. Just not sure what you would want.

Annually 9 days equals 0,75 per full calander month.

so using datediff would work too

Formula:

Please Login or Register  to view this content.

but that might be a bit strong rounding as difference for apr 1 as 90 day mark = 6 days rather then 6,76 on day based calculation.

this formula will round to the nearest full hour (based on 8 hour workday)

Formula:

Please Login or Register  to view this content.

last one is most flexible for rounding, replace 1/8 by 1 for full days, or 0,5 for halfdays

**jbrottman** · 02-26-2019, 01:45 PM

Very helpful! Thank you. I like column B, =DATEDIF(B2,DATE(YEAR(B2),12,31),"M")*9/12. I appreciate your assistance.

Need help determining prorated days off based on 90 days after hire date

LinkBack

Thread Tools

Rate This Thread

Display

Need help determining prorated days off based on 90 days after hire date

Re: Need help determining prorated days off based on 90 days after hire date

Re: Need help determining prorated days off based on 90 days after hire date

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] calculate total number of days between two days based on 365 days year

[SOLVED] Determining Years EE is Active Based on Hire/Term Dates

I need to calculate monthly prorated salary for staff based on hire and termination dates

Calculate End Date Using Start Date and No. of Days excluding specific days.

changing hire date + 90 days later to the word "available"

excel formula: days remaining=end date-todays date+extention days

Excel Formula to calculate 90 days after hire date & Add Initial Pay Rate

Bookmarks

Bookmarks

Posting Permissions