Mar 17 2022 08:03 AM - edited Mar 17 2022 10:27 AM
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))
Mar 17 2022 12:08 PM
Mar 17 2022 12:10 PM
Mar 17 2022 12:15 PM
Mar 17 2022 12:18 PM
SolutionIf 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.
Mar 17 2022 12:57 PM
Mar 17 2022 12:18 PM
SolutionIf 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.