Excel date format

  mickyricky 14:24 04 Apr 2005
Locked

I am using Excel to take data from a system that produces dates as eg 20050325 format but they are not recognised as date fields in Excel. I have tried to format the field as a date in various ways, but just get a line of ############## which doesn't appear to be a column width issue.
Can anyone please help me or point to to a good help site?
Thanks

  pauldonovan 14:30 04 Apr 2005

Or is it via a text file e.g. a csv file?

  pj123 14:51 04 Apr 2005

Highlight the column you want the dates to appear. Go to Format, Cells scroll down the Category to Custom and type in how you want the date to appear. eg. dd/mm/yy, or dd/mm/yyyy etc...

That's how I do it, I am using excel 2000.

  Simsy 16:07 04 Apr 2005

that you are , (it seems), importing a number, not a date.

What you need to do is convert that number to a date, (using a formula), and have the cell with the formula in it formatted.

The formula can be constructed using the various "text" functions, ie, LEFT, RIGHT etc.

I can get on to this later, but I'm just about to finish something at work... and then go home.

I'll check in tomorrow.

Regards,

Simsy

  mickyricky 16:20 04 Apr 2005

Thanks Simsy (and others for responding) - that seems to be exactly what I need - I really would appreciate your help when you have time.

  pauldonovan 16:44 04 Apr 2005

you didn't answer my question about source. I'd be surprised if it is a number as it is displaying as ####### even if he widens the column. For number I'd expect to see it when it was expanded.

I'm interested in the source because if it is a text file you can open it in say notepad before import to excel and confirm the format of the date there.

  Simsy 18:48 04 Apr 2005

perform any calculations with the date, or do you just need it displayed more conventionally?

pauldonavan... as this happens only when the cell is formatted to be a date the ###### is to be expected if it's a number...

The way Excel deals with dates and times an 8 digit integer is so far in the future it is outwith the timescale Excel can deal with. The biggest number Excel can deal with and interpret as a date is 2958465, which represents 31 Dec 9999


I'll get into this further later,


Regards,

Simsy

You will need an extra column to convert your 'number' to a normal date field.

You can hide the original column if required.

Assuming your number to be in A1 then format B1 to a date format of your choice and then place the following formula into B1

=DATEVALUE(RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4))

Simsy may well come up with a better way

HTH

  eego 20:51 04 Apr 2005

Mick.....

this is only a quick belts and braces fix for your problem, but it will at least convert your ######### into a date format.

Highlite the required data column,
1.select "Data" from the toolbar, then "Text to columns"
2. Step 1 press "Next"
3. step 2 press "Next"
4. step 3 select "Date", then in the right box select the format that you want it displayed in, eg "YMD". Press "Finish"

Your data should now be sorted.

Nice one eego

  mickyricky 21:41 04 Apr 2005

Thank you everyone for your kind assistance.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HP Envy x2 review: Hands-on

How Sketch and InVision have revolutionised our design workflow

The best tech gifts for Christmas 2017

Les meilleurs jeux de société (2017)