Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from stackoverflow.com Sep 29 '11 at 17:58

This question came from our site for professional and enthusiast programmers.

1  
Where does "current date and time" (in the title) fall in your problem? – Kaze Sep 30 '11 at 2:18

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).

share|improve this answer
    
I cant see how to make it work. But Thanks. – Amra Oct 3 '11 at 12:21

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

share|improve this answer
    
I can't get this to work either as the DateTime to compare with is Now(). Could you please create a working example working with Now()? Thanks in advance. – Amra Oct 3 '11 at 12:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .