Highlighting today’s date: a lovely little trick

I came up with a neat trick the other day. With each column indicating a sequential date, it was a way of highlighting the current date.

There are two ways of doing this. You can either use conditional formatting to format the entire column; or you can put a cute little down arrow above the date at the top of the column.

First, some principles. Let's assume our dates run across row 2, starting in column B and running across to the right.

Highlighting the column

Highlight all of the columns that house dates: column B all the way over to the right-hand side. And hit Conditional Formatting on the Home ribbon. Hit New Rule, and choose Use a formula to determine which cells to format.

In the formula box, type the following:

=B$2=today()

Now choose the format that you want (yellow fill, possibly), and hit OK.

This compares the date in the column in row 2 with today's date. If they're the same as one another, it will highlight the column. Otherwise, it won't. The dollar before the "2" serves to always look at the value in row 2, irrespective of the location of the cell you're highlighting.

Having a pointer arrow

With cell B1 selected (immediately above the first date), hit the Symbol button on the Insert ribbon. Change the font to Wingdings and double-click the down-arrow symbol towards the bottom of the array of options.

Change the font colour to white so that it's invisible (assuming your backdrop colour is white), and copy that all the way across row 1, above your dates.

Now highlight all of those cells B1 across to its right, and select Conditional Formatting from the Home ribbon.

Again, Hit New Rule, and choose Use a formula to determine which cells to format.

In the formula box, type the following:

=B$2=today()

And then choose a red font colour when the conditional format is true.

Every time you open the spreadsheet, the arrow will have moved above today's date.

All rather neat, don't you think?

 

This entry was posted in How to. Bookmark the permalink.

26 Responses to Highlighting today’s date: a lovely little trick

  1. Mike says:

    very good. Did you see this recently? Excel killed the economy: http://www.bbc.co.uk/news/magazine-22213219

  2. Mike says:

    .. and this is worth a look. A whole new aspect of gameplay opens up at level 10: http://carywalkin.wordpress.com/2013/03/17/arena-xlsm-released/

  3. Steve Root says:

    Alternative: use an If statement, like:
    =IF(B2=TODAY(),"ê","")
    The arrow (assuming you've set the font to wingdings) will only appear when the statement is true.
    Of course, the advantage of conditional formatting is that you can change the cell background colours

  4. Qadeer Ahmed says:

    thanks... Very easy and simple...best...

  5. Adrian Hogan says:

    The arrow is a super little trick Dan, just what I was looking for

  6. Ned says:

    Dan you're a legend!! Have been pondering how to get entire row highlighted to line up with todays date (dates run down col B) and you've sorted my dilemma. Thanks a million!! (have added the red arrow as well which is a neat addition..)

  7. george says:

    I love the arrow...
    Totally off subject but I am working on a drivers recap of hours worked. I want to shift a group of cells right with each new calendar date. Any idea how to do this? please send info to george.grenon@rivertontruckers.com

    • Steve Root says:

      My thought would be; have a macro run when the sheet is opened. The macro would step something like:

      Read cell A4
      IF A4 = Today, do nothing
      ELSE
      Insert column
      put todays date into A4
      End.

      BUT! What if you only want to open the sheet an view things, without adding new data for today?
      Then, have a message display, with a box to ask if a new day should be added/
      BUT! What if you want to add records for yesterdays date, not today?
      Maybe it would be best to have a macro (with button) to insert column. It could even prompt for date (and it you get really clever, only prompt for dates if there could be more options than today's date).
      Of course, if you need every date including days where nothing happened then you can have the button read the last date, then insert new column and date until today = value in column.

      Hope that helps.

  8. Maria says:

    This is great! Now, how do I make sure that it applies to other rows below? 🙂 Thanks.

  9. Bob says:

    Can you do the same thing to rows? I have am ongoing database that is organized by dates (future and past) in the row A. Is there a way to see today's date/row in relation to the rest of the document each day I open it? The document is already sorts newest to oldest with a pulldown arrow.

  10. Karen says:

    Aaaah THANKS! 😀

  11. Bodynsoil says:

    I'm working on this and trying to get it to open the sheet to today's date when I have multiple months on one page.

  12. Naomi Smith says:

    How would you do this if the date is in column C and goes down rather than across?

    Thanks

  13. Tracey Smith says:

    how can i conditional format using a date that is not today.
    i want to highlight a cell if it is 12 weeks past a date ( not todays date)in a previous column.

    • Dan says:

      If the date field being highlighted is in column D and the date is in column A, and the rows start from row 2, then the condition is
      D2>$A2+7*12

  14. Ade Turner says:

    Well
    it doesn't work for me, today is Thursday 20th and the arrow is over Saturday 22nd. I have checked the PC system date and all ok. Good idea but shame it doesn't do what its supposed to.

  15. Lis says:

    how does it work if my dates are first monday of the week

    so 6-Mar 13-Mar 20-Mar 27-Mar ??

  16. Found in a search, clicked the link and ended up here as I knew I always would...the student teaches the 'teacher' 😉

  17. Aleesha Shahzad says:

    Hi,

    Wondering if you could help me please. How do i add a moving data line on a spreadsheet that has dates for each week rather than each day?

    Thanks!

  18. Barry Sutton says:

    To show all future dates as green and historic as red I adapted the above using
    =$A2>TODAY()-1

    and conditional formatting for "true" and "false". This allows you to see at a glance (especially when scrolling quickly) future and older dates in a table.

    Barry

  19. Melanie says:

    Yer a wizard, Harrison

  20. Rina says:

    Challenge:
    making this work with multiple date columns.

    Detail:
    I have four columns with dates in my task sheet. I am only trying to select today's date from column D. I tried using =D:D=TODAY() and nothing worked. When I used something like =$D2=TODAY(), entire rows were highlighted with today's date in other columns, where D:D's dates were for past or future dates. How can I get the conditional formatting to look only at D:D? I have tried making it absolute ($D:$D) as well and it highlighted nothing.

    Please advise!

Comments are closed.