+ Reply to Thread
Results 1 to 4 of 4

Separate time (hrs, min, sec) to other columns

  1. #1
    Registered User
    Join Date
    11-06-2003
    MS-Off Ver
    2013
    Posts
    75

    Separate time (hrs, min, sec) to other columns

    In the attached file I have time as h:m:s. I need to separate this data to three columns of Hours, Minutes and Seconds. I have put formulas in cells B-D and they work for the last two. The Hours column works until it is over 24, then I do not understand the number returned.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Separate time (hrs, min, sec) to other columns

    Please try at B2
    =INT(A2*24)

  3. #3
    Registered User
    Join Date
    11-06-2003
    MS-Off Ver
    2013
    Posts
    75

    Re: Separate time (hrs, min, sec) to other columns

    That worked, thank you! I had tried the INT but did not think about *24.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Separate time (hrs, min, sec) to other columns

    Edited: My formula was wrong. I was thinking outside the parentheses.
    What is happening is that an hour is 1/24th of a day. Days are expressed as whole numbers in Excel and it ignores days when it is doing hour calculations for the Function HOUR(ref). Hope that helps
    Last edited by ChemistB; 07-01-2020 at 03:48 PM.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ 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. [SOLVED] combine separate date and time columns into a single column
    By jbporcher in forum Excel General
    Replies: 7
    Last Post: 05-17-2018, 12:50 PM
  2. Replies: 7
    Last Post: 06-19-2017, 06:10 PM
  3. Replies: 2
    Last Post: 03-24-2016, 11:49 PM
  4. [SOLVED] How do I split the date & time into two separate columns?
    By bananajelly in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 09-22-2014, 03:26 PM
  5. Merging separate date and time columns into one
    By Gadgets in forum Excel General
    Replies: 5
    Last Post: 07-26-2006, 04:24 PM
  6. Replies: 0
    Last Post: 08-23-2005, 12:23 PM
  7. [SOLVED] Adding Time Values from Separate Hours and Minutes Columns
    By jeepers in forum Tips and Tutorials
    Replies: 2
    Last Post: 08-23-2005, 12:05 PM

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