Excel 2007 time and date problem

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

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 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
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.
0
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
=DATE(RIGHT(B2,4),LEFT(B2,2),MID(B2,3,2))

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:

=IF(RIGHT(B2,2)="AM",IF(LEFT(B2,2)="12",TIME(LEFT(B2,2)-12,MID(B2,3,2),MID(B2,5,2)),TIME(LEFT(B2,2),MID(B2,3,2),MID(B2,5,2))),IF(LEFT(B2,2)="12",TIME(LEFT(B2,2),MID(B2,3,2),MID(B2,5,2)),TIME(LEFT(B2,2)+12,MID(B2,3,2),MID(B2,5,2))))
0
Scrap the previous time formula, this is much neater:

=IF(RIGHT(B2,2)="AM",TIME(LEFT(B2,2)-IF(LEFT(B2,2)="12",12,0),MID(B2,3,2),MID(B2,5,2)),TIME(LEFT(B2,2)+IF(LEFT(B2,2)="12",0,12),MID(B2,3,2),MID(B2,5,2)))
0
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:
=DATE(RIGHT(A2,4),MID(A2,1,LEN(A2)-6),LEFT(RIGHT(A2,6),2))

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