Solved

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

Posted on 2008-10-16
9
391 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
  • 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
Industry Leaders: 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

696 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