Converting Dates in Excel

[davidrsutton]

Hi...

For some reason, a load of dates in a spreadsheet are being displayed as 5 digit numbers.

Is there a way of converting these 5 digit numbers into dd/mm/yy???

Any ideas would be most appreciated.

Cheers,

Dave

 

[theo1881]

just checking the cells are formatted correctly ?

Laters, phat, headshape

 

[KenWright]

Sounds like they are actually unformatted. Todays date is 38750, which is 38,750 days since 1st Jan 1900. When formatted as a date using Format / Cells / Number Tab / Date Option, you will then get real dates.

Regards
Ken........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[davidrsutton]

Hi...

Nope I've tried formatting the fields they are in and everything like that.

The spreadsheet is an export from Access and some dates are fine, the rest are in this 5 digit format which I understand is how Access stores dates and times.

Thanks,

Dave

 

[KenWright]

The dates that appear to be fine, do you have any that are greater than the 12th day of the month?

Are you sure that the ones that appear fine really are fine, ie have you checked one of the records against the Access database to be sure that months and days didn't get reversed somehow?

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[davidrsutton]

Hi Ken...

Yep they're definately OK. I've just tried typing one of the numbers into a blank cell and formatting it and that works fine (when I manually type the date in) but if I try and format the original date (even if I paste it into another cell!) nothing happens.

I'm confused.....

Dave

 

[KenWright]

Select all your dates, do Data / text To Columns / Delimited, no delimiters, hit Next, choose Date, hit OK.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[davidrsutton]

Cheers for that Ken... I actually just managed to get them to change by copying all the numbers, then Paste Special into new fields with 'Value' and 'Add' ticked and that seemed to do the trick.

 

[xlbo]

yup - 'textual' dates

Often get this issue from Access - numbers are exported as text. In this case, dates are actually numbers (as Ken has explained) and they have come out as text

 

[KenWright]

What confused me was that some of them seemed to be OK. We have an issue at work where we use a database (Access Backed) Pricing Tool, and we normally export straight to Excel, and dump it all into a pivot table, and then group on dates etc.

Ever since we upgraded to an XP desktop, those same dates come out reversed, such that days = months and months = days. Excel can handle (albeit incorrectly) that situation when the day (hence the month) is less than or equal to 12, but otherwise we get 'dates' and text, and it's all wrong .

Data / Text To Columns fixes everything though as it reverse it back for us if we choose the right option. Go figure.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

Ken - it's almost certainly a combination of both. Depends on the order that Access does things in.