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

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

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" wrote in message
...
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

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:

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

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:

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" wrote in message
...
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

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

[h]:mm

and then the formula will work correctly.

Rick


"H. Nissen" wrote in message
...
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:

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" wrote in message
...
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

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


"H. Nissen" wrote in message
...
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:

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" wrote in message
...
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

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)-(E3F3)*MEDIAN(0;F3-5/24;17/24)-MAX(0;MIN(11/12;F3+(E3F3))-MAX(5/24;E3))

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

Sincerely,

H. Nissen

"daddylonglegs" skrev:

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