Wrong count of time

[H. Nissen]

Hello

I simply can not understand that the following code does not work when "the
time" is equal to or less than 24:00

= IF (E3 = 0; 0; IF (AND (E3 <F3; MEDIAN (E3; F3; 0) = E3); MOD (MIN (TIME
(5, 0, 0); F3)-E3; 3); V (MIN (TIME (5, 0, 0); F3)-MAX (E3; TIME (22, 0, 0));
1)))

The formula must count the hours between 22:00 and 5:00 in a random time,
for example. 16:00 to 2:00 = 4 hours.

But if the term is before 24:00 counts it wrong.

T.ex. 16:00 to 24:00 it counts 7 hours. It should be 2 hours.

I hope some of you sharp minds to help me to solve this problem

Best regards

H. Nissen

 

[daddylonglegs]

Not clear what the V does in your formula but this will give you a count of
all hours between 22:00 and 05:00 given start time in E3 and end time in F3,
for any shift length up to 24:00

=MOD(F3-E3;1)-(E3>F3)*MEDIAN(0;F3-5/24;17/24)-MAX(0;MIN(11/12;F3+(E3>F3))-MAX(5/24;E3))

 

[Rick Rothstein \(MVP - VB\)]

I am pretty sure this formula will do what you want...

=IF(E3=0,0,(MIN(24*F3+2,7)-MOD(MAX(24*E3,22)+2,12))/24)

where I just repeated your condition that if E3 equals 0, the result should
be 0.

Rick

 

[H. Nissen]

I must have been something sleepy when I type it into V instead MOD.

But, thank you for your help. Your proposal seems perfect, count all the
hours between 22:00 and 5:00 and no more than that. Thank you for your help.

Sincerely,

H. Nissen



"daddylonglegs" skrev:

 

[H. Nissen]

Hello, Ricky

Unfortunately, your proposal has the same mistake as the original, the count
wrong as soon as the "end" is in 24:00.

The formula Daddylonglegs has proposed solve the problem. But thanks anyway
you for taking the time to make the proposal.

Sincerely,

H. Nissen

"Rick Rothstein (MVP - VB)" skrev:

 

[Rick Rothstein \(MVP - VB\)]

Custom Format your "end" time cell (F3) using this pattern...

[h]:mm

and then the formula will work correctly.

Rick

 

[H. Nissen]

Hello, Ricky

I use costum format [t] mm, but still do not I correct result.

If I write E3 = 16:00 and F3 = 23:30 the result is = 7:00. The correct
result would be = 4:00 (22:00 - 18:00 = 4 hours)

Has to translate the formel to DK lang so I wrote your formel like this:

=HVIS(E3=0;0;(MIN(24*F3+2;7)-REST(MAKS(24*E3;22)+2;12))/24)

HVIS = IF
MAKS = MAX

Kinds regards
H. Nissen

"Rick Rothstein (MVP - VB)" skrev:

Custom Format your "end" time cell (F3) using this pattern...

[h]:mm

and then the formula will work correctly.

Rick

Hello, Ricky

Unfortunately, your proposal has the same mistake as the original, the
count
wrong as soon as the "end" is in 24:00.

The formula Daddylonglegs has proposed solve the problem. But thanks
anyway
you for taking the time to make the proposal.

Sincerely,

H. Nissen

"Rick Rothstein (MVP - VB)" skrev:

 

[H. Nissen]

Hello Daddylonleg

I can not figure out where I have to change in the formula for a count of
hours between t.ex. 18:00 to 22:00 instead of 22:00 Thursday 05:00.

=MOD(F3-E3;1)-(E3>F3)*MEDIAN(0;F3-5/24;17/24)-MAX(0;MIN(11/12;F3+(E3>F3))-MAX(5/24;E3))

I hope you will show me how the formula so to look.

Sincerely,

H. Nissen

"daddylonglegs" skrev: