Some date/time cells appear in different format

R

RoadRunner66

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!
 
M

Marcelo

Hi,

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

hope this helps
regards from Brazil
Marcelo

"RoadRunner66" escreveu:
 
D

David Biddulph

RoadRunner66 said:
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?
 
R

RoadRunner66

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top