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.
Bookmarks