TransWikia.com

Adding of time in Excel gives a strange answer

Super User Asked by humanitiesclinic on February 6, 2021

I tried to add the following times:

4:28:31

and

17:51:13

and got:

46:19:44

using simply =firstCell+secondcell

all are in [h]:mm:ss format.

What could be the reason why the answer is wrong?

3 Answers

I suspect the problem was hidden information.

enter image description here

One of the times was actually the time shown plus a day. The time containing the extra day was formatted hh:mm:ss, so only the portion less than a day was displayed.

Answered by fixer1234 on February 6, 2021

try that one....

Enter your hours and minutes in the following format:

hh:mm            example         10:20        for 10 hours, 20 minutes, OR 
hh:mm:ss        example        10:20:32     for 10 hours, 20 minutes, 32 seconds

In excel's mind:

12 hours + 13 hours = 25 hours (in yours too?)

However it will display to you as:

12 hours + 13 hours = 1 hour (it rolls over to the next day to show a time-of-day format)

To see the correct total of hours:

Method A

Right Click on your total
Click on Format Cells On the Number Tab, under "custom" change your settings to [h]:mm

Method B

Right Click on your total Click on Format Cells On the Number Tab, Select "Number" and 2 Decimal Places In your formula bar, add *24 to the end of the sum formula (example =SUM(A20:G20)*24 )

difference between Method A and Method B is

In Method A, eight and a half hours will appear as 8:30 In Method B, eight and a half hours will appear as 8.50

Answered by Itachi Uchiha on February 6, 2021

If you format your cells in hh:mm:ss you get the correct result:

enter image description here

Answered by Leo Chapiro on February 6, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP