Excel 2007 time and date problem

Karen - Feb 17, 2010 at 10:59 AM
 David - Jul 2, 2010 at 08:47 AM

I have a sheet that has a date column and a time column (brought in from a csv file).
My date column is showing in the format of MDDYYYY with no forward slashes and I need to change it to MM/DD/YYYY format. Formatting the cells isn't working, every format I pick comes back with #############

I also have a time column displaying such as 075458 PM for hours minutes and seconds and can't seem to get that to format as 7:54:58 PM. Can anyone offer a suggestion?

2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 17, 2010 at 11:10 AM
It is treating MDDYYYY as excel date serial.

Have you tried while bringing in the CVS data, to mark that column as DMY?

One option would be use a formula or a function to convert it into date if nothing else work.
I take it you mean the original data is in mmddyyyy?
In this situation, what I do is add an extra column and use the formula

You can then copy and "Paste Special - Values" onto the original column and delete the extra column if required.

The time query is a little more tricky as you need to account for 4 scenarios, AM/PM and hour=12 or not 12.

There's possibly a slicker way of doing it, but the following works:

Scrap the previous time formula, this is much neater:

In fact, scrap my date method as well, I've realised what you meant by mddyyyy now. This would work for a non-zero leading month:

If a mod wants to merge my posts together, feel free!