Convert text to time

[logstarter]

Hi,

I have a column of time showing like "01-JAN-19 03:23:22.120000 AM", I would like to ask how I could convert this text to time.

Thank you for your help.

 

[mrshl9898]

As long as it is a Value and not a String you could format the cells. Or use =TIME(HOUR(A1),MINUTE(A1),SECOND(A1)) to extract the time (again formatting the cells as this will default to a decimal number)

 

[Rick Rothstein]

Time only? If so, give this formula a try...

=0+MID(A1,11,99)

 

[sandy666]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DT = Table.TransformColumnTypes(Source,{{"Column1", type datetime}}),
    T = Table.TransformColumnTypes(DT,{{"Column1", type time}})
in
    T

Column1		Column1
01-JAN-19 03:23:22.120000 AM		03:23:22
01-JAN-19 03:23:22.120000 PM		15:23:22

 

[logstarter]

Time only? If so, give this formula a try...

=0+MID(A1,11,99)

seems not working.. it returns #VALUE!.

 

[logstarter]

As long as it is a Value and not a String you could format the cells. Or use =TIME(HOUR(A1),MINUTE(A1),SECOND(A1)) to extract the time (again formatting the cells as this will default to a decimal number)

but since the time is displayed in AM/PM format, how could I extract the value in the HOUR(A1) function?

Thank you.

 

[Rick Rothstein]

seems not working.. it returns #VALUE!.

Is what's in the cell exactly what you showed in quote marks in Message #1 (no leading or trailing spaces)?

 

[logstarter]

Is what's in the cell exactly what you showed in quote marks in Message #1 (no leading or trailing spaces)?

The cell is showing 01-JAN-19 03:23:22.120000 AM but if I move to the cell, the content is actually ="01-JAN-19 03:23:22.120000 AM"

Thanks.

 

[mrshl9898]

but since the time is displayed in AM/PM format, how could I extract the value in the HOUR(A1) function?

Thank you.

The value dictates the AM/PM, not the other way.

 

[logstarter]

i am now using the following formula to solve it.. see if there is a better solution to replace it.

=if(right(a1,2)="AM", time(mid(a1,11,2), mid(a1,14,2), mid(a1,17,2)), time(mid(a1,11,2)+12, mid(a1,14,2), mid(a1,17,2)))