Solved

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

Posted on 2008-10-16
9
402 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
Technology Partners: 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 50

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 50

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

734 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