Time format question.

[ashoengot]

Lets say that i have a value in cell A1 11:00PM as a string is there any way to convert it to time value "11:00 PM"

i just want to get ideas on how to solve this through MACRO or just plain formating.


Thanks in advance

 

[delaneyjm]

You could use this formula to convert it (someone may suggest a better formula) to a number and then use Formatting to display it as Time

in B1, just enter:
=TIMEVALUE(REPLACE(A1,6,2," "&MID(A1,LEN(A1)-1,1)&"M"))

 

[Scott R]

Lets say that i have a value in cell A1 11:00PM as a string is there any way to convert it to time value "11:00 PM"

i just want to get ideas on how to solve this through MACRO or just plain formating.


Thanks in advance

Do a Find and Replace. Find = "PM". Replace = " PM", then format as Time.

 

[ashoengot]

Do a Find and Replace. Find = "PM". Replace = " PM", then format as Time.

Nice.... Thanks!

 

[ashoengot]

how about MACRO Solutions?

 

[ashoengot]

bump