Create Excel from Access

[strive4peace]

@Valentine -- adding on

To know where to go to write a date, that's pretty easy too! Well, easy when you realize that dates are stored as numbers!

Date and Time is stored as a double-precision, floating-point number. The whole part of the number represents the date and the fractional part represents time. So you can add a number of days to a date, and subtract 2 dates to get how many days are between them

 

[strive4peace]

@Valentine

The whole part of the number represents date and the fractional part represents time (noon is halfway through the day so it's 0.5)

? CLng(Date)
44690

? Format(1,"mm/dd/yyyy")
12/31/1899

? CDbl(Now)
44690.6097222222
(it's currently 2:38 pm for me -- so 0.6 through the day)

? Date
5/9/2022

? Date- CDate(1)
44689 

? Date-CDate(0)
44690

? Date-7
5/2/2022 

? Now
5/9/2022 5:10:44 PM 

? Now-1.5
5/8/2022 5:10:47 AM

by storing dates this way, you can subtract 2 dates to find out how many days (or cells!) are between them

Format is just the way you see the number ~

 

[strive4peace]

@Valentine

on Access and Excel serial dates (what the numeric value of a date is called) ... this is from an article I wrote for Strategic Finance Magazine, explaining the Excel bug for dates

Access: Dates and Times​

https://sfmagazine.com/post-entry/june-2018-access-dates-and-times/

Excel stores date values in a similar fashion, but day 1 in Excel is January 1, 1900, which is a day later than in Access. So why the difference? When creating Excel, Microsoft chose to propagate a bug from Lotus 1-2-3 where February 29, 1900, was counted as a day; but 1900 wasn’t a leap year. By having the fixed dates for Excel and Access off by a day, the serial dates of the programs end up aligning on March 1, 1900, and continue to do so for every date after.

 

[The_Doc_Man]

I've always wondered about that one, Crystal. Thanks. One mystery solved.

 

[strive4peace]

thanks Richard ~ happy to shine a light

... and thanks for saying so

 

[The_Doc_Man]

But now comes the passing mystery... knowing the odds are low of any of US actually finding out, will Excel also say that 2100 is a leap year and get out of sync again?