+ Reply to Thread
Results 1 to 17 of 17

Calculating Accruals

  1. #1
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

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

  2. #2
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    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. #3
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    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. #4
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Calculating Accruals

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

  5. #5
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    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. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Calculating Accruals

    Try 'em like this...

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

    =COUNTIF(J2:J27,"<"&H2)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    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. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Calculating Accruals

    Quote Originally Posted by tapsmiled View Post
    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. #9
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    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. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Calculating Accruals

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

  11. #11
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    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. #12
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    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. #13
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    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.
    Last edited by tapsmiled; 02-16-2014 at 05:19 PM.

  14. #14
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Calculating Accruals

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

  15. #15
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Calculating Accruals

    Still not quite working...

  16. #16
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #17
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Calculating Accruals

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Vacation Accruals
    By DonnaJ in forum Excel General
    Replies: 1
    Last Post: 04-22-2016, 11:22 AM
  2. Calculating Vacation Accruals for bi-monthly payroll
    By Marbles21 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-09-2012, 08:07 AM
  3. Vacation Accruals
    By lab1107 in forum Excel General
    Replies: 2
    Last Post: 06-12-2012, 03:25 PM
  4. Excel 2007 : Calculating Vacation Accruals
    By DonnaJ in forum Excel General
    Replies: 2
    Last Post: 07-13-2010, 08:43 AM
  5. Help with Accruals please
    By sgrahams in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 01-23-2007, 04:03 AM

Bookmarks

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