Malcolm Stewart
Systems Support Engineer, Microsoft Access
Created: July 7, 1993
This article explains how Microsoft® Access® stores dates and times so that you can do date/time manipulations that are not covered by the many date/time functions in Microsoft Access.
When you use Date/Time fields in Microsoft® Access®, you will find a wealth of functions provided to manipulate dates and times in various ways. But if you want to do something that these functions don't support, you will need an understanding of how Microsoft Access stores dates and times in order to do your own Date/Time calculations.
The Microsoft Access Date/Time data type is both simple and powerful in its implementation. Although Microsoft Access can present a date or time to you in several different formats, internally they are stored as double precision numbers.
In order to be able to store a date as a number, the date has to be converted to something other than the standard calendar format. The numeric representation of dates is called a Julian, or Serial, date. To do this, the date is converted to an offset from a fixed point in time.
In the case of Microsoft Access, day 0 is 12/30/1899, and all dates are stored as the number of days since this date. Thus 7/7/93 is stored as 34157, meaning 34,157 days since 12/30/1899. Negative numbers represent dates prior to 12/30/1899. Storing Serial dates is what makes Date/Time a powerful data type that is easy to manipulate.
Because dates are stored as numbers, you can subtract two dates to find the number of days between them, or add a number and a date together to find another date either in the future or the past. Try the following examples in the Immediate window of a module.
Find the number of days since the beginning of the year:
Print #7/7/93# - #1/1/93#
187
Find the date 45 days in the future:
Print #7/7/93# + 45
8/21/93
Find the date two weeks ago:
Print #7/7/93# - 14
6/23/93
Times are also stored numerically. Since adding 1 to a date represents 1 day or 24 hours, each hour is stored as .041666..., or 1/24 of a day. In Microsoft Access all times are stored as a fraction of a day. Each hour is 1/24 of a day, each minute 1/1440, each second 1/86400. So 3:00 is stored as .125 (or 1/8 of a day), and 16:00 is stored as 0.666..., (or 2/3 of a day). Conversely, 0.2 represents 4:48 hours (1/5 of a day), and so on.
As with dates, you can add and subtract times. Try the following examples in the Immediate window of a module.
Add a time interval:
Print #1:45PM# + #3:27#
5:12:00 PM
Subtract a time interval:
Print #1:45PM# - #4:06#
9:39:00 AM
Add two intervals:
Print #6:57# + #7:45#
2:42:00 PM
Note that in the last example, the internal data is correct but the default format is not appropriate for the calculation. Microsoft Access displays the result using the 12-hour default format, instead of a 24-hour format. You can use the Format function to force the correct format, or on a form or report, you can set the Format property of the field to "Short Time" in order to display using the 24 hour format.
Add two intervals that exceed 24 hours:
Print #15:45# + #12:02#
12/31/1899 3:47:00 AM
The result should be 1 day and 3:47 hours. Internally, the interval is stored correctly. However, Access has no format to display the "1" as "1 Day", so the "1" is displayed as a date, namely 12/31/1899 (1 day after 12/30/1899).
This brings to light an interesting feature about dates and times in Microsoft Access. Midnight is represented by .0, which means that 34,157 is the same as 34,157.0 or 7/7/93 0:00. Also, 0 represents 12/30/1899, so .125 is the same as 0.125 or 12/30/1899 3:00 a.m. Access normally suppresses displaying the time component if 0, and suppresses displaying the date component if 0, thus giving the appearance that dates and times are independent of each other.
When displaying time intervals of greater than 24 hours, the date portion is no longer 0 and Access will default to displaying a date. We can overcome this limitation of the Date/Time data type in one of two ways: The first is to treat the interval as a decimal value rather than a Date/Time value, as shown in the following examples.
Add two intervals and display as a decimal number of days:
Print format(#15:45# + #12:02#,"#.00") & " Days"
1.16 Days
Add two intervals and display as a decimal number of hours:
Print format((#15:45# + #12:02#)*24,"#.0" & " Hours"
27.8 Hours
Add two intervals and display as a decimal number of minutes:
Print format((#15:45# + #12:02#)*1440,"#") & " Minutes"
1667 Minutes
Note The ".00" and ".0" in the formats above tell Microsoft Access how many decimal places to display. The "#" in the format represents the integer portion of the number.
The second way to overcome the limitation is to use a custom function to treat the number of days as a decimal value, and the remainder as a time value. The following function will display the interval correctly:
Function DaysHoursMinutes (Interval)
Dim Days As Long, Remainder As Double
Days = CLng(Interval)
Remainder = Interval - Days
* DaysHoursMinutes = Days & IIf(Days = 1, " Day ", " Days ") &
* Format(Remainder, "h:mm") & " Hours"
End Function
Note Lines marked with * need to be entered as a single line in Access Basic.
Add two intervals and apply custom formatting:
Print DaysHoursMinutes(#15:45# + #12:02#)
1 Day 3:47 Hours
The same formatting principles illustrated in the previous example are also useful when applied to intervals calculated as the difference between two Date/Time values.
Calculate elapsed time as a decimal number of days:
Print #7/7/93 6:45PM# - #7/3/93 8:15PM# & " Days"
3.9375 Days
Display the elapsed time as a decimal number of hours:
Print (#7/7/93 6:45PM# - #7/3/93 8:15PM#)*24 & " Hours"
94.5 Hours
Display the elapsed time as a decimal number of minutes:
Print (#7/7/93 6:45PM# - #7/3/93 8:15PM#)*1440 & " Minutes"
5670 Minutes
Display the elapsed time using the DaysHoursMinutes function:
Print DaysHoursMinutes(#7/7/93 6:45PM# - #7/3/93 8:15PM#)
4 Days 1:30 Hours
Note When calculating the difference between Date/Time values, Microsoft Access defaults to a numeric format rather than a Date/Time format.
This covers the internals of Date/Time fields in Microsoft Access. By building on these examples and using the built-in Date/Time functions, you can create powerful date, time, and interval manipulation functions for your own database.
A side note: In the 16th century, the calendar was revised and lost about 11 days. In Microsoft Access, the conversion between serial dates and calendar dates does not take this gap into account. The only real effect of this is that (a) you can enter the 11 dates that never existed, and (b) you can't do accurate date math if the dates span this range.