Conditional Formatting Formula - Date & Time for intervals

[jamesfxd]

Hey, I am no excel guru and could only come up with =IF(HOUR(C2:C28)-HOUR($G$1)>4,TRUE,FALSE) but it doesn't account for the actual day without creating something a little more drastic. I know there's a simple way!

I am trying to highlight cells based on the time from now.
Green - Time < 2 hours from current time
Light Orange - Time is >= 2 hours but < 4 hours from current time
Dark Orange - Time is >= 4 hours but < 12 hours from current time
Red - Anything >= 12 hours

The data appears as:

02/01/2015 17:19
05/01/2015 20:59
06/01/2015 13:32
06/01/2015 14:13
07/01/2015 0:01
07/01/2015 0:39

Thank you in advanced. Apologies I couldn't search harder as I'm on a time restraint at work

[David A Coop]

Welcome to the forum James,

JamesFDX Cond Format..xlsx

I suggest you use FOUR Conditional Format conditions:

=$B1<$F$1
=AND(B1>=$F$1,B1<$F$2)
=AND(B1>$F$2,B1<=$F$3)
=B1>$F$3


I placed 2:00 in F1; 4:00 in F2; and 12:00 in F3

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

[jamesfxd]

Hi David,

JamesFDX Cond Format..xlsx

It's definitely a start. I updated the sheet with a sample of the output I am after. So the condition needs to be an output based on the current time & date when the data is entered into the cell.

(looking at the CallBack sheet) for example; if the current time (in H2) is 07/01/2015 16:00 (if it were to be a now() cell), then column D is to show the conditions where RED is anything that has been > 12 hours from H2. I am not sure how I would manipulate your conditions to include the date as well e.g. (sheet1) making rows 4 and up red because it was from previous days.

Thank you again (:

[David A Coop]

Hello Again,

I have made a change, and replicated your example (with the exception of Mark Elvin Elmido where I get a different result).

I hope this helps, please let me know!

JamesFDX Cond Format(v2).xlsx

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

[jamesfxd]

Works great! Thank you (:

[jamesfxd]

Hi David,

I ran the report this morning and received an odd output. I take that the condition isn't moving down the cells correctly as it's showing odd results.

acbIntraday.xlsx

This is the extract from this morning, all fields should be showing red.

Thanks,

James

[David A Coop]

Hi James,

When I opened the file, I could see that some of the cells were GREEN, however, when I clicked on the ENABLE EDITING button, it "recalculated" and all cells were RED.

Have you tried pressing F9?

This may not be the best solution all the time, but you can set something up to do it automatically, but this will involve macros, and many people are not willing to go there!

Another observation! It is not good practice to apply a Conditional Format to an entire column as you have done! It makes the file quite large, and can slow the calculation process. You also get a column of RED cells below your data.

Can you select a shorter range? What is the maximum range you would expect?

David