Format expression to calculate aging (date/time entered vs now()

Posted on 2008-10-16
Last Modified: 2012-05-05
Hello all.  I am trying to build an expression so that supervisors will be able to look at the records and determine how long a document is pending.  I have a field on my table with a default value of NOW().  What I need to do is create a query that will show how long the escalation has been open.  
I used the following expression to try and obtain that value:
Aging: (Now()-[DateTimeEntered])
I need to format the values to reflect days:hours:minutes:seconds.  Any and all assistance is greatly appreciated!
Question by:Nikki28838
  • 5
  • 2
  • 2
LVL 23

Accepted Solution

irudyk earned 150 total points
ID: 22734601
Try something like:
Aging: Format((Now()-[DateTimeEntered]),"d:hh:mm:ss")

Author Comment

ID: 22734669
I tried that and came up with the following:
DateTimeEntered      Aging
10/13/2008 2:41:55 PM      2:00:26:51
10/15/2008 2:42:05 PM      31:00:26:41
10/16/2008 2:41:54 PM      30:00:26:52

Any other thoughts?

Author Comment

ID: 22734712
Now that I think about it, we can convert it to hours and minutes if that is easier.  Thank you in advance for your time.


Author Comment

ID: 22735108
I did some additional testing and it seems the problem occurs when the date entered is the same as the now() date.  If I change the DateEntered to a previous date, it works fine.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


Author Comment

ID: 22735113
Points increased to 150.

Author Closing Comment

ID: 31506877
I apologize for the many, many posts on this.  I modified your format to only include hours, minutes and seconds.  This works for me!  Thanks again!
LVL 49

Expert Comment

by:Gustav Brock
ID: 22735175
This function will do in a standard way.
You can adjust the output string, strYDMS, to your needs.

Public Function FormatYearDayHourMinuteSecondDiff( _

  ByVal datTimeStart As Date, _

  ByVal datTimeEnd As Date, _

  Optional ByVal strSeparatorDate As String = " ", _

  Optional ByVal strSeparatorTime As String = ":") _

  As String


' Returns count of years, days, hours, minutes and seconds of difference

' between datTimeStart and datTimeEnd converted to

' years, days, hours and minutes and seconds as a formatted string

' with an optional choice of date and/or time separator.


' Should return correct output for a negative time span but

' this is not fully tested.


' Example:

'   datTimeStart: #2006-05-24 10:03:02#

'   datTimeEnd  : #2009-04-17 20:01:18#

'   returns     : 2 328 09:58:16


' 2007-11-06. Cactus Data ApS, CPH.


  Const cintSecondsHour As Integer = 60& * 60&


  Dim intYears      As Integer

  Dim intDays       As Integer

  Dim intSeconds    As Integer

  Dim intHours      As Integer

  Dim datTime       As Date

  Dim strDatePart   As String

  Dim strTimePart   As String

  Dim strYDHMS      As String


  intYears = Years(datTimeStart, datTimeEnd)

  datTimeStart = DateAdd("yyyy", intYears, datTimeStart)

  intDays = Days(datTimeStart, datTimeEnd)

  datTimeStart = DateAdd("d", intDays, datTimeStart)

  intHours = DateDiff("h", datTimeStart, datTimeEnd)

  datTimeStart = DateAdd("h", intHours, datTimeStart)

  intSeconds = DateDiff("s", datTimeStart, datTimeEnd)


  ' Format year and day part.

  strDatePart = CStr(intYears) & strSeparatorDate & CStr(intDays)

  datTime = TimeSerial(intHours, 0, intSeconds Mod cintSecondsHour)

  ' Format hour, minute and second part.

  strTimePart = Format(datTime, "hh\" & strSeparatorTime & "nn\" & strSeparatorTime & "ss")

  strYDHMS = strDatePart & " " & IIf(datTime < 0, "-", "") & strTimePart


  FormatYearDayHourMinuteSecondDiff = strYDHMS


End Function

Open in new window

LVL 23

Expert Comment

ID: 22735202
Okay, well I'm not sure how you are getting the number of days by just using the hours minutes and seconds.  I'd think you would need to use something like:
Aging: DateDiff("d",[DateTimeEntered],Now()) & ":" & Format((Now()-[DateTimeEntered]),"hh:mm:ss")
But hey, if you got the results that you wanted, then leave well enough alone.
LVL 49

Expert Comment

by:Gustav Brock
ID: 22745274
You will need this function as well if you wish a working solution.
The line with Years() you can just comment out.


Public Function Days( _

  ByVal datDateFirst As Date, _

  ByVal datDateLast As Date) _

  As Long

' Returns the difference in full days from datDateFirst to datDateLast.

' 2007-06-27. Cactus Data ApS, CPH.

  Dim lngDays As Long


  lngDays = DateDiff("d", datDateFirst, datDateLast)

  If lngDays > 0 Then

    ' Decrease by 1 if time of first date is later than time of last date.

    If DateDiff("s", datDateLast, DateAdd("d", lngDays, datDateFirst)) > 0 Then

      lngDays = lngDays - 1

    End If

  End If


  Days = lngDays


End Function

Open in new window


Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now