Converting Minutes to Hours:Minutes in MS Access

There may be occasions when we need to convert the difference between a start time and an end time, and display the result in hours and minutes.  For example, suppose we set up an Access table field to record the time a person starts work (the session_start field) and another to record when s/he finishes (the session_end) field.  Rather than set up a third field to store the total time worked, we can instead calculate this value automatically using a calculated control in an Access form, or a calculated field in an Access query.

Above: the bottom text box (txtHoursWorked) is a calculated control. 
The CONTROL SOURCE is an expression based on the values contained in the top two text boxes
(bound to the session_start and session_end fields in the undelying table).

However, to do this we must use two expressions to calculate the time difference, and then display the time difference in the hrs:mm format.  So if we enter 20:40 as the session_start, and 22:06 for session_end, we need an expression to calculate the total minutes worked (which in this case is 86 minutes), and then convert the total minutes to hours/minutes format (which would be 01:26).  To achieve this we use the DateDiff and FormatDateTime functions.  Lets take a look at each function individually before combing them to produce the required result.

The syntax for the DateDiff function is as follows:

DateDiff("interval", time_1, time_2)

The function works by calculating the time difference between the time_1 and time_2 parameters, and returns the result in accordance with the interval parameter.  In our case, we want need the result returned as minutes, so enter "n" as interval.   Time_1 and Time_2 correspond to the start time and end time.  To get these values for the expression, we can we put a reference to the values contained in the text boxes for "Start time" and "End time".  In our example these text boxes are called txtStartTime and txtEndTime.  This what our DateDiff function looks once we add the parameter values:

DateDiff("n", txtStartTime, txtEndTime)

The value returned in our example is 86 minutes.

The second function that we are going to use is FormatDateTime which converts the value returned by the DateDiff function into hours and minutes.  The syntax we need to use in the FormatDateTime is as follows:

FormatDateTime( (minutes/(24*60) , named_format)

This first parameter is a division of the number of minutes (returned in the DiffFunction) by the total number of minutes in a day -  which in our example is 86/(24*60) - and returns the result in accordance with the value contained in the named_format parameter.  The latter is a numeric value indicating the required time format - which in our example is vbShortTime ie hours:minutes.  The numeric value we enter for the vbShortTime format is 4.  This is what our FormatDateTime function looks like once we add the parameter values:

FormatDateTime((86/(24*60) , 4)

The value returned from this expression is 01:26 - ie 1 hour and 26 minutes.

When we come to enter the actual expression used as the control source for the TOTAL (HRS:MINS) text box (referred to as txtHoursWorked in the screenshot below), we need to combine these two functions by nesting the DateDiff function inside the first parameter of the FormatDateTime function. This is done as follows:

FormatDateTime((DateDiff("n", txtStartTime, txtEndTime/(24*60) , 4)

Finally, we just need to enter this expression into the CONTROL SOURCE property of the
txtHoursWorked text box (see screen shot below):

The txtHoursWorked text box is highlighted on the left, and the expression used as the CONTROL SOURCE property value is highlighted on the right.

Calculating Date Difference: Using the DateDiff Function

Suppose you want to calculate the difference between two dates.  An example of this might be a Library Management System: somebody returns an overdue library book, and receives a fine based on the number of days it happens to be late.  Our two dates in this instance would be the DueDate and the actual DateReturned.  To calculate the difference between these two dates we may use the DateDiff Function.

We could use the DateDiff Function as a Calculated Control (just put an = sign in front of it and enter the function as the textbox's CONTROL SOURCE), or within a VBA Code Module.  In this example, however, we shall use it within an Access Query.

Let's take a look at how the DateDiff Function is constructed:

DateDiff("interval", FirstDate, SecondDate)

As you can see, the function passes three parameters: these are "Interval", FirstDate, and SecondDate.  The interval parameter allows us to specify whether we want the function to return the difference in, for example, days, weeks, or years.  In our case we are interested in how many days a book is overdue, so we enter "d" as a string value. (Had we wanted the interval in weeks, we would have entered the parameter as "ww"; or years as "yyyy".  There are also a number of other options available such as quarter: "q"; hours: "h"; and minutes: "n").  The FirstDate and SecondDate parameter's refer to the two dates between which the difference is to be calculated.  In our case, these dates are contained in the DueDate and ReturnDate fields of a table called jnkLoan. As such, we would construct the DateDiff Function as follows:

DateDiff("d", DueDate, ReturnDate)

Incidentally if the book was returned early, ie the DueDate is later than the ReturnDate, the function would return the Date Difference as a negative value.  As such, the order in which the two date parameters are entered will make a difference to the return value of the function. It is the same principle as the subtraction of a smaller number from a larger number, and vice versa. With the DateDiff Function, the value of the first date is "subtracted" from that of the second, to return the date difference which we defined in the interval parameter.

So lets take a look at how we may use this function in the context of an Access Query:

Figure 1: The DateDiff Function is used in the last column on the right.

For the sake of simplicity I have used a table with just three fields - LoanID, DueDate, and ReturnDate.  However, as you can see from the screen shot above, our query has four columns.  The first three columns contain the fields of our jnkLoan table, but the last column on the right is not bound to any field; it is actually a calculation based on the DateDiff Function that we constructed above. To use the function within this query we have just had to choose an alias for the column - ie a name we make up to refer to the column.  In this example, we have used an alias called Difference.  A colon is then used to separate the alias from our DateDiff Function.

Another thing worth mentioning is how our DateDiff Function refers to the DueDate and ReturnDate fields of the jnkLoan table.  As such our Query bases the DateDiff calculation on the date values contained in the relevant record (ie that which matches the criteria set in the LoanID column) of this table.  Here is the result:

Figure 2: Results of the Query using the DateDiff Function.
The result of the DateDiff calculation is displayed in
the last column on the right.

Our query criteria selected a record containing the LoanID 427648205.  The DueDate for this record was the 08/08/2011 and the ReturnDate is 12/08/2011.  Our DateDiff Function, displayed in the last column on the right, has calculated the book is being returned four days overdue.