Hide Formula Results Based on Date

My challenge this week was to show the running total in a list, but only for the current date and earlier. I wanted rows for future dates to appear empty, but have the formulas in them – ready to go. How would you solve that problem?

Hide Formula Results Based on Date http://blog.contextures.com/

Keeping Track of Activities

The project that I was working on was a simple list, to keep track of my daily walks.

My daughter works for the Movember Foundation, and she forced, errr, encouraged me to sign up. This year, you can MOVE every day, to support the cause, instead of growing a Moustache. So, I have an activity page on the Movember site, but I made my own tracking sheet, in Excel of course!

I filled in all the November dates in the first column, and each day I’ll enter the number of minutes that I walked.

movembertracker03

Add Running Totals

Then, to see my progress, I added formulas for running totals, in columns D and E.

  • In column D, a SUM formula gives a running total, by summing the numbers from C4, down to the current row:
    • =SUM($C$4:$C4)
  • In column E, a COUNT formula gives a running count, by counting the numbers from C4, down to the current row:
    • =COUNT($C$4:$C4)

That worked well, but I didn’t like all the numbers showing up in the upcoming date rows. How could they be hidden?

movembertracker02

As always with Excel, there are many ways to do what you need, but here’s how I solved the problem. Let me know if you’d use a different solution!

No Entry, No Results

Sometimes, I add an IF function in a formula, to hide the result until a specific column is filled in.

In this sheet, the dates are all filled in, so I couldn’t use that column as a test. Instead, I’ll check for an entry in column B (Activity).

=IF([@Activity]=””,””,COUNT($C$4:$C4))

That worked, but it didn’t just hide the results for upcoming days – it also hid the result if I skipped a day for some reason. (This is just a test – I did walk on Tuesday!)

Maybe I’m too picky, but I’d prefer to see the running total in that row too.

movembertracker04

Check the Date

So, what I really want to do is show the running total for every day, up to the current date.

To calculate the current date, we can use the TODAY function. To check if the entry in column A (Date) is after today:

=[@Date]>TODAY()

I’ll add that test to the running total formulas:

  =IF([@Date]>TODAY(),””,SUM($C$4:$C4))

  =IF([@Date]>TODAY(),””,COUNT($C$4:$C4))

Now, if the date in column A is after today’s date, the result will be an empty string, so the cell will appear empty.

  • Running totals will appear in all rows for today or earlier, even if there was no activity.
  • No running totals in rows with future dates (Nov. 6th and later)

movembertracker05

Download the Sample File

You could adapt this technique for other types of lists – it’s not just for Movember MOVE tracking!

You can download a copy from the Excel Sample Files page on my website — in the Functions section, look for FN0042 – MOVEMBER Activity Tracker Chart.

And if you want to support the cause, click this link to go to my Movember page.

_____________

Hide Formula Results Based on Date http://blog.contextures.com/

You may also like...

5 Responses

  1. Nigel S says:

    How come you didn’t want to use conditional formating? Background and font are white if date is after today?

  1. November 9, 2015

    […] Hide Formula Results Based on Date […]

Leave a Reply

Your email address will not be published. Required fields are marked *