Show All

Hide All

by Sal Ricciardi

Dates and times play a vital role in most database applications. In this article, learn how Access stores Date/Time values and how to calculate elapsed time, such as might be needed in a timesheet application. We provide a downloadable sample database with three sample functions you can copy and paste.

Certain things are fundamental to a database application, among them, the need to track dates and times. You work with dates and times in Access by using the Date/Time data type. First, you decide what subject-based tables you need. Then, in those tables, what fields you need. For each field, you choose a data type. Access supports a number of base data types, including Text, Memo, Number, Currency, AutoNumber, Yes/No, OLE Object, Hyperlink, and last but not least, Date/Time.

Date/Time is therefore a fundamental data type. You use it to store calendar dates and times so that you can take advantage of the rich set of related features in Access. For instance, Access can perform arithmetic calculations on dates; for example, you can calculate how many days have elapsed since the invoice date to age your accounts receivable. There's also built-in data validation so that you or your users can’t enter an invalid date, such as 2/31. Some example uses of Date/Time fields include invoice dates, entry dates for orders, time stamps, employee hire dates, start and end times, and in and out times for timecards.

Often, you'll want to specify a format so that Access displays a date or time the way you'd like. When you create a field in a table, for instance, you can enter a display format in the field's Format property (property: A named attribute of a control, a field, or an object that you set to define one of the object's characteristics (such as size, color, or screen location) or an aspect of its behavior (such as whether the object is hidden).). You can choose one of the many predefined formats or build a custom one where you specify precisely the placement of the month, day, year, hours, minutes, and so on.

One of the more common questions about the use of Date/Time values is how to calculate elapsed time and to format the result appropriately. Two solutions are shown here, but before we consider them, let's review how Access stores Date/Time values.

How Date/Time values are stored

Access stores Date/Time values internally as double-precision (double-precision: Characteristic of a number stored in twice the amount (two words; typically 8 bytes) of computer memory that is required for storing a less precise (single-precision) number. Commonly handled by a computer in floating-point form.) floating point numbers. Each such number contains a date component and a time component. The integer portion, to the left of the decimal point, represents the date. The fractional portion, to the right of the decimal point, represents the time.

The double-precision number shown here represents December 24, 2003, at 9:00 P.M. as 37979.875. That's because Access stores the number of whole days elapsed since a base date of 12/30/1899 as the date component. You'll find there are 37,979 days from the base date (12/30/1899) to December 24, 2003.

Negative values in the date component represent dates prior to the base date. For instance, a value of -1 as the date component resolves to one day prior to the base date, or 12/29/1899. The time component is a fraction of a 24-hour day. So, a time component of .875 equals 21 hours, or 9:00 P.M.

Because the way Access internally represents Date/Time values (as in our example, 37979.875) is not what you would normally work with, it makes sense to have the ability to convert a Date/Time value in internal double-precision format to a more familiar format. You can do this with CDate(), a built-in function in Access.

A function is a procedure that performs some unit of work and often returns a result. Access includes a long list of built-in functions that you can employ in your database applications to do such things as perform calculations, or, as in this case, convert from one data type to another. The CDate() function converts a double-precision value to a Date/Time value. You can use the CDbl() function to go the other way and convert a Date/Time value to its equivalent double-precision number.

Calculating elapsed time

Calculating elapsed time is simple. Access can calculate the time interval between two dates, even for large intervals that span months or years. Formatting and displaying the resulting value the way you want, however, often requires some creativity and a little dabbling in Microsoft Visual Basic® for Applications (VBA), the programming language that Access uses.

The HoursAndMinutes function shown here is a custom function that formats a time interval in an hours:minutes format. It's designed to correctly handle accumulated times such as might be summed for a weekly timesheet report. You pass the function a time interval or a Date/Time calculation (for example, #12/24/2003 11:00PM# - #12/10/2003 6:00AM#), and it returns a properly formatted string (in this case, 353:00). To specify an explicit Date/Time string, you enclose the date and time portions within pound signs (#). You should strive to specify both the date and time component when you provide a literal string, for clarity and to avoid mistakes.

Public Function HoursAndMinutes(interval As Variant) As String
'*************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'*************************************************************
  Dim totalminutes As Long, totalseconds As Long
  Dim hours As Long, minutes As Long, seconds As Long

  If IsNull(interval) = True Then Exit Function

  hours = Int(CSng(interval * 24))
  
  ' 1440 = 24 hrs * 60 mins
  totalminutes = Int(CSng(interval * 1440))
  minutes = totalminutes Mod 60
  
  ' 86400 = 1440 * 60 secs
  totalseconds = Int(CSng(interval * 86400))
  seconds = totalseconds Mod 60

  ' Round up the minutes and adjust hours
  If seconds > 30 Then minutes = minutes + 1
  If minutes > 59 Then hours = hours + 1: minutes = 0
  HoursAndMinutes = hours & ":" & Format(minutes, "00")

