SOLVED

How do I formulate a running count of the dates in my headers to today's date?

Copper Contributor

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.

 

ColumnB2C2D2E2...DX2DY2DZ2
Date8/18/218/19/218/20/218/23/21...3/17/223/28/223/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))

5 Replies

@DanW1648 

Try

 

=COUNTIF(B2:GX2,"<="&TODAY())

It is giving me a zero, do I need the dates formulated a certain way?
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.
best response confirmed by DanW1648 (Copper Contributor)
Solution

@DanW1648 

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.

This worked, for some reason when the dates are in my table it doesn't read the date format unless I double click on the date cell & do it for all of them, this way counted it right away, Thank you again for your help.
1 best response

Accepted Solutions
best response confirmed by DanW1648 (Copper Contributor)
Solution

@DanW1648 

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.

View solution in original post