Excel conditional formatting comparing date and time to current date and time

Question

I have a style sheet in which I have to dates with times

-----------------
29/09/2011 12:00
29/09/2011 14:00
-----------------

If current date and time is 29/09/2011 11:50 then row is green

If current date and time is 29/09/2011 12:10 then row is orange

If current date and time is 29/09/2011 15:05 then row is red

@chris neilsen suggested the following:

Datevalue does what it says on the tin: gets the date. Yuo will need to add TimrValue to it to get date and time.

=DATEVALUE(LEFT(B2,FIND(CHAR(10),B2)-1))+TIMEVALUE(LEFT(B2,FIND(CHAR(10),B2)-1))>NOW()

But i cant get it to work.

Could anyone help me on this?

Answer 1

I'm not familiar with using style sheets with Excel, but have you tried just using the datetime in it's current form? i.e. try

= B2 > Now()

If you do need a more complicated formula, datevalue(date_text) and timevalue(time_text) will not help you. Try using date(year,month,day) and time(hour,minute,second).

Answer 2

In Excel Date and Time is a simple number

For example

29/9/11 20:37

To Excel is a 40815,8590277778 or 40815,86, use number format in cell with data to view a number.

In your conditional formatting this integer value for number is a date, and decimal as hours.

If your date is in A1 Cell use a formula

=A1-INT(A1) to get a decimal number, and analyse if until 12:00 decimal is 0,50 , and so on.

[]'s