How to convert Excel Time to Numbers Time?

Hi again, Jed,

I suspect that Numbers is importing the Excel "Time" as Text. If I am wrong and Numbers accepts a time of day, this method will still work.

Formula in C2 =HOUR(B2)

Formula in D2 =MINUTE(B2)

Formula in E2 =SECOND(B2)

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

Formula in F3 = DURATION(,,C3,D3,E3,)

(The extra commas are "Placeholders" for weeks, days and milliseconds that we don't need.)

Confusion may arise from the Cell Data Formats for Duration, h m s

and 0:00:00

That looks like a Time, but it isn't!

Also, please consider tt2's suggestion of decimal time.

Tested in Numbers for Mac version 5.2 under macOS High Sierra. Screen shots may not match what you see in iOS.

Regards,

Ian.

Hi Jed,

In Numbers, h m s is a Duration format, not a Time format. What are you trying to do?

Regards,

Ian.

Hi Jed,

It looks as if

• the Total formula is trying (and failing) to add two times of day. (In Numbers, that makes no more sense than adding today and tomorrow.) or
• the Total formula is trying (and failing) to add two Text values.

In Numbers, you can

• add one or more Durations together, or
• add one or more Durations to a starting Time (or a Date).

Select the cell containing 5:30:48 and/or 9:23:12

What is the Cell Data Format?

Automatic? Date & Time? Duration?

I doubt that either "Time" is a Duration. AFAIK, Excel does not use Duration format.

You can extract the Hours, Minutes and Seconds and then reconstruct them into a Time or a Duration

Posting now before the Time-Out. More to come.

Regards,

Ian.

The trick might be to format the values in the Excel spreadsheet as decimal numbers before copying into Numbers where you can set the format to duration.

tt2

In Excel use this custom format string: hh\h mm\m ss\s

tt2