Solved

Excel rolling 365 day formula

Posted on 2014-11-26
18
1,278 Views
Last Modified: 2014-11-30
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
Comment
Question by:mdj1501
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 23

Expert Comment

by:Eirman
ID: 40466501
=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 50

Expert Comment

by:Rgonzo1971
ID: 40466561
Hi,

if you want to take into account leap years

pls try

=EDATE(B7,-12)+1

Regards
0
 
LVL 1

Author Comment

by:mdj1501
ID: 40466574
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40466606
Could you send a dummy?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40466633
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

by:mdj1501
ID: 40466911
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 33

Expert Comment

by:Rob Henson
ID: 40467011
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

by:mdj1501
ID: 40467152
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 33

Expert Comment

by:Rob Henson
ID: 40467172
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40467226
If you upload the file we can take a look.
0
 
LVL 1

Author Comment

by:mdj1501
ID: 40467553
Here you go

Thanks
Book3.xlsx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40467902
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 33

Expert Comment

by:Rob Henson
ID: 40469069
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

by:mdj1501
ID: 40469818
Many Thanks Rob, let me have a look around at the two options.
0
 
LVL 1

Author Comment

by:mdj1501
ID: 40470221
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 33

Expert Comment

by:Rob Henson
ID: 40470315
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 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40470350
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

by:mdj1501
ID: 40472385
Rob,

Spot on thanks for all of your help
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question