Numbers Duration in cell

Excel doesnt really have a duration data format. It has time and a difference in time can be done using subtraction. the difference can be viewed in the format you are showing above "00:41:00" but if you view the custom format dialog in excel, you will see it is the same as 12:41:00 AM.

Meaning it is just a time past midnight formatted to show 12 am as 00.

So... that being said, once we understand how excel stores duration as time, i am thinking that we can just write Ians final formula in one cell, no extra columns needed.

=DURATION(,,HOUR(B2),MINUTE(B2),SECOND(B2))

(Yes i just did this to verify, sent from excel to my ipad and added the formula, worked perfectly, confirming that the number being brought over from excel is just a Time format)

Jason

Hi BGB,

Try this:

Formula in C2 =LEFT(B2,2)

Formula in D2 =MID(B2,4,2)

Formula in E2 =RIGHT(B2,2)

Formula in F2 =DURATION(,,C2,D2,E2)

In F, the two leading commas are in place of Weeks and Days.

I formatted F as Duration with the hms style (rather than the 0:00:00 style) to make it obvious that they are Durations.

Regards,

Ian.

Hi Jason,

=DURATION(,,HOUR(B2),MINUTE(B2),SECOND(B2))

Nice! I have learnt a lot from your replies.

jaxjason wrote:

It is a rare day that get to add to a response from Ian. I think this is just because I am in excel all day at work dealing with durations, times,dates, advanced functions... all .... day,.... every ..... workday

glad to help

jason

Thanks for the compliment!

Regards,

Ian.

It is a rare day that get to add to a response from Ian. I think this is just because I am in excel all day at work dealing with durations, times,dates, advanced functions... all .... day,.... every ..... workday

glad to help

jason