Excel rolling 365 day formula

=Today()-365 would refer to a year ago.
(it would not be an exact year if a leap year is involved)

You could use this formula
http://support.microsoft.com/kb/214019
in conjunction with the Year() function to go back one day further in leap years

Hi,

if you want to take into account leap years

pls try

=EDATE(B7,-12)+1

Regards

I am trying to get the formula sorted based on today's date - 365 check down the column and work out the sick days if it is a 1 or 0.5 I am trying to a total ?

any ideas ?

Could you send a dummy?

Mark, as Rgonzo says a sample file would be useful.

Without said sample, I suspect you can use SUMIFS function, the syntax for which is:

=SUMIFS(SumColumn,CriteriaColumn1,Criteria1,CriteriaColumn2,Criteria2,CriteriaColumn3,Criteria3,......)

Do you have a separate column for each user. If so I think you will be able to do as follows:

=SUMIFS(UserColumn,DateColumn,>TODAY()-365,DateColumn,<=TODAY())

Alternatively, I would suggest a different approach. I assume the current layout has numerous blank cells, ie days when no-one is off sick. How about just creating a list with following columns:

Date  |  User Name  |  No of Days

You could then use this list as data  for a pivot table. The pivot will allow you to show only a range of dates ie prior 12 months.

Thanks,
Rob H

Hi Thanks Rob what I need is something like this

Todays Date :    26/11/14 (can use =TODAY()
Employee Name                                                           User 1                 User 2
Days sick in last 12 months
26/11/13
27/11/13
28/11/13

so the dates will keep rolling down and under each user when they are off sick a 1 or 0.5 goes in the cell against the date . At the top it shows on a rolling 365 day period how many days sick they have had

does that make sense ?

Thanks

Yes I understand that. Following assumptions for the formula:

List of dates in column A starting at A5 going down to A2560 (allows 7 years worth)
Todays Date in cell B2
User 1 in cell C3
User 2 in cell D3
User 3 in cell E3 ... etc

Formula in C4:

=SUMIFS(C5:C2560,$A$5:$A$2560,">"&$B$2-365,$A$5:$A$2560,"<="&$B$2)

You can copy across to D4, E4 etc for user 2, 3 etc and reference to C5:C2560 will change to D5:D2560 and onward but other ranges will stay static.

Did you understand the suggestion I was making?

With the layout you have if nobody is sick in a particular week, the rows relating to those days will be blank so not really required and taking up resources. With simple example above lets assume User 1 is sick on 26 Nov and user 2 is sick on 28 to 29 Nov; nobody is sick on 27th. The layout I am suggesting would be:

Date          |   Name  |   Days
26/11/13  | User 1   |   1
28/11/13  | User 2   |   1
29/11/13  | User 2   |   1

As another person is off, whoever updates the list just adds the current date, name and days sick. For user 2 in this scenario you could just change the days number to allow for another day off but that could show slightly inaccurate results at the point where a 12 month period cutoff cuts through the period off sick.

You would then use this list as data source for a Pivot in which you can group the dates into months and years and show only the required previous 12 months; the filter drop down will show multiple options. The User names would be rows in the pivot and the value would be the sum of the Days column.

Thanks
Rob H

Yes thanks for that but the value in c4 always comes out as 0

any ideas ?

I have done a new spreadsheet to test this

Are your dates true dates?

Try applying a comma format to the cells containing the dates, it should turn to a number in the range 40000 to 50000. If it stays as a date, then the dates are not true dates but text.

Thanks
Rob H

If you upload the file we can take a look.

Here you go

Thanks
Book3.xlsx

You have todays date in B1 when formula is looking at B2. Change formula or move date.

Result is still 0 as all values in column C are more than 12 months ago. Put a value in next to a date that is less than 12 months ago and it will be counted.

Corrected file now attached with formula referring to correct cell for todays date and entries for both users that count in last 12 month period.

Also with a page for my alternative suggestion.

With the limited data in the sample, the alternative doesn't show much but hopefully you will get the idea.

Thanks,
Rob H
Sick-Days.xlsx

Many Thanks Rob, let me have a look around at the two options.

Rob,

That is great on the suggested I tried to recreate this to learn it from scratch and can get everything but the

Sum of Days  moved and labels for 2013, nov and dec. I can get it where there is a list of days under each user but nothing else

also what would happen in the event of two people being off on the same day ?

Thanks once again

To get the Sum drag the Days field into Value area in bottom right pane.

To get monthly labels, right click on a date and select the Group option. You can then group by Month and Year.

For multiple people on same day, make multiple entries in the list, the dates row will only show each date once and will list both users values.

Rob,

Spot on thanks for all of your help