drmelzie
Level 1 (4 points)
iWork

Q: Convert timevalue to text

Numbers 3.6.2

Mac OS X 10.11.6

 

I need to either display a decimal number created by the timevalue function in the format "hh:mm:ss" or using some formula to create the text equivalent of that time value. The Automatic data format just displays it as a decimal number. I've tried to change the data format to Date & Time but it stays a decimal. I've even tried Date & Time with None for date and "hh:mm:ss" for time but it still displays as a decimal number. Any explanation for why the number is not displayed in time format?

 

The TIMEVALUE function converts a time entered as text in "hh:mm:ss". There should be an easy way of converting a decimal time value to text in "hh:mm:ss" format. If there is what is it?

 

I'm usingThanks in advance!

Mac OS X (10.6.8)

Posted on Aug 23, 2016 10:36 PM

by SGIII,Solvedanswer
SGIII SGIII
Level 6 (10,796 points)
Mac OS X
A:

A simple way is to just add TODAY() to the decimal time value and format the cell to display as you want.

 

Screen Shot 2016-08-24 at 9.47.01 AM.png

 

SG

Posted on Aug 24, 2016 7:47 AM

Close
drmelzie

Q: Convert timevalue to text

  • All replies
  • Helpful answers

  • by t quinn,

    t quinn t quinn Aug 23, 2016 11:13 PM in response to drmelzie
    Level 5 (5,063 points)
    Mac OS X
    Aug 23, 2016 11:13 PM in response to drmelzie

    Hi drmelzie,

     

    TIMEVALUE() is designed to return a number. It is a way of striping a number out of a date/time value.

    It would help to understand why you are using TIMEVALUE() to begin with. What are you inputs?

     

    quinn

  • by Wayne Contello,

    Wayne Contello Wayne Contello Aug 24, 2016 6:29 AM in response to drmelzie
    Level 6 (19,421 points)
    iWork
    Aug 24, 2016 6:29 AM in response to drmelzie

    you should generally describe WHAT you want to do (omitting the how).  specifying timevalue or any other function right now is the HOW.  Focus on sharing what you want to do and let us assist you.

  • by SGIII,Solvedanswer

    SGIII SGIII Aug 24, 2016 7:47 AM in response to drmelzie
    Level 6 (10,796 points)
    Mac OS X
    Aug 24, 2016 7:47 AM in response to drmelzie

    A simple way is to just add TODAY() to the decimal time value and format the cell to display as you want.

     

    Screen Shot 2016-08-24 at 9.47.01 AM.png

     

    SG

  • by drmelzie,

    drmelzie drmelzie Aug 24, 2016 7:53 AM in response to t quinn
    Level 1 (4 points)
    iWork
    Aug 24, 2016 7:53 AM in response to t quinn

    I am using it to strip a time out of a text value and then doing math with it, like adding a time amount.

  • by drmelzie,

    drmelzie drmelzie Aug 24, 2016 8:00 AM in response to SGIII
    Level 1 (4 points)
    iWork
    Aug 24, 2016 8:00 AM in response to SGIII

    The TODAY()+ worked. I guess to format it it needed to be a Date-Time value. I would think that just plain time values would be a data type that Numbers would support. Looks like MS Excel does support just time values. I exported to Excel and was able to format the decimal number as hh:mm:ss without having to add TODAY()+ to the formula.

  • by SGIII,

    SGIII SGIII Aug 24, 2016 8:30 AM in response to drmelzie
    Level 6 (10,796 points)
    Mac OS X
    Aug 24, 2016 8:30 AM in response to drmelzie

    drmelzie wrote:

     

    I exported to Excel and was able to format the decimal number as hh:mm:ss without having to add TODAY()+ to the formula.

     

    Yes, handling of date and time is one area where Excel and Numbers differ.  Numbers always treats a time or date in a cell as a date-time string.  If you format it as "just a date" the time part is still there.  And vice versa. 

     

    In Numbers using TODAY() results in a date-time string that is today's date with a time of 00:00:00.  When you add the decimal time value to that date-time string you get a new date-time string, which you can then format to show either the date or the time, or both.

     

    Numbers has the concept of "duration" values in cells, which can be very handy. Check out the DURATION() function in Numbers, which a completely different animal from the Excel DURATION() that relates to bonds, plus DUR2DAYS(), DUR2HOURS() and other related functions.  Using durations in Numbers can simplify date-time math. For examples examples of how durations can be used in formulas see the Calendar and Employee Schedule templates at File > New in your menu.

     

    SG