Subtract hour from time

[30percent]

Hi,

I tried to subtract 5 hours from a certain time with the following formula: =A1-(5/24). However, time that are after 24:00 resulted in #######.
Could someone please explain why that happened and how should I change the formula? Thanks

Time	After Subtract
23:00	18:00
0:30	##########
1:00	##########

 

[6StringJazzer]

Excel won't represent negative times. If a calculation results in a negative time, the cell will be filled with "#".

This formula will work for you. If the first time is earlier than 5:00 AM you have to add 1 to make it a day later, then subtract the 5 hours.

=IF(A1<5/24,1,0)+A1-5/24

 

[Scott Huish]

=MOD(A1-"5:00",1)

 

[6StringJazzer]

=MOD(A1-"5:00",1)

That is a really interesting solution. How does MOD return 0.970139 as the remainder of -0.029861/1 ?

 

[Scott Huish]

This is how Microsoft says the MOD function is evaluated:
n - d*INT(n/d)

SInce the denominator is 1, we can basically take that out because multiplying by 1 and dividing by 1 doesn't change anything.

so this reduces to n-int( n )
So if A1 is 1:30, then subtracting 5 hours shown as a decimal is -.1458333333

if you apply the INT function to this number, it will become -1

so

-.1458333333 - -1

becomes

-.1458333333 + 1 which is .854167

which when formatted as time is 20:30

 

[6StringJazzer]

shown as a decimal is -.1458333333

if you apply the INT function to this number, it will become -1

That's the surprise to me. I thought INT simply truncated the decimal portion and would give 0 in this case. But I've never used it with negative numbers. I see that the spec says "Rounds a number down to the nearest integer." To me that's odd behavior for negative numbers, but it is what it is.

 

[Rick Rothstein]

That's the surprise to me. I thought INT simply truncated the decimal portion and would give 0 in this case.

The function TRUNC does that.