Conditional Formatting Formula - Date & Time for intervals

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.

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 (:

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.

Works great! Thank you (:

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

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