+ Reply to Thread
Results 1 to 7 of 7

Converting multiple non-standard time formats to one standard?

  1. #1
    Registered User
    Join Date
    01-04-2018
    Location
    Surrey, British Columbia
    MS-Off Ver
    Excel 2013
    Posts
    8

    Converting multiple non-standard time formats to one standard?

    Hi all, I did a basic search and couldn't find this, but I'm hoping someone has the answer.

    I have a master sheet made up of several logs from various people that includes a list of times, but not everyone uses the same time format (there's at least h:mm, 00\:00, hh:mm...)

    All attempts to change this master list to the same format results in a portion of them screwing up.

    Thinking outside the box, I created a list of the number that Excel sees these times as (0.000694444444444444 and 1 equal 00:01, depending on what format they're coming from) and then tried a VLOOKUP to insert the correct time in the next column. It worked fine for the whole numbers, but those crazy long decimals came up N/A. I tried rounding them in both columns thinking maybe there were additional digits that I couldn't see, but that made no difference.

    Any suggestions?
    Last edited by RandyStone; 06-13-2018 at 06:04 PM. Reason: Solved

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,726

    Re: Converting multiple non-standard time formats to one standard?

    What you need to understand about dates and times in excel is…

    a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Wed 13 Jun 2018) is actually 43264

    Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

    With that said, can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,056

    Re: Converting multiple non-standard time formats to one standard?

    I've found that the decimal values of time aren't always that pure and that sometimes there is a difference in those "far out" decimal places. You might have to round to the nearest 10 decimal places. That should bring you down to fractions of a second which should be good enough for most business purposes.

    I've had to deal with this issue myself except some I had a mixture of actual dates and strings that looked like dates. I had to use = IF(ISNUMBER(A2),A2,DATEVALUE(A2)) and then format the cells to whatever date formats I wanted. You probably want TIMEVALUE() or maybe even a combination of DATEVALUE() + TIMEVALUE().
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,726

    Re: Converting multiple non-standard time formats to one standard?

    Very good point, dflak

  5. #5
    Registered User
    Join Date
    01-04-2018
    Location
    Surrey, British Columbia
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Converting multiple non-standard time formats to one standard?

    Rounding closer to the decimal did the trick (I was just doing one less than what was shown, but dropping to 10 was more than enough for me to get specific minutes), so I was able to use the VLOOKUP scheme.

    Thanks for your help, and your effort too Ford!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,726

    Re: Converting multiple non-standard time formats to one standard?

    Happy to help and thanks for the feedback

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,056

    Re: Converting multiple non-standard time formats to one standard?

    One more thing to consider the next time you're working with times, you can use Hour(), Minute() and Second() functions as well. They might work better in certain situations.

+ 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] Adding 2 Standard Time Formats
    By mycon73 in forum Excel General
    Replies: 2
    Last Post: 10-16-2016, 01:25 PM
  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. [SOLVED] Extracting Time from cell and converting it to Standard Time
    By Jietoh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-03-2016, 11:18 AM
  4. Replies: 4
    Last Post: 05-10-2014, 10:45 AM
  5. [SOLVED] Converting Standard Time format (hh:mm:ss) to number of minutes
    By xygrax in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-27-2014, 10:54 AM
  6. Converting all date formats to one single standard format.
    By booo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-25-2010, 07:14 PM
  7. Converting name to standard time
    By sridharKj in forum Excel General
    Replies: 1
    Last Post: 03-21-2005, 12:38 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