# 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.

Thank you in advance!!

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.

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.

4. ## Re: Calculating Accruals

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

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.

6. ## Re: Calculating Accruals

Try 'em like this...

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

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

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?

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.

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.

10. ## Re: Calculating Accruals

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

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.

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.

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.

14. ## Re: Calculating Accruals

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

15. ## Re: Calculating Accruals

Still not quite working...

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())

17. ## Re: Calculating Accruals

Thanks. I will try it when I get to work later.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1