Change Text entered time to Time format.

[RD9686]

Hi All,

I have a formula to change text entered time ("10:00am") to time format as below, is there a short form of this?

=IF(ISNUMBER(SEARCH("am",T2))=TRUE,TIMEVALUE(SUBSTITUTE(T2,"am"," am")),IF(ISNUMBER(SEARCH("pm",T2))=TRUE,TIMEVALUE(SUBSTITUTE(T2,"pm"," pm"))))

 

[Marcelo Branco]

Maybe...

=IF(T2<>"",--REPLACE(T2,6,," "))

M.

 

[RD9686]

Maybe...

=IF(T2<>"",--REPLACE(T2,6,," "))

M.

Thank you for your reply, my intention is to get the text format time to be converted to any excel time format, as 10:00:00 AM or 10:00 AM
The above formula result to

"10:00a m"

<tbody>
</tbody>

 

[Marcelo Branco]

It worked for me

	T​	U​
1​	Text​	Time​
2​	10:00am​	0,416667​
3​	10:00pm​	0,916667​

Formula in U2 copied down
=IF(T2<>"",--REPLACE(T2,6,," "))

Then you can format U2:U3 as Time achieving...

	T​	U​
1​	Text​	Time​
2​	10:00am​	10:00:00​
3​	10:00pm​	22:00:00​

M.