Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Rolling Year Calculation

  1. #1
    Join Date
    28th March 2007
    Posts
    8

    Rolling Year Calculation

    My employer uses a scoring system to give a value to an individual’s sick record in the current rolling year.

    Each person, has recorded, the number of sickness occasions and the total number of days in this rolling year period, these details go onto the final sheet where a score is calculated.

    At present to get an accurate period, I have needed manually to deduct days to produce the correct time frame, ie 27/3/06 to 28/3/07 or when the sikness carrys on into the folling month.

    I have tried without success to modify the rolling year formula, which as it is, will include the whole of March 06.

    The formula is

    =SUMIF(B5:D49,">="&DATE(YEAR(TODAY()),MONTH(TODAY())-12,1),L5:L49)

    I am new to all this, but what I am trying to get is a current rolling year based around TODAY that will give values for total days and number of occasions without removing expired sick.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Rolling Year Calculation

    Can't test it without an attachment, but this is what I would use:

    =SUMPRODUCT((B5:B49>=TODAY()-365)*(L5:L49))

    If the date is not always in column B, please explain or attach a sample.
    Last edited by ByTheCringe2; March 28th, 2007 at 17:19.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th April 2006
    Posts
    224

    Re: Rolling Year Calculation

    I believe this is now a triple cross post

    http://www.excelforum.com/showthread...t=rolling+year

    VBA Noob

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th March 2007
    Posts
    8

    Re: Rolling Year Calculation

    Quote Originally Posted by VBA Noob
    I believe this is now a triple cross post

    http://www.excelforum.com/showthread...t=rolling+year

    VBA Noob
    Sorry, I didn't realise that all the forums were connected. I am desperate to find a solution as it is beginning to drive me nuts so I thought I would ask on different forums. More exposure and all that

    ByTheCringe2 this is the doc I've created so far.

    excel trial2.zip

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Rolling Year Calculation

    I will accept your apology, but please read the rules. Another case like this and you will end up banned...

    (Thanks, VBA Noob.)
    Last edited by ByTheCringe2; March 28th, 2007 at 17:37.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Rolling Year Calculation

    The formula I gave you will work, but I don't know where you are going to put it.

    For more accuracy in leapyears, etc, and if you don't mind using an add-in, this will be better (can't test it):

    =SUMPRODUCT((B5:B49>=EDATE(TODAY()-12))*(L5:L49))

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    28th March 2007
    Posts
    8

    Re: Rolling Year Calculation

    Thanks I'll give it a try.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    28th March 2007
    Posts
    8

    Re: Rolling Year Calculation

    Unfortunately, neither code worked. Possibly I've bitten off too much here.

    The aim is to get a total value in days, for the current rolling year period based on TODAY and EDATE-12-(1), using the totals in the Final Value sick days column that fall within this criteria.

    I also need the number of occasions (Events) which fall within the same period.

    For example if TODAY is 29/3/07 then the period would go back to 28/3/06.

    Start Date ----------------------- End Date-----------------------Final value
    MON....3/1/05 -------------------- FRI .. 7/1/05 ------------------ 5
    MON....1/1/07 --------------------- TUE .. 9/07/07 ---------------- 6
    MON ...26/3/07 ------------------- THUR ..29/3/07 -----------------4 (2)


    Total Days……15 (2)…………….. Occasions……3 (1)…………….

    Occasions COUNTA adds cells A1:A7 TOTAL DAYS SUMS F1:F7

    I am hoping that if a sick period is spread across two months then the rolling period will only deduct the days in say January and leave the days in February.

    At present I have to look back and manually delete all expired entries and adjust sick which crosses the cut off dates to obtain the correct result.

    This would enable a sick record (1 per sheet) for each person to run forever, but the values recorded would be correct.

    Like I say possibly I am trying to incorporate too much information.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Rolling Year Calculation

    Right, let's go back a bit. I put the first formula in and it gives the answer 4, which is correct for what is trying to do.

    Now what is it not doing for you?

    It would help if you could reattach the sample, cut down so that it does not need to be zipped, with the formula in place.
    Last edited by ByTheCringe2; March 29th, 2007 at 21:45.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    28th March 2007
    Posts
    8

    Re: Rolling Year Calculation

    I've tried again and it doesn't give the right figure

    I have deleted additional pages and there is one staff page and the final page in the attachment.

    excel trial3 for the board.xls

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Rolling Year-to-Date Sum By Month Chosen
    By calimero1 in forum EXCEL HELP
    Replies: 3
    Last Post: June 20th, 2008, 10:50
  2. Excel 2000 incorrectly assumes that the year 1900 is a leap year
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: April 11th, 2008, 14:09
  3. Rolling Overtime Calculation
    By ShawnIRQL in forum EXCEL HELP
    Replies: 5
    Last Post: August 9th, 2007, 01:15
  4. year,month,week,days calculation
    By rpdtrooper in forum EXCEL HELP
    Replies: 3
    Last Post: August 3rd, 2006, 16:44

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
  •  
porno