Excel 2007 time and date problem

[Solved/Closed]
Report
-
 David -
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
=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))))
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)))
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!