Hot Dates

[Interloper]

Hi,

I have a Year-at-a-Glance worksheet into which users enter important dates
and associated actions to be taken. I now want to use Conditional
Formatting to highlight specific entries in the worksheet when their date
matches today's date.

The problem I have is that I need to convert data from three independent
cells into a date to compare it with 'TODAY()'.

The first cell (formatted as a number) carries the day, e.g. 2, 17, 23,
etc.

The second cell (formatted as General) carries the month, e.g. January,
March, September, etc.

The third cell (formatted as a number) carries the year, e.g. 2010, 2011,
2012, etc.

Is it possible to concatenate the data from the three cells and covert the
result to a valid date number to compare with 'TODAY()'?

If necessary, the second cell could carry the month as a number, e.g. 03,
rather than as text, e.g. March.

Thanks,

 

[Claus Busch]

Hi,

Am Sat, 6 Aug 2011 13:55:07 +0100 schrieb Interloper:

The first cell (formatted as a number) carries the day, e.g. 2, 17, 23,
etc.

The second cell (formatted as General) carries the month, e.g. January,
March, September, etc.

The third cell (formatted as a number) carries the year, e.g. 2010, 2011,
2012, etc.

it's easier if in the second cell the month is entered as number.
Your year in C2, the month in B2 and the day in A2:
=DATE(C2,B2,A2)
If you have in B2 the name of the month:
=DATE(C2,MATCH(B2,{"January","February","March","April","May","June","July","August","September","October","November";"December"},0),A2)



Regards
Claus Busch

 

[DanielCo]

With E1:G1 :
=DATEVALUE(E1&F1&G1)
Daniel

Hi,

Am Sat, 6 Aug 2011 13:55:07 +0100 schrieb Interloper:


it's easier if in the second cell the month is entered as number.
Your year in C2, the month in B2 and the day in A2:
=DATE(C2,B2,A2)
If you have in B2 the name of the month:
=DATE(C2,MATCH(B2,{"January","February","March","April","May","June","July","August","September","October","November";"December"},0),A2)



Regards
Claus Busch

 

[joeu2004]

I have a Year-at-a-Glance worksheet into which users
enter important dates and associated actions to be taken.
I now want to use Conditional Formatting to highlight
specific entries in the worksheet when their date matches today's date. [....]
The first cell (formatted as a number) carries the day,
e.g. 2, 17, 23, etc.
The second cell (formatted as General) carries the month,
e.g. January, March, September, etc.
The third cell (formatted as a number) carries the year,
e.g. 2010, 2011, 2012, etc.

Is it possible to concatenate the data from the three
cells and covert the result to a valid date number to
compare with 'TODAY()'?

Yes. Your conditional format in all 3 columns could be the formula:

=DATEVALUE($A1&$B1&$C1)=TODAY()

Note the use of partial-absolute references. This permits you to assign the
same conditional format to all 3 columns in one action.

However, I think it would be easier for your users as well as you if they
entered the complete date in just one cell. You can use Custom formatting
to display each of the three cells the way you want. For example:

A1: the complete date, formatted as Custom d
B1: =A1, formatted as Custom mmmm
C1: =A1, formatted as Custom yyyy

The "complete date" can be entered in any form that Excel recognizes as a
date. In particular, the date can be entered the way that users would have
entered across the 3 cells, e.g. 13 January 2012.

Then your conditional format in all 3 columns could be simply:

=$A1=TODAY()

or

A1: =A1=TODAY()
B1: =B1=TODAY()
C1: =C1=TODAY()

whichever seems more intuitive to you.

Note: If you assign the conditional format =A1=TODAY() to all 3 columns at
the same time, Excel will change A1 to B1 and C1 automagically.

 

[Interloper]

Thanks for the helpful replies. I'm certain I can now achieve the required
highlighting of the cells when the dates match.

"joeu2004" suggested letting the user enter the entire date, but I think
this may cause confusion. The Year-at-a-Glance worksheet has the year set in
its header, whilst each of the 12 monthly sections have the month set in
their headers. These cells are all locked and the user can only enter the
day and the action to be taken for each specific month. The day cells are
formatted as a 'number' and each cell has Validation set to allow only
numbers between 1 and 28/29/30/31, as appropriate to the particular month.

 

[joeu2004]

"joeu2004" suggested letting the user enter the entire date,
but I think this may cause confusion. [....]
the user can only enter the day and the action to be taken for each
specific month.

I concur, given these new facts. Previously, you had said that "users enter
important dates", and you went on to describe the 3 separate cells for day,
month and year. You said nothing of the fact that 2 of those cells are
filled in automagically; so I ass-u-me-d that the user had to fill in all 3
cells. It is in that context that I suggested it would be easier for a user
to fill in 1 cell instead of 3.

 

[Interloper]

I concur, given these new facts. Previously, you had said that "users
enter important dates", and you went on to describe the 3 separate cells
for day, month and year. You said nothing of the fact that 2 of those
cells are filled in automagically; so I ass-u-me-d that the user had to
fill in all 3 cells. It is in that context that I suggested it would be
easier for a user to fill in 1 cell instead of 3.

Fair enough, and thanks for your input.

In order not to write an originating post that ran into several volumes, I
précised the information.

The fact is I intend to highlight the cells progressively, such that the
cell changes colour when the action date is (say) 5 days away, and then
changes colour again (to a more urgent colour combination of bold red text
on yellow background) when the action date matches 'TODAY()'. This will
give users advance warning of an impending action and advise them that they
need to get their act together within a few days

I can write the Conditional Formatting formulae for this; all I was stuck on
was how to concatenate the three day/month/year cells into a meaningful
date.

And now I know ;-)

Thanks,