+ Reply to Thread
Results 1 to 6 of 6

Need assistance converting times stored in date format to number format.

  1. #1
    Registered User
    Join Date
    03-31-2014
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Need assistance converting times stored in date format to number format.

    One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it. Any help doing so would be greatly appreciated.

    Thanks.

    Edit: Further information. If I highlight the field the edit box displays |12:09:16 AM|
    Last edited by halabis; 03-31-2014 at 02:59 PM. Reason: clarification

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Need assistance converting times stored in date format to number format.

    If the original value is in A1 this might do what you want. I can't be sure as you don't supply any actual values in a worksheet.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,898

    Re: Need assistance converting times stored in date format to number format.

    Hi and welcome to the forum

    excel treats time as a decimal of 1 (day), so 9 minutes and 16 seconds is actually 0.00643460648148148 if you format it to general. Not sure if that will help you.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Need assistance converting times stored in date format to number format.

    If you have the decimal value as you show then with the value in A3

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Using 0.00643460648148148 this works out to be 555.95000 seconds or 556 seconds to the nearest second formatted as GENERAL.

  5. #5
    Registered User
    Join Date
    03-31-2014
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Need assistance converting times stored in date format to number format.

    That formula was perfect! Is there a way I can convert an entire spreadsheet at once without having to make a copy of it with that formula in every cell pointing to the origional sheet?

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Need assistance converting times stored in date format to number format.

    There is no way that I know of that will allow you to select the whole worksheet and apply a formula to it.

    If the values are in a column, enter the formula at the top of a neighbouring column and copy down the column. Or if the values are in a row, enter the formula in a row and copy across.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Converting from the 1904 date format to the 1900 date format without losing data
    By Patty McJorst in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-01-2020, 12:53 PM
  2. Converting a range of numbers stored as text to number format w/o looping
    By Excel_vba in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-23-2013, 04:40 AM
  3. Replies: 5
    Last Post: 02-08-2013, 11:06 AM
  4. Converting Six Digit Number to MM/YYYY Date Format
    By bschmeec in forum Excel General
    Replies: 1
    Last Post: 12-22-2011, 06:55 PM
  5. [SOLVED] Converting number or text to a Date Format
    By samhain in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 10-17-2005, 10:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1