how do i create a daily rolling calendar in excel?

April 16, 2014 at 15:20:23
Specs: Windows 7
I need a calendar that, each day, will sum attendance data going back 6 months to the day. In other words, add today to the bottom of the calendar and drop the oldest date. Sum attendance for the dates displayed. I need to show this for 20 employees.

See More: how do i create a daily rolling calendar in excel?

Report •

April 17, 2014 at 06:01:39
Without knowing the layout of your data, it's impossible for use to offer any solutions.

You said "I need a calendar that, each day, will sum attendance data "

Calendars don't "sum" anything. Calendars show dates, days, appointments, etc.

Do you mean you need a list such as this?

         A                          B
1     1/1/2014        {Sum of data from 11/2/2013}
2     1/2/2014        {Sum of data from 11/3/2013}
3     1/3/2014        {Sum of data from 11/4/2013}

If that is what you are looking for, we'll need some details as to where the data to be summed will be found.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 17, 2014 at 10:38:30
Thank you for your reply. I tried to attach a screenshot but must not have done it correctly & see no place to attach a file here. Basically, it is as you demonstrated, except the employees names are my column labels. Yes, the dates are my row labels. Attendance numbers are entered for each employee and for each day up to today, going back 6 months. I tried dropping the oldest and adding today's date by using a filter in the date column. I used a subtotals with 109 to ignore hidden rows. If this is a good method, I think it will work for me. Any thoughts or suggestions on this?

Report •

April 17, 2014 at 13:17:01
EDIT: Please read this post to understand how I came up with the suggestion offered in my next response.

Post starts here

Keep in mind that I can't see your spreadsheet from where I'm sitting, so it's still not clear to me what your data looks like or where you want these SUMs placed.

The issue I see is that you can't use any kind of "offset" formula because "6 months" worth of data isn't a consistent number of cells because all months don't have the same number of days. If you wanted to SUM the last 180 entries, you could use something like this:

As an example, with a list of numbers in Column A put this formula in B200 and it will SUM A21:A200. Drag it down and it will SUM A22:A201. In other words, it will always SUM the corresponding cell in Column A and 179 Rows upwards.


But, as I said, not every 6 month period is 180 days. Maybe you can play with that formula and use one of Excel's Date functions to figure out how many Rows back any given 6 month date is.

With a better understanding of your layout, there may be something else I can offer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

Related Solutions

April 17, 2014 at 17:35:08
Here is an enhancement to the formula I offered in my previous response.

This formula is based on the following data layout. It may need to be modified to fit your exact needs.

             A                 B            C
1          Date             Values         SUM
2        1/1/2014              6             6
3        1/2/2014              7            13
4        1/3/2014              2            15
5        1/4/2014              5            20
190      7/8/2014              6           911   
191      7/9/2014              7           913

Put this formula in C2 and drag it down:

SUM(INDIRECT(ADDRESS(ROW(),2) & ":" &"B2")),

I'll explain the IF function later...for now let's deal with building the SUM range for each 6 month period.

The EDATE function has this syntax:

EDATE(start_date, n months)

The function will return the date that is n months away from the start_date.

=EDATE("1/1/2014", 6) will return 7/1/2014 (6 months after)

=EDATE("1/1/2014", -6) will return 7/1/2013 (6months before)

If we subtract the result of EDATE(start_date, -6) from the start_date, we will get the number of days between those dates. We can then use that number to build the range to SUM.


ADDRESS(ROW(),2) will return the address of the current row, Column B, e.g. in Row 190, we'll get "$B$190"

ADDRESS(ROW()-(Ax-EDATE(Ax,-6)),2) will return the address of the cell that contains the date that is 6 months prior to the date in Ax, e.g. for A190, in the example above, "$B$9"

Therefore, in Row 190,

ADDRESS(ROW(),2) & ":" &ADDRESS(ROW()-(A190-EDATE(A190,-6)),2))

will return "$B$190:$B$9"

Finally, SUM(INDIRECT("$B190:$B$9")) will SUM that range.

OK, back to the IF function.

In my example, there is are no dates that are 6 months prior to the dates in A1:A182, so I can't build a 6 month range of dates. However, I can build a range that goes back as far as my start date in B2:


So as long as IF(ROW()-(A2-EDATE(A2,-6))<1 is TRUE, the formula SUMs the range from B2 through B(current row). Once a 6 month range is available, the formula SUMs the last 6 months worth of values.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

April 18, 2014 at 05:33:35
Sorry, the screen capture still isn't working for me. I will enter the sample manually. It looks something like this:
1 Day Linda Pete Mike Mary
2 11/18/14 .5 1 .75 .5
3 11/19/14 1 1 1 1
. . . . . .
365 4/18/14 .25 .5 1 .75

In col A, I have entered 365 days. I have used a filter in col A that hides all rows except for the current 6 mths of days. Each day, I use the filter to hide the oldest date and unhide today's date.
In row 368, I have entered a subtotal(109, xx:xx) for each employee, AND a subtotal(102,xx:xx) to count the unhidden rows so that I can ensure I have the correct # of days displayed. Does this make sense? Am I using the subtotal correctly? And is it possible to write a macro that will do the filtering for me each day?
Sorry if I wasn't clear before.At some point, maybe someone can tell me how to download my spreadsheet here. As I said, the screen capture included here isn't working for me. Thanks much for your time, DerbyDad.

Report •

April 18, 2014 at 05:34:33
Noticed my example didn't line up correctly but you get the idea . . .

Report •

April 18, 2014 at 06:33:40
Please click on the following line and read the instructions on how to post example data in this forum. Then reformat your data so that it is easier for us to read.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Ask Question