Date Format

[Guest]

We use a software program for our accounting records which runs off of an
Access Database. The database stores account numbers, balances and closing
dates etc. We have set up a query in Excel to extract certain pieces of data
into a spreadsheet so that we can analyze it. We are restricted from doing
anything in access by the software. When Excel pulls in the dates, it is
pulling them in as a number. I did look in the Access file database design
view and saw where the date is stored as a number. The number assigned for
12/31/03 is 2453005, 12/31/04 is 2453371 and 12/31/05 is 2453736. How can I
get these dates converted back to a normal date format? I tried changing the
formatting, but that doesn't work.

 

[Douglas J. Steele]

Okay, it looks as though the dates are the number of days since some
arbitrary 0 point (since the value for 31 Dec 2004 is 366 larger than the
value for 31 Dec, 2003, and the value for 31 Dec, 2005 is 365 more than the
value for 31 Dec, 2004)

Access uses 30 Dec, 1899 as its 0 point, so it stores 31 Dec, 2003 as 37986,
31 Dec, 2004 as 38352
and 31 Dec, 2005 as 38717.

Try subtracting 2415019 from the stored number, then using CDate on it.

 

[Guest]

Subtract 2415019 from the number. Set the format for the column to date.