Highlight todays dat in a Gantt Chart

Hi,

I have created an annual leave gantt chart with months down the left hand side, and days of the week going across the top. Each cell contains a day of the month. what I am trying to do is to highlight today's date on the chart to make it easier to use. The problem is the cells only contain a day of the month (1-31), not a actual date. I have tried all sorts to get this to work but it never automatically highlights today's date. I have put today() in a cell, and broken this into year, month and day in other cells to see if I could use Match, but still no luck. Any ideas?

It would help if you attached a sample Excel workbook, so we can see how your data is laid out.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

please see image attached Untitled.jpg

We can't do much with an image - you clearly have an Excel workbook, so please attach that.

Pete

Hi, sorry for only getting back, I was away for a few days and have only just got back to my work computer.

I have attached my file.

In some of the cells in your calendar you have an entry of BH (presumably for Bank Holiday) rather than a number - you won't be able to make a date out of that.

Pete

Nevertheless, you can do it like this:

Select all the cells from C3 to AM14, then click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box:

=AND(TEXT(TODAY(),"mmmm yyyy")=$B3,DAY(TODAY())=C3)

Click on the Format button | Fill tab and choose your colour (e.g. yellow), and you might want to embolden the text so click the Font tab and click on Bold (you might also want to change the colour of the foreground text - click on the Color drop-down and select your colour. Then you need to click OK twice to exit the dialogue box, and you should see today's box highlighted with yellow.

Obviously, this won't show if you have letters in the calendar boxes (although I've just thought of a way that might work - I'll post back)

Hope this helps.

Pete

You should use this formula in the CF dialogue instead:

=AND(TEXT(TODAY(),"mmmm yyyy")=$B3,DAY(TODAY())=COUNTA($C3:C3))

and then this will highlight the correct day cell, irrespective of whether it contains a number or text value.

Hope this helps.

Pete

Yaay, it works! Thank you so much, I've been scratching my head for weeks over this!