Solved

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

Posted on 2008-10-16
9
403 Views
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!
Nikki28838
0
Comment
Question by:Nikki28838
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 23

Accepted Solution

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

Author Comment

by:Nikki28838
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?
0
 

Author Comment

by:Nikki28838
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.

Nikki28838
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Nikki28838
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.
0
 

Author Comment

by:Nikki28838
ID: 22735113
Points increased to 150.
0
 

Author Closing Comment

by:Nikki28838
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!
0
 
LVL 51

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.

/gustav
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

0
 
LVL 23

Expert Comment

by:irudyk
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.
0
 
LVL 51

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.

/gustav


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

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

632 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