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.
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.


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.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

746 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

13 Experts available now in Live!

Get 1:1 Help Now