Converting Time again

[PCRIDE]

Hi, I've tried several things and excel is still not seeing the time as time.

The system exports the time data in text in this format, note AM and PM

09:00AM
08:00PM

So its like a hybrid 24 hour normal time. Note there is no space between the AM/PM and the time.

If I click into the time field and add a space and hit enter in Excel, Excel now see's it as time.

What formula is best for this, to retain the AM/PM format?

 

[KillerOfGiants]

=TIME(VALUE(LEFT(C1,2)),VALUE(MID(C1,4,2)),0)+0.5*(RIGHT(C1,2)="PM")

Assuming C1 is where the original data is.

 

[PCRIDE]

Excellent! thanks again, this forum never fails to deliver!! Works like charm

 

[MARK858]

Just another option, cells formatted as hh:mm AM/PM

Book1
	A	B
2	09:00AM	09:00 AM
3	11:12PM	11:12 PM
4	08:00PM	08:00 PM
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=--REPLACE(A2,6,0," ")

 

[felixstraube]

Another (untested) option that I can think of is to change time format in control panel/region/advances configuration/time from "h:mm tt" in the short time format to "h:mmtt". I think then when you open the fresh exported file it should recognize it as time.
As I said, I didn't test it but I think it will work.

 

[MARK858]

As I said, I didn't test it but I think it will work.

But won't it then not recognise times in the more standard time format which probably most times are in other workbooks?

 

[felixstraube]

If you open a excel file where you already have time stored in cells it will show them with the new format.

Anyway I was thinking of this just like of a workaround for opening these exported file only.
1. Change time format to h:mmtt
2. Open .csv files (or whatever type they are)
3. Save excel file with time column recognized correctly.
4. Change time format back to what it was.

 

[MARK858]

If you open a excel file where you already have time stored in cells it will show them with the new format.

Of course it will as it is only a number

Changing the regional format for a temporary fix is not something I personally would do especially as I am getting old and liable to forget to change it back

 

[felixstraube]

Of course it's personal preference. I used this approach in the past to open some CSV files and get the right data type.

One more thing that just occurred to me... would power query recognize the time as expected? Don't have desktop version of excel right now to test.

 

[MARK858]

One more thing that just occurred to me... would power query recognize the time as expected? Don't have desktop version of excel right now to test.

It will if it is a CSV (i.e. a comma after each time) in hh:mm:ss format as standard

Book1
	A
1	Column1
2	09:00:00
3	23:12:00
4	20:00:00
csv2