Conditional Formatting Using MOD and AND formulas

[Harrytheb]

Greetings, trying to conditional format a time entry (start time) if time difference of the preceding (stop time) time entry is less than 8 hrs. Used MOD(B5-H4,1) which gives me the time difference. Trying to use AND(>1,<8) which gives me the 8 hr limit but not sure how I use the conditional drop down to get my original time entry to change to red as per my starting desire. Can anyone make sense of what I am trying to achieve and better still come up with a workable solution? Many thanks as always.

Eg
Start Stop Hrs Worked
08:00 21:00 12:00
03:00 09:00 06:00

In the example I am trying to get 03:00 go red on condition it is less than 8hrs between the stop time 21:00 and 03:00 start time. There is a month's worth of start/stop times.

[JohnTopley]

Can you post a small file illustrating what you require.

[Harrytheb]

I have updated the original post to try to better explain what I am after. The start and stop times cover a month in this instance, I have just given an example.

Thanks for looking, appreciate your help. H

[Tony Valko]

If this is your data...

Data Range

	A	B	C
1	Start	Stop	Hrs Worked
2	8:00	21:00	12:00
3	3:00	9:00	6:00

Use a formula like this for the conditional formatting:

=AND(COUNT(A2,C2)=2,C2<TIME(8,0,0))

[Harrytheb]

Tried this and I could not get it to format. Trying to format the cell not based on it's value but the formula, so not sure which of the options on the drop downs I should be using.

[Tony Valko]

Works for me.

Here's a small sample file that demonstrates this.

[Harrytheb]

Thank you for your patience. I do not understand the syntax of the formula, would you put it in layman's terms? I have attached a portion of the file that I am building so that you can have a better idea. The columns that I am working with are greyed out.

Harrytheb2.xlsxExample1.xlsx

[Tony Valko]

I didn't download your files.

After reading the top post again I think I have misunderstood what you want to do.

Eg
Start Stop Hrs Worked
08:00 21:00 12:00
03:00 09:00 06:00

In the example I am trying to get 03:00 go red on condition it is less than 8hrs between the stop time 21:00 and 03:00 start time.

So, it's based on the stop time from the row above and the start time from the row below? Is that correct?

[Harrytheb]

Hi Tony, I have changed the post a couple of times for clarity. You are correct, it is the stop time from the row above and the start time from the next row.

[Tony Valko]

Based on the Harrytheb2.xlsx file...

Select the range B11:B19 starting from B11.

Conditional formatting
Formula:

=AND(COUNT(H10,B11)=2,MOD(B11-H10,1)<TIME(8,0,0))