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

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

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.

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

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.

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?

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.

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.

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.

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

Still not quite working...

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