Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.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?
very good. Did you see this recently? Excel killed the economy: http://www.bbc.co.uk/news/magazine-22213219
.. 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/
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
Thank you... This is very helpful tip.
thanks... Very easy and simple...best...
The arrow is a super little trick Dan, just what I was looking for
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..)
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
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.
This is great! Now, how do I make sure that it applies to other rows below? 🙂 Thanks.
Sorry, Maria. I don't understand your question.
you can use =$b2=today() in the formula
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.
Aaaah THANKS! 😀
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.
How would you do this if the date is in column C and goes down rather than across?
Thanks
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.
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
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.
how does it work if my dates are first monday of the week
so 6-Mar 13-Mar 20-Mar 27-Mar ??
Found in a search, clicked the link and ended up here as I knew I always would...the student teaches the 'teacher' 😉
Ha! Glad to be of service, Jason. 🙂
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!
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
Yer a wizard, Harrison
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!