Formula to fix time format problem

[sai_rlaf]

Hello Everyone,

Right now I have a field with a date field in the format of HH:MMPM. THere is no space between the PM/AM thus excel does not recognize it as a date field and it won't work with my pivot tables. Does anyone know a forumla to add a space between the time and the PM/AM?

[shg]

Select the column and do Find and Replace. Replace A with <space>A, and P with <space>P

[mardyl]

try to format one cell : right click on the cell, choose format cell, choose custom then enter "hh:mm AM/PM". If it works use format painter to paste format to all required cells
Regards