Time format

**BillBeachwood** · 04-09-2008, 03:39 PM

Frustrated as hell. I’ve experimented for a few days trying to figure this out and have officially lost my mind. I’ve decided to join this forum. I hope you guys can help me.

I recently downloaded an Excel spreadsheet of my cell phone usage from Verizon to analyze my usage for my business so that I can pick a better plan with another provider once my contract expires in a couple of months.

The spreadsheet includes a column that lists the times of day that I had made my phone calls, however it lists it in a format that excel cannot define as time (ex: 12:00AM). Without the space between the AM/PM and the time, Excel simply regards it as a “General” format for the cell. When I select the whole column and try to convert it into the “hh:mm AM/PM” Time format, it still doesn’t recognize the data (because when I try to sort it, it gives me the option of “A to Z” but not “Oldest to Newest”).

When I go in and manually add the space between the AM/PM and the time, Excel has no problem formatting the cell for Time.

Unfortunately, there are over 800 rows of data and I can’t go in and manually add the space for each cell. I tried recording a macro where I go into the first cell and place a space between the numeric value of the time and the AM/PM, but when I run it on the next cell, it also copies the numeric value of the time:

For example:
I recorded a macro changing:

12:00AM

to

12:00 AM

But when I ran it on the cell underneath it, which was:

12:01PM

It turned that cell into:

12:00 AM

(The same value as the cell above it)

Does anyone know how I can have the macro add a space between the AM/PM field and the numeric value of the time without altering either field's actual value? Or if there is any other way I can attack this problem? Who knows, maybe macros aren't the way to go.

-Bill

P.S. I’ve also tried selecting the whole column and doing a custom format of the time by making it read as hh:mmAM/PM (without the space), but for whatever reason, it still doesn’t recognize it as time.
Any suggestions would be helpful. Thanks.