time difference - conditional formatting and fix

[Lukael]

Hi,

I have start/end cells for entering times on each days (with drop-down lists). In want to highlight end time of first day and start time of next day, if time difference between them is less than 11 hours. In my sample worksheet would that be cells C3 & D3. My current CF formula for that is :

=IF(TEXT(D3-C3+(D3<C3);"hh:mm")<"11:00";"TRUE";"FALSE")

First problem is that It only highlights one cell.

Second problem are my formulas for calculating time difference for each day ( merged cells B4 & D4, in sample). If I leave start cell empty and end cell filled, It correctly calculates. But If start cell is filled and end cell is empty, It calculates wrong

How can I fix this ?

Thanks for help !!

[etaf]

whats this formula doing
D3-C3+(D3<C3)

if D3 is less than C3 it will add 1 - if not it adds 0 (D3<C3) logical test means (TRUE = 1 FALSE = 0 )
is that what you want?

[Lukael]

whats this formula doing
D3-C3+(D3<C3)

if D3 is less than C3 it will add 1 - if not it adds 0 (D3<C3) logical test means (TRUE = 1 FALSE = 0 )
is that what you want?

Hi etaf,

this formula is calculating time difference from start/end cells of a certain day. I don't want to display anything if value for both (start and end) cells are not entered.

[FDibbins]

For the CF, highlight C3:D3, go into CF, dlete whatever rules you have there and use this instead...
=11/24<=IF($C3>$D3,1,0)+$D3-$C3

CF evaluates either TRUE or FALSE, so you dont need to include that in your formula.

for (2), see if you can adapt my formula...=IF($C3>$D3,1,0)+$D3-$C3...for what you need.

[Lukael]

Hi FDibbins,

I tried your CF formula and It works, I just had to turn "<" into ">" in formula

For second problem - adapting your formula worked only when start cell is filled and end cell is empty - however I had to change cell format to "hh:mm;0" so It displays 0 as result which is not quite correct. It should display something like 00:00 (as a time).

But when start cell is empty and end cell is filled, It still displays result of a end cell. I used your formula like this :

=IF($C3>$B3;1;0)+$C3-$B3

How can I fix this ?
Thanks for help

[FDibbins]

Not really sure where this is supposed to go, maybe add some cell ref's to your explaination?

Try this though...
=if(and(C3="",D3=""),"",IF($C3>$B3;1;0)+$C3-$B3)

[Lukael]

Tried this one too but It still doesn't work, same as before.

Explanation:

B3 - start time cell
C3 - end time cell
D3 - time difference

If B3 is "", then D3 should be result 00:00
If C3 is "", then D3 should be result 00:00
If B3 and C3 are both not empty, then D3 should be result of time difference

[FDibbins]

If B3 is "", then D3 should be result 00:00
If C3 is "", then D3 should be result 00:00

This cannot be done with a regular formula, you will need VBA for that.

You have a drop-down in in D3, so any formula you put in that cell will get replaced with any time that is selected - a cell can have either data, OR a formula, it cannot have both

[Lukael]

I'm sorry, It's a little bit early hour here in Europe, I misstyped
D3 should be B4, a cell right under start/end cells, It doesn't have any drop downs.

[FDibbins]

Ok...
B4=if(b3=0,0,c3-b3+(c3<b3))
d4=if(d3=0,0,e3-d3+(e3<d3))

[Lukael]

Tried this one too, but It still doesn't work. When you enter something in start time cells & nothing in end time cells, It subtracts the time from 24. Example : 3:00 entered - result 21:00 ...It works the other way though (when you enter in end time cell & nothing in start time cell)

Looks like this problem is a little bit heavier than I thought

[ThirtyTwo]

out of curiosity, is this related to EU Driver Hours Rules and Regs, for minimum 11 hours Daily Rest in between duties ?

[Lukael]

out of curiosity, is this related to EU Driver Hours Rules and Regs, for minimum 11 hours Daily Rest in between duties ?

Well, not quite, but you are very close. I'm not from UK as you are, but within EU we share a lot of simmilar laws and in our country all companies must follow the rule of 11 hours rest between duties, day or night shifts.

I'm doing this for my company, but we are not drivers

[FDibbins]

hmm maybe this?
=IF(OR(B3=0,D3=0),0,C3-B3+(C3<B3))

[Lukael]

Thanks, this one is worksbetter, I just had to change It to :

=IF(OR(B3=0,C3=0),0,C3-B3+(C3<B3))

But now I have problem with CF formula. I changed It to :

=10/24>=IF($C3>$D3,1,0)+$D3-$C3

because 11 hours between times is still allowed, but not 10 hours.

Problem now are night shifts. If I select for example a 18:00-06:00 on Sunday, CF should immediately highlight If I try to select start time cell value on Monday that is less than 11 hours after that time interval on Sunday. Current Cf does that too, but start time cell value has to be larger than end time of previous day. However, It highlights properly for 10 hours If value is higher.

I guess like I'm not making myself clear, so please take a look at attached sample, and focus only on cell C3 & D3. I set solution like a night shift on Sunday, and day shift on Monday was to be entered. That can be allowed only If 11 hours time difference between shifts.

Can this be even fixed ??


Thanks for all your help !!