+ Reply to Thread
Results 1 to 17 of 17

Convert non standard time formats to a standard time format

  1. #1
    Registered User
    Join Date
    10-31-2019
    Location
    India
    MS-Off Ver
    2007
    Posts
    8

    Lightbulb Convert non standard time formats to a standard time format

    Hi
    I have a column over 10,000 records of time in excel some are sorted as original time format of excel and some are mentioned as text and some as numbers ex(10.0, 3.30 etc.,)
    in this situation How can i convert the time non standard time formats to a standard time format with out changing the time value.

    I tried the following formulas but not getting correct results;
    1. =TEXT(LEFT(A1,2)&RIGHT(A1,2)/24,"h:mm am/pm")
    2. =IF(ISNUMBER(A1),A1,TIMEVALUE((A1)))

    please suggest formula to resolve this

    Thanks and Regards
    Sandeep Vemula
    Attached Files Attached Files
    Last edited by vemulasandeep; 10-27-2020 at 03:50 AM. Reason: Attachment

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,031

    Re: Convert non standard time formats to a standard time format

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Convert non standard time formats to a standard time format

    Read the yellow banner, top of page, and post a sample Excel sheet with a fully representative range of examples.
    Glenn



  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Convert non standard time formats to a standard time format

    Try this:

    =IF(LEN(B2)<8,(INT(B2)/24)+((B2-INT(B2))/14.4),B2)

    Set the column formatting to TIME.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    436

    Re: Convert non standard time formats to a standard time format

    Quote Originally Posted by AliGW View Post
    =IF(LEN(B2)<8,(INT(B2)/24)+((B2-INT(B2))/14.4),B2)
    12:00:00 and 0.50 will return the same results.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Convert non standard time formats to a standard time format

    What's your point? This suggestion works on the OP's sample.

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: Convert non standard time formats to a standard time format

    self deleted by user
    Last edited by bebo021999; 10-27-2020 at 04:59 AM.

  8. #8
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    436

    Re: Convert non standard time formats to a standard time format

    I mean the example is for illustration only. He has over 10,000 records.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Convert non standard time formats to a standard time format

    Sorry - I don't get your point. Do you have any suggestions yourself?

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: Convert non standard time formats to a standard time format

    @huuthang_bd
    It appears in desired output that OP expect #.40 as #h.40 m,
    11.50 as 11h50m
    You are translating 0.5 to be 1/2 day = "12:00:00", it should be "0:50" as per the OP's logic.

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,772

    Re: Convert non standard time formats to a standard time format

    Please try at D2
    =IF(--TEXT(B2,"h:m:s")=B2,B2,DOLLARDE(B2,6)/24)
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    436

    Re: Convert non standard time formats to a standard time format

    @bebo
    I didn't translate anything. I think he want 0.50 will be converted to 00:50:00 and 12:00:00 will not change, but the formula return the same results.
    Last edited by huuthang_bd; 10-27-2020 at 05:28 AM.

  13. #13
    Registered User
    Join Date
    10-31-2019
    Location
    India
    MS-Off Ver
    2007
    Posts
    8

    Re: Convert non standard time formats to a standard time format

    Quote Originally Posted by AliGW View Post
    Try this:

    =IF(LEN(B2)<8,(INT(B2)/24)+((B2-INT(B2))/14.4),B2)

    Set the column formatting to TIME.
    Hi, Thanks for the reply....!
    The formula works good for many records for some records the formula is showing wrong results for example, for 7:30:00, 22:30:00, 14:15:00, and 7:12:00 the formula results as 0:31:15, 1:33:45, 0:59:23 and 22:30:00 respectively.

    Regards
    Sandeep

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Convert non standard time formats to a standard time format

    You are going to need to provide a better set of sample data and expected results.

  15. #15
    Registered User
    Join Date
    10-31-2019
    Location
    India
    MS-Off Ver
    2007
    Posts
    8

    Re: Convert non standard time formats to a standard time format

    Quote Originally Posted by Bo_Ry View Post
    Please try at D2
    =IF(--TEXT(B2,"h:m:s")=B2,B2,DOLLARDE(B2,6)/24)
    Hi, Thanks for the reply....!
    The formula works great, How does "DOLLARDE" formula works and what is the significance of fraction "6" in the formula.

    Regards
    Sandeep

  16. #16
    Registered User
    Join Date
    10-31-2019
    Location
    India
    MS-Off Ver
    2007
    Posts
    8

    Re: Convert non standard time formats to a standard time format

    Quote Originally Posted by AliGW View Post
    You are going to need to provide a better set of sample data and expected results.
    The attached sheet consists of large set of sample data where the above formula is mentioned in which #VALUE fields represents the cells which are showing wrong results for the remaining cells the formula works good enough.

    Regards
    Sandeep
    Attached Files Attached Files

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Convert non standard time formats to a standard time format

    However, you seem to have an alternative suggestion that works for you, so it's a moot point.

+ 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] Converting multiple non-standard time formats to one standard?
    By RandyStone in forum Excel General
    Replies: 6
    Last Post: 06-14-2018, 07:40 AM
  2. Convert Multiple Date Formats Into Standard Format
    By Abdur_rahman in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-29-2016, 02:04 PM
  3. Replies: 4
    Last Post: 07-24-2013, 03:23 PM
  4. [SOLVED] How to I convert standard time to Military or 24 hour format?
    By Nacho in forum Excel General
    Replies: 5
    Last Post: 06-28-2006, 02:20 PM
  5. [SOLVED] how do i convert standard time format into seconds?
    By Raj in forum Excel General
    Replies: 4
    Last Post: 05-29-2006, 12:00 PM
  6. Convert data into standard military time format
    By geog in forum Excel General
    Replies: 2
    Last Post: 12-12-2005, 03:50 PM
  7. Replies: 0
    Last Post: 08-23-2005, 12:22 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