Creating uniform time format from multiple formats

[heyredone]

I have received time punch data from client in multiple formats. I want to
create uniform time formats so I can conduct various calculations. I would
like initial format to be h:mm:ss AM/PM. I will alter format thereafter as
needed for calculations.

Some punch data is in General format (examples, 1:54:00 PM and 10.06) & some
are in Custom/h:mm (example, 12:30). Added issue is if I change the
Custom/h:mm entries to h:mm:ss AM/PM, any entry that is 1:00 or after will
change to 1:00 AM when it should be 1:00 PM.

I am using Excel 2003. Any help from the Discussion Group will be most
appreciated. Thank you!

 

[joel]

Excel will default to AM time when AM/PM is not specified. You can use theh
menu EDIT - REPLACE to fix the times by replacing AM with PM. If you have
normal working hours between 9 to 5 it is pretty simply to fix the times
between 12:00 AM and 5:00 AM. If you have people working 24/7 then it is
difficult.


Formulas and macros can simplifiy the task. You can add a new column with a
formula that can look at the text string time and convert the string where
changing the Cell format doesn't produce the correct time but again this
doesn't work when you have 24/7 time.