Excel 2007 time and date problemSolved/Closed

Question

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?

rizvisa1 · Answer

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.

David · Answer

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))))

Excel 2007 time and date problem

2 responses

Similar discussions

Subscribe To Our Newsletter!