Using MS Access/Formatting a date


Good morning,

I have a question that I hope isn't overly simple. I have two sets of dates formatted in the following

Admit: 03/01/2013 15:45
FHR:  03/05/2013 15:45
Total: (the difference in time between FHR and admit#

The problem is that when I calculate this field there are a few problems.

1: negative entries #data collection/entry errors)
2: Some events may take months and I need to sort the values by Days:Hours:Minutes

When I do this [hh:nn];@ I get the right hh:mm but the hours don't seem to work once it hits 99 hours.

Admit: 7/17/2012 10:20:00 AM  
FHR: 7/31/2012 8:46:00 PM
Output/Total: [10:26]- Should be 14(days):10(hours):00(Min)

I am still very new to this and do not know code yet. Is there a way to format this date in the design view of each table for a proper output or perhaps create another field with a conversion?

If so how would I go about doing that?

Thank you so much!


No, because it doesn't matter how a date is formatted. Formatting is just for display. Access stores dates as a double precision number where the integer portion represent the number of days since 12/30/1899 and the decimal portion is a fraction of a day. So that 6AM (or 6 hours) = .25.

So you have to take this into account when doing date arithmetic.

First, lets talk about the negative results. This is handled by doing a check after FHR is entered to see if it is earlier than Admit. You can use the After Update event of the FHR control on your form to run code like:

If Me.FHR < Me.Admit Then
   MsgBox "FHR has to be later than Admit"
   Me.FHR = Null
End if

This will popup a message that FHR is earlier than Admit, clear what was entered and place focus in the FHR control so it can be re-entered.

No for the calculation. If you just subtract Admit from FHR and format as general Date the result will be in date notation and be 1/13/1900 10:26 AM. Which is not what you want. So the best way to handle this is with a custom function. Something like this:

Public Function TimeDifference(dteStart As Date, dteEnd As Date) As String

Dim intDays As Integer, intMin As Integer, intHrs As Integer, lngDiff As Long

lngDiff = DateDiff("n",dteStart, dteEnd)

intDays = lngDiff / 1440

intMin = lngDiff - (intDays * 1440)

intHrs = intMin /60

intMin = intMin MOD 60

TimeDifference = intDays & ":" & Format(intHours, "00") & ":" & Format(intMin,"00")
End Function

What this function does is first calculate the different in the two times in Minutes. It then divides that by 1440 (# of minutes in a day) to get the number of days). It then calculates the remaining minutes and does similar calcs to get the hours and minutes. It then outputs the difference as a string. In your example:


To use this, you would place the code in a Global Module and wherever you need to display the difference use:

=TimeDifference(date1, date2)

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.