Text date and time to time

[Bakerlady]

I need to convert text date/time Jun 1 2011 12:41AM to valid time only. I have tried numerous techniques that have failed. Please help - I have 212357 rows to interpret. I need the data time period analysis using Pivot.

 

[mikerickson]

Try =MOD(A1, 1)

 

[Scott Huish]

Are times like 1:00 PM written as 01:00PM or 1:00PM?

 

[Scott Huish]

MOD(A1,1) won't work. The initial format is not in a format recognized by Excel as a date/time.

If there is a leading 0 on single digit hours:

=REPLACE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),6,0," ")+0

Format as time.

 

[barry houdini]

This formula should work - leading zero or not

=TRIM(REPLACE(RIGHT(A1,7),6,0," "))+0