Forum Discussion
How do I formulate a running count of the dates in my headers to today's date?
How do I formulate a running count of the dates in my headers to today's date but only the dates in my headers & only when today is in my headers? Since this is tracking for a school the dates jump over Weekends, Breaks & Holidays & I need a rolling count of the total amount of days up to today's date when it is in the headers.
| Column | B2 | C2 | D2 | E2 | ...DX2 | DY2 | DZ2 |
| Date | 8/18/21 | 8/19/21 | 8/20/21 | 8/23/21 | ...3/17/22 | 3/28/22 | 3/29/22 |
I have this formula for calculating Mondays through Fridays but I am not sure how to use this to calculate all the days.
=SUMPRODUCT(($B$200:$GX$200<=TODAY())*(WEEKDAY($B$200:$GX$200)=2))
If the range B2:GX2 contains "real" dates, it should work.
If it contains text values that look like dates, for example if the range is the header row of a table, use
=SUM(--(DATEVALUE(B2:GX2)<=TODAY()))
If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.
5 Replies
- I got it, I had to format it as dddd, mmmm d, yyyy & I just pasted it in a row under my table for calculation purposes. Thanks so much for your help.
- It is giving me a zero, do I need the dates formulated a certain way?
If the range B2:GX2 contains "real" dates, it should work.
If it contains text values that look like dates, for example if the range is the header row of a table, use
=SUM(--(DATEVALUE(B2:GX2)<=TODAY()))
If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.
