22:00 Hour Time display difference

Copper Contributor

I have come across an issue with a formula that I am working on. In the first cell I input my time in 24 Hour format I.E. 21:30 and in the second cell it will display (07:30) 10 Hours later and any time I input into the first cell will display the correct time in the second. However that is until I input 22:00 into the first cell. When I input 22:00 it displays (09:00) instead of (08:00), 11 hours instead of 10. does daylight saving time have anything to do with this and if so how do i fix this issue?

7 Replies

@jonathonsalt010 

Which formula do you use to calculate time in the second cell?

I'm not sure to be honest. the spread sheet was created years ago and the people i now work for are saying that this has been an issue ever since it was made. if i could send you the file you can take a look. @Sergei Baklan 

@jonathonsalt010 

Better to append the file to the post. However, if it's with sensitive information send me a private message.

I put the time in cell B7 and the time difference will show up in cell M7,  however any time that I input will be correct even one minute before and one minute after except 22:00. With a ten hour time difference the end result should be 08:00 but it will display 09:00 instead. Have you come across this before?@Sergei Baklan 

Any thoughts what might be the cause of the issue?

@jonathonsalt010 

Sorry, I missed your previous post. The reason is in rounding errors in intermediate formulas. I don't know why so complicated calculations are used. If you need only date and time on that date, formula in F7 is enough

 

=B7+D7/24+E7/1440

 

Next date will be

 

=A7+F7

 

and the time is just

 

=F7

 

Only apply proper date and time formats to these cells. Please see in columns P and R in attached.

@Sergei Baklan I appreciate the help, after looking at it the first time I thought it looked overly complicated for no reason. I just couldn't think of how to fix it. thank you.