End Function

The HoursAndMinutes function works by calculating the individual hours, minutes, and seconds parts—in effect, deriving the interval's individual components. When you subtract one Date/Time value from another to produce an interval, you end up with a double-precision number with the number of days to the left of the decimal point and the fraction of the day remainder on the right. To get the hours component, multiply the interval by 24. The minutes component is derived by first multiplying the interval by 1,440, which is the number of minutes in a day. That yields the total number of minutes represented by the interval. Next, divide that number by 60 to remove the minutes already contained in our hours result, yielding a remainder that gives us the number we need (the Mod operator does this). A similar approach, calculating a day total and dividing to yield a remainder, produces the seconds component.

The remainder of the function consists of rounding up the result to hours and minutes and constructing the result string.

Sometimes you want a more verbal and readable result string. Here's an alternative that accepts two Date/Time values and returns the elapsed time as one long string that looks like this: "10 days, 20 hours, 30 minutes, 40 seconds".

Public Function ElapsedTimeString(dateTimeStart As Date, _
                                  dateTimeEnd As Date) _
                                  As String
'*************************************************************
' Function ElapsedTimeString(dateTimeStart As Date,
'                            dateTimeEnd As Date) As String
' Returns the time elapsed between a starting Date/Time and
' an ending Date/Time formatted as a string that looks like
' this:
' "10 days, 20 hours, 30 minutes, 40 seconds".
'*************************************************************
  Dim interval As Double, str As String, days As Variant
  Dim hours As String, minutes As String, seconds As String

  If IsNull(dateTimeStart) = True Or _
    IsNull(dateTimeEnd) = True Then Exit Function

  interval = dateTimeEnd - dateTimeStart
  days = Fix(CSng(interval))
  hours = Format(interval, "h")
  minutes = Format(interval, "n")
  seconds = Format(interval, "s")

' Days part of the string
  str = IIf(days = 0, "", _
    IIf(days = 1, days & " Day", days & " Days"))
  str = str & IIf(days = 0, "", _
    IIf(hours & minutes & seconds <> "000", ", ", " "))

' Hours part of the string
  str = str & IIf(hours = "0", "", _
    IIf(hours = "1", hours & " Hour", hours & " Hours"))
  str = str & IIf(hours = "0", "", _
    IIf(minutes & seconds <> "00", ", ", " "))

' Minutes part of the string
  str = str & IIf(minutes = "0", "", _
    IIf(minutes = "1", minutes & " Minute", _
                  minutes & " Minutes"))
                  
  str = str & IIf(minutes = "0", "", _
                  IIf(seconds <> "0", ", ", " "))

' Seconds part of the string
  str = str & IIf(seconds = "0", "", _
    IIf(seconds = "1", seconds & " Second", _
        seconds & " Seconds"))
  
  ElapsedTimeString = IIf(str = "", "0", str)

End Function

The ElapsedTimeString function works by first subtracting the start time from the end time to get the time interval. Next, it determines the individual day, hour, minute, and second components by using the Format function. Then, the function constructs a result string with the pieces combined the way we want, accounting for singular results ("Day" instead of "Days") and eliminating unnecessary components like "0 seconds."

Creating the functions

You can download the sample database for this article. In the database you’ll find the Elapsed Time VBA code module containing the HoursAndMinutes and ElapsedTimeString functions. To create the functions yourself, fire up Microsoft Access and follow these steps:

1. On the File menu, click Open, and then click the database where you want to insert the functions.
2. In the Database window, click Modules.
3. Click New to create a new module.
4. Type the HoursAndMinutes and ElapsedTimeString functions exactly as you see them here. Or, if you downloaded the sample database, copy and paste the functions from the Elapsed Time module.
5. On the File menu, click Save.
6. Type a module name, such as Elapsed Time, and then press ENTER.
7. On the Debug menu, click Compile.
8. On the File menu, click Close and Return to Microsoft Office Access.

After you place the functions in your database, you can use them in Access objects such as queries, forms, and reports.

Employing elapsed time in queries, forms, and reports

