Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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? –  Ellesa Sep 30 '11 at 2:18

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.