Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
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. Super Moderator
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. Re: Rolling Year Calculation

I believe this is now a triple cross post

VBA Noob

Excel Video Tutorials / Excel Dashboards Reports

4. I agreed to these rules
Join Date
28th March 2007
Posts
8

Re: Rolling Year Calculation

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

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. Super Moderator
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. Super Moderator
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. I agreed to these rules
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. I agreed to these rules
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. Super Moderator
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. I agreed to these rules
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

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