Access to Excel Date format

[Tonyjstone]

If you can assist I would be grateful:
We have a database in access which is 5 cols wide and 800 rows deep. One col shows date, and is formatted in access as 'long date'.
When we copy the database to an Excel sheet, (to facilitate manipulation as skills in Access are primitive), the date col comes over as text and we cannot reformat it as a date to allow a 'sort by date' function. All the usual format tools will not reformat the col as we need it
Can anyone advise me on how to get this col reformatted as a date in a format which will permit a "Sort by Date

 

[lupins46]

What version of Office?
How are you doing the export?

I would not expect this behaviour if your Access field is a datetime field.

 

[Tonyjstone]

The version is MS Office Pro 2003
The export was via "tools", then "analyse with MS Excel"

The field was set as a datetime field.

Thanks

 

[lupins46]

Can't replicate the problem.
When I use Analyze with XL on a report, then even though the dates appear in full text format in XL they sre still dates and can be sorted.

 

[SkipVought]

Hi,

I would not COPY the table.

Rather, in Excel, use Data/Get External Data -- and query your Access table.

You can return ALL or a portion of your table to perfom analysis on.

I use the technique frequently -- even perfer it over going direct queries in Access as I can use the results in Excel much easier.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[Tonyjstone]

Thanks lupins46 for your effort.
This problem is on a database at a colleagues office.
I also, could not replicate the problem and I reckon it is in the original formatting of the "Date " col when they created the database.
They say it is formatted as 'datetime' but.....
I have connected them with a local IT college in the hope that a tutor or student there will be able to assist.
Thanks for your time.

 

[xlbo]

Almost certainly, the column is not actually a Date/Time set of data but is actually text BEFORE being edited in excel.

There is no formatting trick to sort this out but there is a fairly simple set of steps to follow

1: Enter a 1 into any blank cell
2: Copy it
3: Select all your "Dates"
4: Follow Edit>Paste Special. Tick Values & Multiply
5: Re-Format the cells as date/time

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question