[AccessD] Convert Clarion Serial Date to Access Normal Date

Gale,

Actually, Microsoft Day 0 is: 12/30/1899, 12:00:00 AM.  That is 36161 
days after Clarion Day 0.

So, your function should be:

MSDate = CDate(ClarionDate-36161)

Incidentally, the smallest expressible MSDate is 1/1/100 (-642416), and 
the largest is 12/31/9999 (2973483).

-Ken


>  
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gale Perez
> Sent: Saturday, January 13, 2007 12:43 AM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Convert Clarion Serial Date to Access Normal Date
>
> Hello!
>
> I have been given an Access table, exported from Clarion, with a date field
> containing a serial number representing the date.  I understand that the
> Clarion date begins December 28,1800, and that Access begins with December
> 31, 1899, or 36162 days later.  I created a query to update the Clarion date
> to ClarionDate-36162 (although i've also seen advice to subtract 36161
> - I'll know which is right once I've converted the serial to a date), but
> how do I convert that serial number to a regular date?  I've tried
> CDate([MyNumber] and Format([MyNumber,"mm/dd/yyyy") (and I've tried
> rearranging the days, months and years in the Format function), but to no
> avail.  Could someone please advise me how to do this?
>
>
> Thank you very much for any suggestions,
> Gale
>