Home

22:00 Hour Time display difference

%3CLINGO-SUB%20id%3D%22lingo-sub-912623%22%20slang%3D%22en-US%22%3E22%3A00%20Hour%20Time%20display%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-912623%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20come%20across%20an%20issue%20with%20a%20formula%20that%20I%20am%20working%20on.%20In%20the%20first%20cell%20I%20input%20my%20time%20in%2024%20Hour%20format%20I.E.%2021%3A30%20and%20in%20the%20second%20cell%20it%20will%20display%20(07%3A30)%2010%20Hours%20later%20and%20any%20time%20I%20input%20into%20the%20first%20cell%20will%20display%20the%20correct%20time%20in%20the%20second.%20However%20that%20is%20until%20I%20input%2022%3A00%20into%20the%20first%20cell.%20When%20I%20input%2022%3A00%20it%20displays%20(09%3A00)%20instead%20of%20(08%3A00)%2C%2011%20hours%20instead%20of%2010.%20does%20daylight%20saving%20time%20have%20anything%20to%20do%20with%20this%20and%20if%20so%20how%20do%20i%20fix%20this%20issue%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-912623%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-912702%22%20slang%3D%22en-US%22%3ERe%3A%2022%3A00%20Hour%20Time%20display%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-912702%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425702%22%20target%3D%22_blank%22%3E%40jonathonsalt010%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhich%20formula%20do%20you%20use%20to%20calculate%20time%20in%20the%20second%20cell%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-912877%22%20slang%3D%22en-US%22%3ERe%3A%2022%3A00%20Hour%20Time%20display%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-912877%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20not%20sure%20to%20be%20honest.%20the%20spread%20sheet%20was%20created%20years%20ago%20and%20the%20people%20i%20now%20work%20for%20are%20saying%20that%20this%20has%20been%20an%20issue%20ever%20since%20it%20was%20made.%20if%20i%20could%20send%20you%20the%20file%20you%20can%20take%20a%20look.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-913246%22%20slang%3D%22en-US%22%3ERe%3A%2022%3A00%20Hour%20Time%20display%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-913246%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425702%22%20target%3D%22_blank%22%3E%40jonathonsalt010%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBetter%20to%20append%20the%20file%20to%20the%20post.%20However%2C%20if%20it's%20with%20sensitive%20information%20send%20me%20a%20private%20message.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-915233%22%20slang%3D%22en-US%22%3ERe%3A%2022%3A00%20Hour%20Time%20display%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-915233%22%20slang%3D%22en-US%22%3E%3CP%3EI%20put%20the%20time%20in%20cell%20B7%20and%20the%20time%20difference%20will%20show%20up%20in%20cell%20M7%2C%26nbsp%3B%20however%20any%20time%20that%20I%20input%20will%20be%20correct%20even%20one%20minute%20before%20and%20one%20minute%20after%20except%2022%3A00.%20With%20a%20ten%20hour%20time%20difference%20the%20end%20result%20should%20be%2008%3A00%20but%20it%20will%20display%2009%3A00%20instead.%20Have%20you%20come%20across%20this%20before%3F%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-916871%22%20slang%3D%22en-US%22%3ERe%3A%2022%3A00%20Hour%20Time%20display%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-916871%22%20slang%3D%22en-US%22%3EAny%20thoughts%20what%20might%20be%20the%20cause%20of%20the%20issue%3F%3C%2FLINGO-BODY%3E
jonathonsalt010
Occasional 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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies