FORMULA THAT CALCULATES HH:MM ON THIRD SHIFT(BETWEEN TWO DAYS)

[Norman]

i am trying to find a formula that calculates time difference in hh:mm when
the shift runs between 22:00 and 07:00

 

[Teethless mama]

Assuming
A1= 22:00
B1= 07:00

=B1-A1+(A1>B1)

Format as hh:mm

 

[JE McGimpsey]

One way:

A1: 22:00
A2: 07:00
A3: =MOD(A2-A1,1)

Format A3 as elapsed time: [h]:mm

 

[Andy]

A1: 22:00

A2: 07:00
A3: =MOD(A2-A1,1)

I don't understand why this works. Isn't the remainder of -.625/1
still -.625 ? Since the result of MOD takes the same sign of the
divisor shouldn't A3 resolve to .625 (15:00)?

time passes....

Before I sent this, I looked again at help:

MOD(n, d) = n - d*INT(n/d)


I do understand why n - d*INT(n/d) works, but then the definition
of MOD ("Returns the remainder after number is divided by divisor. The
result has the same sign as divisor") doesn't seem accurate.

 

[Andy]

I now see that MOD does something unexpected (to me) with negative
numbers.

 

[JE McGimpsey]

Well,

-0.625 - 1 * INT(-0.625/1)

is equivalent to

-0.625 - 1 * (-1)

is equivalent to 0.375, or 08:00.

A1: 22:00
A2: 07:00
A3: =MOD(A2-A1,1)

I don't understand why this works. Isn't the remainder of -.625/1
still -.625 ? Since the result of MOD takes the same sign of the
divisor shouldn't A3 resolve to .625 (15:00)?

time passes....

Before I sent this, I looked again at help:

MOD(n, d) = n - d*INT(n/d)


I do understand why n - d*INT(n/d) works, but then the definition
of MOD ("Returns the remainder after number is divided by divisor. The
result has the same sign as divisor") doesn't seem accurate.
[/QUOTE]

 

[JE McGimpsey]

I suspect your confusion stems from INT()'s rounding *down* to the next
lowest integer, rather than toward zero.

 

[Andy]

I get that Int rounds down for negative numbers, and I understand why
-0.625 - 1 * INT(-0.625/1) = .375

So if Mod is defined as <<MOD(n, d) = n - d*INT(n/d)>>, I get it. (And
it's damn handy for figuring the difference between two times spanning
midnight.)

But if Mod is defined as "Returns the remainder after number is
divided by divisor. The result has the same sign as divisor", then I
don't get it.

If 11 is divided by 3, the remainder is 2.

What is the remainder of -11 divided by 3 ?
What is the remainder of 11 divided by -3 ?

-11/3 = 11/-3 = -(11/3) !!!

Ok, I can see when I do the long division with either the divisor or
the dividend being negative I am either subtracting 9 from -11 (=-20)
or subtracting -9 from 11 (=20), which is different from the remainder
when both divisor and dividend are positive, 2 (or both negative, -2).

I guess I never knew what the "remainder" was when the divisor and
dividend were different signs. It somehow ends up being what the
remainder would have been if all was positive, added to the divisor,
the divisor keeping its sign, and the remainder keeping the sign of
the dividend. Let's see... Oh, that would be n - d*INT(n/d).

Ok, I'm done