Multiply h:mm:ss?

mcintoshmc

Active Member
Joined
Aug 10, 2007
Messages
277
I am trying to multiply 0:35:15*1000 and I keep getting the result of 11:30:00. What am I doing wrong?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Which result you were expecting ...?
With 00:30:15 you get 00:10:00
 
Upvote 0
Try changing the cell format to:
Code:
[h]:mm:ss
if you want total hours, minutes, seconds.

It is important to understand how Excel stores date anf time. It stores dates as whole numbers, specifically the number of days since 1/0/1900. So time is just a fractional component of one day.
So, your multiplication is returning the right value, but the wrong format (or more accurately, not the format you are expecting).
 
Upvote 0
Which result you were expecting ...?
With 00:30:15 you get 00:10:00

If it takes 35 minutes and 15 seconds to build something, and you have to build 1000 of them, it will take longer that 11 hours and 30 minutes to build.
 
Upvote 0
Try changing the cell format to:
Code:
[h]:mm:ss
if you want total hours, minutes, seconds.

It is important to understand how Excel stores date anf time. It stores dates as whole numbers, specifically the number of days since 1/0/1900. So time is just a fractional component of one day.
So, your multiplication is returning the right value, but the wrong format (or more accurately, not the format you are expecting).

Thank you that worked. I had it set to h:mm:ss instead of [h].
 
Upvote 0
Your are welcome.

Yes, that returns a 24 hour clock, so it keep starting over.
If you add a date component, it will show the number of days (January 24, 1900, indicating 24 days) and then the leftover time after that.

So they are all correct, it is just how you want to present the results, which is all controlled by formatting.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top