Solved

# Excel rolling 365 day formula

Posted on 2014-11-26
652 Views
Hi,

I have a spread sheet and column a is a list of dates from a year ago today 26/11/13 rolling down to the end of year 2020. I then have todays date at the top via =TODAY() and a list of all users on the next line across. I want to be able to put sickness days against the users so over any rolling 365 day period I can see how many sick days they have had.

Is there a formula that will do this ?

Thanks

Mark.
0
Question by:mdj1501
• 8
• 7
• 2
• +1

LVL 23

Expert Comment

=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
0

LVL 48

Expert Comment

Hi,

if you want to take into account leap years

pls try

=EDATE(B7,-12)+1

Regards
0

LVL 1

Author Comment

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 ?
0

LVL 48

Expert Comment

Could you send a dummy?
0

LVL 31

Expert Comment

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
0

LVL 1

Author Comment

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
0

LVL 31

Expert Comment

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
0

LVL 1

Author Comment

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
0

LVL 31

Expert Comment

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
0

LVL 31

Expert Comment

If you upload the file we can take a look.
0

LVL 1

Author Comment

Here you go

Thanks
Book3.xlsx
0

LVL 31

Expert Comment

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

LVL 31

Expert Comment

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
0

LVL 1

Author Comment

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

LVL 1

Author Comment

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
0

LVL 31

Expert Comment

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

LVL 31

Accepted Solution

Rob Henson earned 500 total points
New version uploaded with bigger data set.

Filter has been set to dates between 29/11/13 and 28/11/14.

Notes values in rows 22 - 26, the Famous Five were all off for half a day.

Rob H
Sick-Days.xlsx
0

LVL 1

Author Comment

Rob,

Spot on thanks for all of your help
0

## Featured Post

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Datâ€¦
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity anâ€¦
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.