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

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!
Who is Participating?
irudykConnect With a Mentor Commented:
Try something like:
Aging: Format((Now()-[DateTimeEntered]),"d:hh:mm:ss")
Nikki28838Author Commented:
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?
Nikki28838Author Commented:
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.

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Nikki28838Author Commented:
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.
Nikki28838Author Commented:
Points increased to 150.
Nikki28838Author Commented:
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!
Gustav BrockCIOCommented:
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

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.
Gustav BrockCIOCommented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.