SOLVED

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

Occasional Contributor

# 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))

5 Replies

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

Try

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

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

It is giving me a zero, do I need the dates formulated a certain way?

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

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 (Occasional Contributor)
Solution

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

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.

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

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.