+ Reply to Thread
Results 1 to 12 of 12

Days:Hours:Minues

  1. #1
    Registered User
    Join Date
    04-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Days:Hours:Minues

    I have an excel column (imported from another tool) with times in the following Days:Hours:Minutes

    However the Days are not displayed if 0

    13:10
    1:02:07
    1:14:08
    01:00
    2:03:28
    1:07:10
    2:05:36
    1:16:56
    02:00
    00:00
    3:01:01
    1:08:00
    00:20
    02:42
    00:00
    1:19:39

    My end goal is to convert to Hours:Minutes so I can used the (Time Formula) Hour Formula, Minute Formula, Second Formula (Not Relevant)

    Not sure where to go from here?

    Any help greatly appreciated

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Days:Hours:Minues

    The days don't have to be displayed to be counted. In Excel, time is a function as it relates to a percentage of a whole day, which is 1. Therefore, 12 hours is really .5 and it just appears as 00:30:00 because it's formatted that way.

    I'd first begin by formatting all of the cells in the range to a custom format using Ctrl+1, then choosing Custom, and then putting dd:hh:mm:ss as the format. This will show the missing zeroes and confirm if the data was altered in the export. (Data alterations happen often when dumping from telecom software)

    Once you confirm the data is accurate, simple addition will show you the data you need.

    If instead you just want to see all the data as it related in purely hours etc, then choose a custom format such as [h]:mm:ss, which will show the total hours without spilling into days. The same can be done with [m] or [s] to show each cell as whichever total increment you choose.

  3. #3
    Registered User
    Join Date
    04-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Days:Hours:Minues

    Thank you... Here is where the wheels fell off.

    I highlighted the column and hit Cntr+1 (Which popped up the Format Cells Dialog Box)
    Changed Format to Custom and entered dd:hh:mm:ss

    time.PNG


    .... I am not seeing the full output. Still seeing

    00:00
    00:00
    13:10
    1:02:07
    1:14:08
    01:00
    2:03:28
    1:07:10
    2:05:36
    1:16:56
    02:00
    00:00
    3:01:01
    1:08:00
    00:20
    02:42

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Days:Hours:Minues

    This indicates that the data was exported as string values, even though the cells aren't formatted as text.

    You can force Excel to reconsider the formats by activating each cell by hitting F2 and Enter, or you could try another solution I've used in the past:

    Type a 1 somewhere in a blank cell. Then copy that cell. Next, select the range of times, right click, and hit Paste Special.., choose the multiply option and hit okay.

    Alternatively, you could also put in the next column =AH2*1 and then copy downwards.
    Last edited by daffodil11; 10-21-2013 at 03:17 PM.

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Days:Hours:Minues

    The problem with *1 is that it will work to convert 13:10 but it will treat 1:02:07 as 1 hour, 2 minutes, 7 seconds, so any straightforward conversion is problematic. Try this formula in an adjacent column

    =IFERROR(LEFT(A2,LEN(A2)-6),0)+IFERROR(RIGHT(A2,5),0)

    [assuming data starting at A2 - change as required]

    and then format the new column as d:hh:mm

    That should give you the correct values but note that the d part of d:hh:mm won't go higher than 31 so if your original data has days > 31 that won't work!

    Possibly better to format as [h]:mm to that 1:02:07 would convert to 26:07
    Last edited by daddylonglegs; 10-21-2013 at 03:32 PM.
    Audere est facere

  6. #6
    Registered User
    Join Date
    04-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Days:Hours:Minues

    The F2 works on individual cells. However I have 19,000 rows. I am not able to highlight the column and hit F2 .
    The Type a 1 somewhere in a blank cell. Then copy that cell. Next, select the range of times, right click, and hit Paste Special.., choose the multiply option and hit okay. (The format the Column to)
    It is still messing up the Days and Hours and Minutes.. Everything works except the days.. (Days is confusing it)


    Excel is confused --- I have some formats that are hh:mm and others that are dd:hh:mm

    00:00
    00:00
    13:10
    1:02:07
    1:14:08
    01:00
    2:03:28
    1:07:10
    2:05:36
    1:16:56
    02:00
    00:00
    3:01:01
    1:08:00
    00:20
    02:42
    24:09:31

    AHHHHHHHHHHHH (Nothing is simple anymore) LOL
    I love a challenge.
    I am stumpted

    Please keep the ideas coming. Thank you for the responses to date.

  7. #7
    Registered User
    Join Date
    04-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Days:Hours:Minues

    Thank you Daddy Long Legs

    This worked great
    =IFERROR(LEFT(A2,LEN(A2)-6),0)+IFERROR(RIGHT(A2,5),0)

    Then I converted Formatted Column (Ctrl + F1) dd:hh:mm


    Exceptional. Thank you very very much!
    Back in business

  8. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Days:Hours:Minues

    Awesome DLL!

    Where were you when I needed you 10 years ago?

    My final workaround which I used for years was pulling the whole string apart, converting to total seconds, and then back to decimal time.

    Sheer genius.

  9. #9
    Registered User
    Join Date
    04-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Days:Hours:Minues

    Its still bombing on 17:11:02 dd:hh:mm (See Image)

    time2.PNG

    It is working great on
    01:14:08
    02:14:57


    We are very close... what is missing?
    I am double checking work
    Last edited by dmcbrier; 10-21-2013 at 04:24 PM.

  10. #10
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Days:Hours:Minues

    I'm not getting the same issue.

    Can you dump your timestamps into a blank workbook and attach a dummy file so we can see?

    time string conversion.xlsx

  11. #11
    Registered User
    Join Date
    04-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Days:Hours:Minues

    <--- I added this row manually (And manually entered 10:14:16 to test) Excel is trying to convert to data and time format ??? Not sure

    I attached worksheet.


    No idea what format is importing.
    Attached Files Attached Files

  12. #12
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Days:Hours:Minues

    Yes, I see. Instead of DAY use INT as I did in my example.

    DAY returns the day of the month. You want the count of days and since time is a decimal value of 1, then the INTeger of that number is the count of days.


    INT(19.98564554654) = 19 days

    INT(19:25:22:30) = 19 days

+ 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] Function or macro to convert string with weeks, days, hours, minutes to Hours
    By kknb0800 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2012, 11:35 AM
  2. Convert days -> Years, Months, Days, Hours, Minutes, Seconds
    By brharrii in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-06-2012, 06:44 PM
  3. Replies: 0
    Last Post: 04-07-2011, 01:46 AM
  4. Converting hours to 8 hour days, 31 day limit? Formula blows up at 256 hours..
    By krfarmer in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-25-2010, 05:21 AM
  5. Convert Hours, Minues and Seconds To Time Decimal
    By Nett in forum Excel General
    Replies: 6
    Last Post: 08-07-2009, 05:22 AM

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