# Converting multiple non-standard time formats to one standard?

1. ## 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?

2. ## 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.

3. ## 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().

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

Very good point, dflak

5. ## 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.

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

Happy to help and thanks for the feedback

7. ## 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.

There are currently 1 users browsing this thread. (0 members and 1 guests)