Problems with subtracting time

[sanimushin]

I keep getting #VALUE! in 2 specific cells in my Column J while I subtract time.

Column J goes all the way from row 6, F6-E6, to row 155, F155-E155. All cells are working except for J19 and J53 that is giving me #VALUE!.

All cells are formatted h:mm:ss AM/PM. Is there any reason why only 2 cells are not working while everything else is fine and formatted the same way?

 

[AliGW]

What exactly is in F19 and E19?

And in F53 and E53?

 

[sanimushin]

F19 = 5:10:18PM
E19= 5:27-28 PM
J19= F19-E19. same with F53 and E53.

All of column F and E are time. and Column J is the subtraction of F and E. All other cells in Column J is fine except for J19 and J53. Only those 2 cells are giving me the #value! error

 

[sanimushin]

Correction F19 is 5:30:18 pm

 

[AliGW]

I think your problem is that the time you are trying to subtract is greater - you can't do negative time.

 

[James006]

Hi,

A couple of remarks ...
F19 = 5:30:18 PM you do need a space between 8 and P ...
E19= 5:27:28 PM you do need a colon between 27 and 28 ...

The difference is between F19 and E19 ...
J19= Mod(F19-E19,1)

Hope this will help

 

[snjpverma]

a hyphen instead of a colon in E19 is probably causing a problem. Otherwise the formula will work fine

 

[sanimushin]

Thanks everyone. The problem has been resolved. The problem was the space between 8 and p. Double checked all the numbers and corrected the errors.

 

[AliGW]

Thanks for letting us know!