## Convert number to time?

307 Views 6 Replies Latest reply: Mar 3, 2013 9:07 AM by Jerrold Green1
Calculating status...
Currently Being Moderated
Feb 13, 2013 11:47 AM

Hi. I have a slightly odd question. Is there a way to get pages to convert a number to a time?

I have a document with a list of times, something took 5 minutes 26 seconds, but it was typed in as the number 5.26, is there any formula that could convert that number value into a time value?

I need to add up a total for a bunch of values written like this, but straight up adding will give me 11.99 instead of 12m39s.

Any suggestions would be very much appreciated.

• Level 2 (220 points)
Currently Being Moderated
Feb 13, 2013 6:05 PM (in response to Reddkryten)

The only way I can think to do this would be to use the Trunc function and seperate the minutes form the seconds, then sum up the seconds &amp; divide by 60. Let's say 5.26 is in cell a1. In cell b1, you could typt =Trunc(a1,0), that will give you the minutes. In cell c1 you could type =a1-b1, that will give you the seconds. then just paste it down, and then sum them up

• Level 7 (27,345 points)
Currently Being Moderated
Feb 14, 2013 5:49 AM (in response to Reddkryten)

Hi Redd,

Here's a solution in the same vein as mkral's, using a slightly different set of functions...

The expression in Column B is:

=DURATION(,,,INT(A),RIGHT(A,2))

INT(A) grabs what is left of the decimal and RIGHT(A,2) grabs what is to the right of the decimal. The DURATION function puts the minutes and seconds parts together into one Duration value.

Jerry

• Level 2 (220 points)
Currently Being Moderated
Feb 14, 2013 3:30 PM (in response to Jerrold Green1)

Jerrold, excellent tip. Hadn't seen that before, but that saves a step from the way I knew of. Thanks for sharing that.

• Level 7 (27,345 points)
Currently Being Moderated
Feb 15, 2013 10:27 AM (in response to mkral)

One problem with the solution I proposed is the format of the seconds upon entry. Is 10.3 the same as 10.03? If so, it would be necessary to determine the location of the decimal point using FIND and use that to set the number of characters to grab with RIGHT.

Jerry

• Level 7 (27,345 points)
Currently Being Moderated
Mar 3, 2013 9:07 AM (in response to Reddkryten)

You're welcome. Thanks for the feedback.

Jerry

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.