Excel rolling 365 day formula

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.
LVL 1
mdj1501Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EirmanChief Operations ManagerCommented:
=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
Rgonzo1971Commented:
Hi,

if you want to take into account leap years

pls try

=EDATE(B7,-12)+1

Regards
0
mdj1501Author Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Rgonzo1971Commented:
Could you send a dummy?
0
Rob HensonFinance AnalystCommented:
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
mdj1501Author Commented:
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
Rob HensonFinance AnalystCommented:
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
mdj1501Author Commented:
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
Rob HensonFinance AnalystCommented:
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
0
Rob HensonFinance AnalystCommented:
If you upload the file we can take a look.
0
mdj1501Author Commented:
Here you go

Thanks
Book3.xlsx
0
Rob HensonFinance AnalystCommented:
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
Rob HensonFinance AnalystCommented:
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
mdj1501Author Commented:
Many Thanks Rob, let me have a look around at the two options.
0
mdj1501Author Commented:
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
Rob HensonFinance AnalystCommented:
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
Rob HensonFinance AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mdj1501Author Commented:
Rob,

Spot on thanks for all of your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.