Calculating Sick Days for New Employees

[eromadm]

I have employees hired different months of the year and I have to calculate their sick time from date of hire. For existing employees the formula we use to calculate 1/2 day for sick time each month and the formula that I use is: =month(today())/2 for year beginning in January. When I have an employee starting in other months what formula do I use to calculate 1/2 days from start of employment. Example I have employee starting in September what formula do I use.

 

[eromadm]

Yes, but we need to know the exact rules/criteria, which is what I am try to get from you!

You included my second question in your quote, but never answered it:

What if they were hired on the first day of the month?
Would they receive an accrual for that month?

If you would like my help, please answer that question, as that has a big impact on how we write the formula!

 

[eromadm]

Yes they would receive a 1/2 sick day for the month of hire

 

[Joe4]

OK, it sounds like in order to get credit for a month, they need to be there on the first day and on the last day.

Here is a proof of concept I did with various dates. The blue columns are the intermediate calculations, and the red one is the accrued sick time.

So, the formula I have in cell F3 and copied down is this:

=DATEDIF(IF(DAY(A3)>1,EOMONTH(A3,0)+1,A3),IF(B3=EOMONTH(B3,0),B3+1,EOMONTH(EDATE(B3,-1),0)+1),"m")*0.5

If you are always calculating from the current date, you can replace all instances of "B3" in that formula with "TODAY()", i.e.

=DATEDIF(IF(DAY(A3)>1,EOMONTH(A3,0)+1,A3),IF(B3=EOMONTH(TODAY(),0),TODAY()+1,EOMONTH(EDATE(TODAY(),-1),0)+1),"m")*0.5