+ Reply to Thread
Results 1 to 5 of 5

Convert Raw Age to Proper Time Format

  1. #1
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    133

    Convert Raw Age to Proper Time Format

    Keeping it short and sweet:
    We have a Col A (Age) and want the ouput converted to time as shown in Col B so we can sort it.

    Age, Output (mm:dd:hh:mm)
    3d 3h, 00:03:03:00
    16h 3m, 00:00:16:03
    14h 6m, 00:00:14:06
    1d 20h
    7d 13h
    12h 36m
    8h 48m
    1mo 7d
    16h 52m
    3d 13h
    2mo 1d
    12h 53m
    21h 55m
    5d 13h
    5d 19h
    20h 54m
    17h 30m
    21h 22m
    19h 49m
    4d 12h

    How do we do it with a formula?
    Last edited by nicoan; 12-24-2021 at 10:12 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Convert Raw Age to Proper Time Format

    There are instructions at the top of the page on how to post a sample sheet.Thanks

  3. #3
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Convert Raw Age to Proper Time Format

    Fair enough.

    Here:
    Attached Files Attached Files

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

    Re: Convert Raw Age to Proper Time Format

    Please try

    =TEXT(NPV(99,--TEXT(MID(0&A2,FIND({"mo","d","h","m "},0&A2&" modhm ")-2,2),"0;;;\0")*10^8),"00\:00\:00\:00")
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    07-22-2013
    Location
    london
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Convert Raw Age to Proper Time Format

    Ah, perfect..

    Thanks a million!!

+ 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] Text box input in a proper time format
    By thebeastslayer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-08-2020, 10:56 AM
  2. Time not in proper format in pivot table
    By aparnawangu in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-18-2017, 11:07 AM
  3. Replies: 2
    Last Post: 08-21-2013, 02:24 PM
  4. [SOLVED] Convert String to a Proper Date format
    By Boo123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-26-2013, 03:35 AM
  5. Convert string dates to proper numerical date format
    By Codpops in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-21-2008, 01:01 PM
  6. Proper Time format
    By nunya in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-30-2007, 04:31 AM
  7. Convert data into proper time format
    By [email protected] in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-25-2006, 02:45 PM

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