+ Reply to Thread
Results 1 to 4 of 4

Seperate hours and minutes & seconds

  1. #1
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    109

    Seperate hours and minutes & seconds

    Is there an easy way to convert the a list of time values shown below separated with the colons to 3 separate columns without colons and just number values.

    07:10:35
    08:12:58

    When I try the left function or text to columns I get strange values

    Many thanks

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Seperate hours and minutes & seconds

    See HOUR, MINUTE, SECOND functions

    B1: =HOUR(A1)
    C1: =MINUTE(A1)
    D1: =SECOND(A1)
    format B1:D1 as 00 if you want to see leading 0's

    (assumes values in A1 onwards < 24 hours ie 25:32:28 would return 1 for HOUR - let us know if this an issue)

  3. #3
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: Seperate hours and minutes & seconds

    Many thanks, did not work at first but I realized this must be a format issue, as when I copy the original source data to a new column your formulas work ok

    Changing the format did not work, but copy and past of the source data to anew column sorted it.

    When I first tried I got 00:00:00 on all 3 functions.

    Thanks for your help

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Seperate hours and minutes & seconds

    Quote Originally Posted by junada0
    When I first tried I got 00:00:00 on all 3 functions.
    Yes, if formatted as Time that it is to be expected given HOUR, MINUTE & SECOND return Integers and in XL Integers equate to Days, Time is Decimal.

    To elaborate by means of Example:

    Please Login or Register  to view this content.
    B1 will show as 0.5 - this is because 12 hours equates to 1/2 a day (as 6 am is 0.25 and 6pm = 0.75)

    C1 will show as 12 as the Hour is extracted as an Integer.

    If C1 is formatted to say h:mm:ss it will show as 0:00:00 given 12 in XL DateTime terms equates to 12 days (288 hours) and once full days have been accounted for there is no Time remainder.
    Were you to format the cell as [h]:mm:ss you would see it return 288:00:00 - ie the cumulative hours would be displayed.

    The above has probably confused you more but in essence the key is that HOUR, MINUTE & SECOND return non-time values and the results should thus be formatted as General / Number and not Time.
    Last edited by DonkeyOte; 01-11-2010 at 08:37 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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