Some date/time cells appear in different format

[Guest]

I have a spreadsheet that was imported from a .csv file and some dates/times
appear in different formats:

5/28/06 12:00
5/29/06 0:00
5/29/06 12:00
5/30/06 0:00
5/30/06 12:00
5/31/06 0:00
5/31/06 12:00
1-JUN-2006 00:00
1-JUN-2006 12:00
2-JUN-2006 00:00
2-JUN-2006 12:00
3-JUN-2006 00:00
3-JUN-2006 12:00
(down through 9-JUN-2006 12:00)
6/10/06 0:00
6/10/06 12:00
6/11/06 0:00
6/11/06 12:00
....

I've tried selecting the entire column and format it using right-click,
Format Cells/ Number/Date then mm/dd/yy hh:mm and all other cells format
correctly, with the exception of the x-JUN-2006 cells. Even if I format
the entire column to just Time, all cells except the x-JUN-2006 files will
change to just the time.

I'm running Excel 2003 SP2.

Thanks in advance!

 

[Guest]

Hi,

try to check if x-JUN-2006 hh:mm is a text

hope this helps
regards from Brazil
Marcelo

"RoadRunner66" escreveu:

 

[David Biddulph]

I have a spreadsheet that was imported from a .csv file and some
dates/times
appear in different formats:

5/28/06 12:00
5/29/06 0:00
5/29/06 12:00
5/30/06 0:00
5/30/06 12:00
5/31/06 0:00
5/31/06 12:00
1-JUN-2006 00:00
1-JUN-2006 12:00
2-JUN-2006 00:00
2-JUN-2006 12:00
3-JUN-2006 00:00
3-JUN-2006 12:00
(down through 9-JUN-2006 12:00)
6/10/06 0:00
6/10/06 12:00
6/11/06 0:00
6/11/06 12:00
...

I've tried selecting the entire column and format it using right-click,
Format Cells/ Number/Date then mm/dd/yy hh:mm and all other cells format
correctly, with the exception of the x-JUN-2006 cells. Even if I format
the entire column to just Time, all cells except the x-JUN-2006 files will
change to just the time.

I'm running Excel 2003 SP2.

My guess is that those x-JUN-2006 cells were treated as text when you
imported them. You may do better renaming the csv as .txt and trying to
open the txt file. That way the wizard may give you more control of the
formatting.

What format were the data in your CSV file? How does that format reconcile
with your regional settings?

 

[Guest]

Thanks for the replies Marcelo & David.

I've tried formatting the entire "date/time" column into text and all cells
will change with the exception of the June 1-9 dates/times. I tried David's
suggestion of importing the file as .txt and got the same results. This is
what the raw data looks like:

....
"30-MAY-2006 00:00",60.3338,43.7193
"30-MAY-2006 12:00",59.9205,43.2570
"31-MAY-2006 00:00",58.4226,42.1589
"31-MAY-2006 12:00",58.3573,40.6349
" 1-JUN-2006 00:00",55.0066,39.0403
" 1-JUN-2006 12:00",57.9850,39.8434
" 2-JUN-2006 00:00",56.0331,39.7850
" 2-JUN-2006 12:00",56.2600,43.1455
" 3-JUN-2006 00:00",55.1072,42.4869
" 3-JUN-2006 12:00",57.2035,43.7381
" 4-JUN-2006 00:00",57.5862,44.8848
" 4-JUN-2006 12:00",59.4933,46.0359
" 5-JUN-2006 00:00",60.4092,47.0620
" 5-JUN-2006 12:00",58.9940,45.8698
" 6-JUN-2006 00:00",39.1809,30.9682
" 6-JUN-2006 12:00",22.3059,15.5199
" 7-JUN-2006 00:00",57.9736,48.5699
" 7-JUN-2006 12:00",75.4987,62.5386
" 8-JUN-2006 00:00",83.8871,69.9940
" 8-JUN-2006 12:00",84.6448,70.1891
" 9-JUN-2006 00:00",86.1247,71.5386
" 9-JUN-2006 12:00",85.3026,70.7611
"10-JUN-2006 00:00",86.3950,71.6824
"10-JUN-2006 12:00",86.7564,71.7800
"11-JUN-2006 00:00",88.6459,68.8288
"11-JUN-2006 12:00",86.7887,71.3019
....

I'm wondering if the lack of a preceeding "0" in the June 1-9 dates is
causing an issue?

Not sure how to answer David's question on the regional settings.

Thanks!