Wrong count of time
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)-(E3F3)*MEDIAN(0;F3-5/24;17/24)-MAX(0;MIN(11/12;F3+(E3F3))-MAX(5/24;E3))
"H. Nissen" wrote:
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
|