6 Replies Latest reply: Mar 3, 2013 9:07 AM by Jerrold Green1
Reddkryten Level 1 Level 1 (0 points)

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.


iPad, iOS 5.1
  • mkral Level 2 Level 2 (220 points)

    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 & 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

  • Jerrold Green1 Level 7 Level 7 (29,905 points)

    Hi Redd,

     

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

     

    Screen Shot 2013-02-14 at 8.49.02 am.png

    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

  • mkral Level 2 Level 2 (220 points)

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

  • Jerrold Green1 Level 7 Level 7 (29,905 points)

    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

  • Reddkryten Level 1 Level 1 (0 points)

    Thank you so much for this, I have just finished using this. I honestly cannot tell you how much time you saved me!

     

    Thank you.

  • Jerrold Green1 Level 7 Level 7 (29,905 points)

    You're welcome. Thanks for the feedback.

     

    Jerry