Announcement

Collapse
No announcement yet.

Rolling Year Calculation

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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, 17:19.
    .

    Comment


    • #3
      Re: Rolling Year Calculation

      I believe this is now a triple cross post

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

      VBA Noob

      Comment


      • #4
        Re: Rolling Year Calculation

        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

        Comment


        • #5
          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, 17:37.
          .

          Comment


          • #6
            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))
            .

            Comment


            • #7
              Re: Rolling Year Calculation

              Thanks I'll give it a try.

              Comment


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

                Comment


                • #9
                  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, 21:45.
                  .

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      Re: Rolling Year Calculation

                      OK, thanks. See the attachment. G17 works, doing what it is trying to do. Now let's expand that to do exactly what you want. Forget EDATE for now, you can change whatever we do to include that later.
                      Attached Files
                      .

                      Comment


                      • #12
                        Re: Rolling Year Calculation

                        I can see this works using the start date for the criteria of falling in or out of the period.

                        My problem is that I have been trying to include B5:D49 (on the original sheet which is larger) so that the whole period of sick is tested.

                        On my example, the last two sick periods are included, but the earlier event, although starting outside the rolling year, needs to have the last day ie the 28th added, thus the total sick days would become 5.

                        Thanks again for your continued assistance.

                        Comment


                        • #13
                          Re: Rolling Year Calculation

                          Right, I understand your problem. There needs to be a column added that will give the number of days sick in each row, that is within the rolling 12 months. Then you just sum that column. SUMPRODUCT won't work, because the information is not available in a column to sum.
                          .

                          Comment


                          • #14
                            Re: Rolling Year Calculation

                            I think I understand! At the end of each sick row there is a final value column, it’s this cell that needs the rolling year formula. Could this then use NETWORKDAYS, TODAY and EDATE-12-(1) to give a current rolling period.

                            What do you think would be the best formula to use, because what I would like to do also is add up the number of occasions in this period.

                            This is all rather new to me and has turned out to be a much steeper learning curve than expected!

                            Comment


                            • #15
                              Re: Rolling Year Calculation

                              Yes, it can be done that way. I had a go earlier, but I'm not thinking too well today. I can't use NETWORKDAYS and EDATE, so I can't help you. Maybe a multi-IF formula could do it.
                              .

                              Comment

                              Working...
                              X