Home/ Questions/Q 8923
Next
In Process
Computing Staff
  • 2
Asked: 2021-11-30T10:04:40+00:00 In:

How Do I Create A Daily Rolling Calendar In Excel?

  • 2

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.

Share

    2 Answers

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

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

      =SUM(INDIRECT(ADDRESS(ROW(),1) & “:” &ADDRESS;(ROW()-179,1)))

      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

      • 0