# Calculating Accruals

1. ## Calculating Accruals

In my agency, we accrue 3.69 hours of sick time per paycheck, for a total of 96 hours over 26 paychecks. Starting on the SHIFT sheet, I have a drop-down list for employees listed on the MASTER sheet which will reference a date of hire (C2:C24 for example). If I want to determine 1) total accruals since date of hire (F) and 2) total accruals for the year to date (G), what would the calculations be? I realize that for my second request, I would probably also (I think) need to know the date of the first paycheck for that year in order to determine how many paychecks have gone out since the beginning of the year. I will be doing a sick time analysis which will determine the difference between what they have and what they should have.  Register To Reply

2. ## Re: Calculating Accruals

Is it as simple as =3.69*(TODAY()-C2) for the Total? I don't know what it would be for the YTD.  Register To Reply

3. ## Re: Calculating Accruals

Bump.

As far as the second half of the calculation, I'm thinking that I need to have, on the MASTER sheet, a space to indicate the first pay date of the year (and all 25 subsequent pay dates for the remainder) then some type of IF statement to determine where TODAY falls within those 26 values. That way, if TODAY is during the 12th pay period of the year, my only calculation would be 3.69*12.  Register To Reply

4. ## Re: Calculating Accruals

I tried this, but it is not working: =COUNTIF(J2:J27,"<TODAY"). It is giving me an answer of 0.  Register To Reply

5. ## Re: Calculating Accruals

I also tried to put =TODAY() in H2 then wrote the formula as =COUNTIF(J2:J27,"<H2") and it isn't working that way either. It is giving me an answer of 0.  Register To Reply

6. ## Re: Calculating Accruals

Try 'em like this...

=COUNTIF(J2:J27,"<"&TODAY())

=COUNTIF(J2:J27,"<"&H2)  Register To Reply

7. ## Re: Calculating Accruals

I love you. I was getting ready to throw my computer out the window. Thank you so much. Does my formula from Post #2 seem correct?  Register To Reply

8. ## Re: Calculating Accruals Originally Posted by tapsmiled I love you. I was getting ready to throw my computer out the window. Thank you so much.
You're welcome!

Does my formula from Post #2 seem correct?
Don't know as I really don't understand what you're trying to do overall.  Register To Reply

9. ## Re: Calculating Accruals

I just went and checked the calculation from Post #2, and it is wrong. Basically, I am trying to calculate how many sick hours an employee would have earned since their date of hire. They earn 3.69 per check and are paid every 2 weeks. Some employees go back into the 1970's and 80's, so it can be a very high number.  Register To Reply

10. ## Re: Calculating Accruals

Sounds like you would need to know the pay dates to figure out how many there were.  Register To Reply

11. ## Re: Calculating Accruals

I just calculated that. I found the first pay period for this year was 1/3/14, so I did that -14 and copied it down far enough to get to the first hire date---(K2:K1045). The problem is that now my form contains names, so I can't copy it here.  Register To Reply

12. ## Re: Calculating Accruals

If I have the following:

=COUNTIF('Master List'!J2:J27,"<"&TODAY())

and it calculates the number of paydates since the beginning of the year, how could I amend this to include those dates prior to this year that are less than the date of hire (C2 for example). The J2:J27 are just this years dates, and the K2:K1045 are prior to this year.  Register To Reply

13. ## Re: Calculating Accruals

I tried this, but it's not working properly:

=COUNTIF('Master List'!J2:J27,"<"&TODAY()+COUNTIF('Master List'!K2:K1045,"<"&C2))

and then I realized that I needed a ")" after TODAY(), but that is giving me a crazy date and not a number of occurences.  Register To Reply

14. ## Re: Calculating Accruals

Nevermind, I'm dumb. I was still using < when I need to change it to >.  Register To Reply

15. ## Re: Calculating Accruals

Still not quite working...  Register To Reply

16. ## Re: Calculating Accruals

If you put all the dates in a single column then you should be able use something like this:

=COUNTIFS('Master List'!K:K,">="&hire date cell,'Master List'!K:K,"<="&TODAY())  Register To Reply

17. ## Re: Calculating Accruals

Thanks. I will try it when I get to work later.  Register To Reply