Suppose you want to see how long it’s taking each product order to ship. So you construct a query that uses the Invoices table and the Customers table. The Invoices table holds the OrderDate and ShippedDate fields. You need these so you can pass their values to the ElapsedTimeString function, and so you can see when an order was placed and when it shipped. The Customers table holds information like CustomerName, so you can identify the customer for each order.

To add a TimeToShip calculation to the query, you add an expression to the Query grid. An expression is a combination of operators (such as = and + ), control names, field names, functions that return a single value, and constant values. For example, to add the TimeToShip calculation, you place the following expression in the Field cell of a new column in the Query grid.

TimeToShip: ElapsedTimeString([OrderDate],[ShippedDate])

This expression indicates two things to Access: First, that you want the title of the column that results to be "TimeToShip." Second, that you want this column to contain the result of evaluating the ElapsedTimeString function, with OrderDate and ShippedDate passed as the parameters. When Access processes the query, it evaluates the expression for every row in the query result. An example result looks like this.

The OrderDate and ShippedDate fields contain Date/Time values, but the times are not really recorded; they default to 00:00:00, or 12:00 midnight. As long as this remains the case, the time to ship value will always be some number of days—the hours, minutes, and seconds are not needed. When you're only interested in days, you can use the following ElapsedDays function, which eliminates some unnecessary code from the ElapsedTimeString function.

Public Function ElapsedDays(dateTimeStart As Date, _
                            dateTimeEnd As Date) _
                            As String
'*************************************************************
' Function ElapsedDays(dateTimeStart As Date,
'                      dateTimeEnd As Date) As String
' Returns the time elapsed in days between a starting
' Date/Time and an ending Date/Time formatted as a string
' that looks like this:
' "10 days" or "1 day".
'*************************************************************

  Dim interval As Double, days As Variant

  If IsNull(dateTimeStart) = True Or _
    IsNull(dateTimeEnd) = True Then Exit Function
  interval = dateTimeEnd - dateTimeStart
  days = Fix(CSng(interval))
  ElapsedDays = IIf(days = 1, days & " Day", days & " Days")

End Function

You might also want to display the elapsed time in a form or a report. Begin by placing a text box control on the design surface of your form or report. A control is an object that displays data, performs an action, or otherwise decorates your form or report. The text box control is one in which you can enter or display text, such as the result of a calculation.

To add the TimeToShip calculation to a report, add an expression to the Control Source property of your text box. Properties are attributes associated with controls, and Access has loads of them. The Control Source property indicates to Access where to get the data for the control. To place the elapsed time in the text box, insert the following expression in the Control Source property of the text box.

=ElapsedTimeString([OrderDate],[ShippedDate])

Pay special note to the equal sign (=) that starts the expression. When you set a Control Source property, the equal sign is required because it indicates an expression, not a field name, to Access.

Or, suppose you want to create a timesheet form to track the number of hours and minutes that all your employees work. You create your form so that it shows a TimeIn field and a TimeOut field. Now you need to calculate the hours and minutes worked in a day. This is a great opportunity to use the TimeandMinutes function. First, create your HoursWorked text box, and then insert the following expression in the Control Source property of the text box.

=HoursAndMinutes([TimeOut]-[TimeIn])

An example form and report that illustrates the output from using the ElapsedTimeString and HoursAndMinutes functions looks like this.

The TimeSheet form is actually an outer TimeSheet form linked to the Employees table, with an inner TimeSheet subform that's linked to the TimeSheet table. To create the Total column shown on the TimeSheet form, first you create a text box on the TimeSheet subform and give it a name, let's say "SummedHoursMinutes." Then, set its Control Source property to

=HoursAndMinutes(Sum([TimeOut]-[TimeIn]))

Next, create the Total text box on the outer TimeSheet form, and set its Control Source property to SummedHoursMinutes.

=[TimeSheet Subform].Form!SummedHoursMinutes

Download the sample database

The sample database includes the Elapsed Time module with the functions mentioned earlier, the example forms and query, a sample report, and sample data drawn from the Northwind.mdb sample database that is included with Access.

For more information

• For more information about Date/Time values, see these Microsoft Knowledge Base articles: ACC2000: Storing, Calculating, and Comparing Date/Time Data and ACC2000: Functions for Calculating and Displaying Date/Time Values. The functions shown earlier in this article are modified versions of functions in these articles.
• For a fascinating journey through calendar history, see Duncan, David Ewing. Calendar: Humanity's Epic Struggle to Determine a True and Accurate Year. Avon Books, Inc., 1998.
• For a useful reference and guide to working with VBA, see Getz, Ken. Gilbert, Mike. VBA Developer's Handbook. SYBEX, Inc., 2001.