Home

Counting totals based on the previous 4 weeks

Jessica13
Occasional Visitor

I have looked all over and cannot seem to find what I am looking for so hopefully this awesome community will be able to help.

 

I need to calculate the total # of times an event has happened over the last 4 weeks. For example, I have a chart of time off (vacation, sick, personal, etc) with the date/day of week/hours taken (see below) and need to be able to keep a count of total # of events that have happened in the previous 4 weeks (just number of events, does not matter the amount of hours for each event). Right now I basically have to open the calendar and count back 4 weeks from today and then count all the events that occur between those two dates. I would love a way to have a running total on any given day. In the chart below you can see in the last 4 weeks the total count is at 8 so if I was to add an additional event on 04/05/19 it would tell me 9 instances have happened. But then if I add another instance on 04/11/19 it would then be at 7 instances for the last 4 weeks. 

 

Any ideas? 

 

DateDay of WeekHours
01/03/2019Thursday8.00
01/04/2019Friday8.00
01/14/2019Monday8.00
01/15/2019Tuesday8.00
01/21/2019Monday8.00
02/04/2019Monday8.00
02/05/2019Tuesday8.00
02/06/2019Wednesday2.50
02/18/2019Monday8.00
02/19/2019Tuesday8.00
02/21/2019Thursday8.00
03/11/2019Monday8.00
03/12/2019Tuesday8.00
03/13/2019Wednesday8.00
03/15/2019Friday8.00
03/22/2019Friday8.00
03/25/2019Monday8.00
03/26/2019Tuesday8.00
03/27/2019Wednesday8.00

 

1 Reply
You may use this formula:
=COUNTIFS($A$2:INDEX($A:$A,COUNTA($A:$A)),
">="&TODAY()-28,
$A$2:INDEX($A:$A,COUNTA($A:$A)),
"<="&TODAY())
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies