# Convert non standard time formats to a standard time format

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

2. ## Re: Convert non standard time formats to a standard time format

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

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

5. ## Re: Convert non standard time formats to a standard time format

Originally Posted by AliGW
=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. ## Re: Convert non standard time formats to a standard time format

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

7. ## Re: Convert non standard time formats to a standard time format

self deleted by user

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

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

13. ## Re: Convert non standard time formats to a standard time format

Originally Posted by AliGW
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. ## 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. ## Re: Convert non standard time formats to a standard time format

Originally Posted by Bo_Ry
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. ## Re: Convert non standard time formats to a standard time format

Originally Posted by AliGW
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

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

##### Users Browsing this Thread

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

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