DateDiff function

**joym** · 02-14-2017, 01:59 AM

Click image for larger version.
<br /><script async src=

Name: x.jpg Views: 25 Size: 75.4 KB ID: 27494" class="thumbnail" style="float:CONFIG" />

i Have inserted a picture of a form i created on access

i am calculating the total outage time with the following equation

Code:

=DateDiff("n",[Resolution Date And TIme],[Date And Time Fault Lodged])/60

the answer i get is in decimals. i want this to be shown in Hours and minutes format eg. instead of -0.93333333 i what it to show "0 hours 55 minutes"

the second issue in am facing is i want to put a condition on another field thats also calculating a time difference

Firstly i need it the equation to check if Resolution Date And time is bigger than Resolution Time Frame if so then i want it to perform the following equation

Code:

=DateDiff("n",[Resolution Date And Time],[Resolution TIme Frame])/60

else display the number zero

**ssanfu** · 02-14-2017, 02:11 AM

the answer i get is in decimals. i want this to be shown in Hours and minutes format eg. instead of -0.93333333 i what it to show "0 hours 55 minutes"

See http://www.accessmvp.com/djsteele/Diff2Dates.html

Firstly i need it the equation to check if Resolution Date And time is bigger than Resolution Time Frame if so then i want it to perform the following equation else display the number zero

Maybe

Code:

=IIF( [Resolution Date And Time] > [Resolution TIme Frame],DateDiff("n",[Resolution Date And Time],[Resolution TIme Frame])/60,0)

**Bob Fitz** · 02-14-2017, 02:54 AM

Copy the following code into a new module:

Code:

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date, _  Optional ShowZero As Boolean = False, Optional iYears, Optional iMonths, Optional iDays, _
  Optional iHours, Optional iMinutes, Optional iSeconds) As Variant
'Author:    ) Copyright 2001 Pacific Database Pty Limited
'           Graham R Seach MCP MVP gseach@pacificdb.com.au
'           Phone: +61 2 9872 9594  Fax: +61 2 9872 9593
'           This code is freeware. Enjoy...
'           (*) Amendments suggested by Douglas J. Steele MVP
'
'Description:   This function calculates the number of years,
'               months, days, hours, minutes and seconds between
'               two dates, as elapsed time.
'
'Inputs:    Interval:   Intervals to be displayed (a string)
'           Date1:      The lower date (see below)
'           Date2:      The higher date (see below)
'           ShowZero:   Boolean to select showing zero elements
'
'Outputs:   On error: Null
'           On no error: Variant containing the number of years,
'               months, days, hours, minutes & seconds between
'               the two dates, depending on the display interval
'               selected.
'           If Date1 is greater than Date2, the result will
'               be a negative value.
'           The function compensates for the lack of any intervals
'               not listed. For example, if Interval lists "m", but
'               not "y", the function adds the value of the year
'               component to the month component.
'           If ShowZero is True, and an output element is zero, it
'               is displayed. However, if ShowZero is False or
'               omitted, no zero-value elements are displayed.
'               For example, with ShowZero = False, Interval = "ym",
'               elements = 0 & 1 respectively, the output string
'               will be "1 month" - not "0 years 1 month".
' Modified 10/13/03 by Pat Hartman to also output separate date part fields


On Error GoTo Err_Diff2Dates


   Dim booCalcYears As Boolean
   Dim booCalcMonths As Boolean
   Dim booCalcDays As Boolean
   Dim booCalcHours As Boolean
   Dim booCalcMinutes As Boolean
   Dim booCalcSeconds As Boolean
   Dim booSwapped As Boolean
   Dim dtTemp As Date
   Dim intCounter As Integer
   Dim lngDiffYears As Long
   Dim lngDiffMonths As Long
   Dim lngDiffDays As Long
   Dim lngDiffHours As Long
   Dim lngDiffMinutes As Long
   Dim lngDiffSeconds As Long
   Dim varTemp As Variant


 '  Const INTERVALS As String = "dmyhns"
   Const INTERVALs2 As String = "dmyhns"


'Check that Interval contains only valid characters
   Interval = LCase$(Interval)
   For intCounter = 1 To Len(Interval)
      If InStr(1, INTERVALs2, Mid$(Interval, intCounter, 1)) = 0 Then
         Exit Function
      End If
   Next intCounter


'Check that valid dates have been entered
   If Not (IsDate(Date1)) Then Exit Function
   If Not (IsDate(Date2)) Then Exit Function


'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
   If Date1 > Date2 Then
      dtTemp = Date1
      Date1 = Date2
      Date2 = dtTemp
      booSwapped = True
   End If


   Diff2Dates = Null
   varTemp = Null


'What intervals are supplied
   booCalcYears = (InStr(1, Interval, "y") > 0)
   booCalcMonths = (InStr(1, Interval, "m") > 0)
   booCalcDays = (InStr(1, Interval, "d") > 0)
   booCalcHours = (InStr(1, Interval, "h") > 0)
   booCalcMinutes = (InStr(1, Interval, "n") > 0)
   booCalcSeconds = (InStr(1, Interval, "s") > 0)


'Get the cumulative differences
   If booCalcYears Then
      lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
              IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
      Date1 = DateAdd("yyyy", lngDiffYears, Date1)
   End If


   If booCalcMonths Then
      lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
              IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
      Date1 = DateAdd("m", lngDiffMonths, Date1)
   End If


   If booCalcDays Then
      lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
              IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
      Date1 = DateAdd("d", lngDiffDays, Date1)
   End If


   If booCalcHours Then
      lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
              IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
      Date1 = DateAdd("h", lngDiffHours, Date1)
   End If


   If booCalcMinutes Then
      lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
              IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
      Date1 = DateAdd("n", lngDiffMinutes, Date1)
   End If


   If booCalcSeconds Then
      lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
      Date1 = DateAdd("s", lngDiffSeconds, Date1)
   End If


   If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
      varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
   End If


   If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
      If booCalcMonths Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
      End If
   End If


   If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
      If booCalcDays Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
      End If
   End If


   If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
      If booCalcHours Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
      End If
   End If


   If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
      If booCalcMinutes Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
      End If
   End If


   If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
      If booCalcSeconds Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
      End If
   End If


   If booSwapped Then
      varTemp = "-" & varTemp
   End If


   Diff2Dates = Trim$(varTemp)
   iYears = lngDiffYears
   iMonths = lngDiffMonths
   iDays = lngDiffDays
   iHours = lngDiffHours
   iMinutes = lngDiffMinutes
   iSeconds = lngDiffSeconds


End_Diff2Dates:
   Exit Function


Err_Diff2Dates:
   Resume End_Diff2Dates


End Function

Then use:

Code:

=Diff2Dates("hn",[Resolution Date And Time],[Resolution TIme Frame])

**joym** · 02-14-2017, 03:01 AM

Thanks worked like a charm. It's strange i did try exactly the same code or i thought i did. Mine looked something like this mostly without the spaces was getting you entered an operand without an operator. guess i missed something else.

=IIF([Resolution Date And Time]>[Resolution TIme Frame],DateDiff("n",[Resolution Date And Time],[Resolution Time Frame])/60)

**joym** · 02-14-2017, 03:23 AM

This did not work. I am fairly new to access so i must have input it correctly. i'll try and explain what i did.

The field i want this equation is Total Outage Duration which i named txtTOD

so i clicked on the fields properties, selected events, selected on click, and copied the whole code between

Private Sub txtTOD_Click()

End Sub

the following portion of the code was red is there anything wrong in this

Code:

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date, _  Optional ShowZero As Boolean = False, Optional iYears, Optional iMonths, Optional iDays, _
  Optional iHours, Optional iMinutes, Optional iSeconds) As Variant

Originally Posted by Bob Fitz

Copy the following code into a new module:

Code:

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date, _  Optional ShowZero As Boolean = False, Optional iYears, Optional iMonths, Optional iDays, _
  Optional iHours, Optional iMinutes, Optional iSeconds) As Variant
'Author:    ) Copyright 2001 Pacific Database Pty Limited
'           Graham R Seach MCP MVP gseach@pacificdb.com.au
'           Phone: +61 2 9872 9594  Fax: +61 2 9872 9593
'           This code is freeware. Enjoy...
'           (*) Amendments suggested by Douglas J. Steele MVP
'
'Description:   This function calculates the number of years,
'               months, days, hours, minutes and seconds between
'               two dates, as elapsed time.
'
'Inputs:    Interval:   Intervals to be displayed (a string)
'           Date1:      The lower date (see below)
'           Date2:      The higher date (see below)
'           ShowZero:   Boolean to select showing zero elements
'
'Outputs:   On error: Null
'           On no error: Variant containing the number of years,
'               months, days, hours, minutes & seconds between
'               the two dates, depending on the display interval
'               selected.
'           If Date1 is greater than Date2, the result will
'               be a negative value.
'           The function compensates for the lack of any intervals
'               not listed. For example, if Interval lists "m", but
'               not "y", the function adds the value of the year
'               component to the month component.
'           If ShowZero is True, and an output element is zero, it
'               is displayed. However, if ShowZero is False or
'               omitted, no zero-value elements are displayed.
'               For example, with ShowZero = False, Interval = "ym",
'               elements = 0 & 1 respectively, the output string
'               will be "1 month" - not "0 years 1 month".
' Modified 10/13/03 by Pat Hartman to also output separate date part fields


On Error GoTo Err_Diff2Dates


   Dim booCalcYears As Boolean
   Dim booCalcMonths As Boolean
   Dim booCalcDays As Boolean
   Dim booCalcHours As Boolean
   Dim booCalcMinutes As Boolean
   Dim booCalcSeconds As Boolean
   Dim booSwapped As Boolean
   Dim dtTemp As Date
   Dim intCounter As Integer
   Dim lngDiffYears As Long
   Dim lngDiffMonths As Long
   Dim lngDiffDays As Long
   Dim lngDiffHours As Long
   Dim lngDiffMinutes As Long
   Dim lngDiffSeconds As Long
   Dim varTemp As Variant


 '  Const INTERVALS As String = "dmyhns"
   Const INTERVALs2 As String = "dmyhns"


'Check that Interval contains only valid characters
   Interval = LCase$(Interval)
   For intCounter = 1 To Len(Interval)
      If InStr(1, INTERVALs2, Mid$(Interval, intCounter, 1)) = 0 Then
         Exit Function
      End If
   Next intCounter


'Check that valid dates have been entered
   If Not (IsDate(Date1)) Then Exit Function
   If Not (IsDate(Date2)) Then Exit Function


'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
   If Date1 > Date2 Then
      dtTemp = Date1
      Date1 = Date2
      Date2 = dtTemp
      booSwapped = True
   End If


   Diff2Dates = Null
   varTemp = Null


'What intervals are supplied
   booCalcYears = (InStr(1, Interval, "y") > 0)
   booCalcMonths = (InStr(1, Interval, "m") > 0)
   booCalcDays = (InStr(1, Interval, "d") > 0)
   booCalcHours = (InStr(1, Interval, "h") > 0)
   booCalcMinutes = (InStr(1, Interval, "n") > 0)
   booCalcSeconds = (InStr(1, Interval, "s") > 0)


'Get the cumulative differences
   If booCalcYears Then
      lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
              IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
      Date1 = DateAdd("yyyy", lngDiffYears, Date1)
   End If


   If booCalcMonths Then
      lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
              IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
      Date1 = DateAdd("m", lngDiffMonths, Date1)
   End If


   If booCalcDays Then
      lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
              IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
      Date1 = DateAdd("d", lngDiffDays, Date1)
   End If


   If booCalcHours Then
      lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
              IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
      Date1 = DateAdd("h", lngDiffHours, Date1)
   End If


   If booCalcMinutes Then
      lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
              IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
      Date1 = DateAdd("n", lngDiffMinutes, Date1)
   End If


   If booCalcSeconds Then
      lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
      Date1 = DateAdd("s", lngDiffSeconds, Date1)
   End If


   If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
      varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
   End If


   If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
      If booCalcMonths Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
      End If
   End If


   If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
      If booCalcDays Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
      End If
   End If


   If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
      If booCalcHours Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
      End If
   End If


   If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
      If booCalcMinutes Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
      End If
   End If


   If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
      If booCalcSeconds Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
      End If
   End If


   If booSwapped Then
      varTemp = "-" & varTemp
   End If


   Diff2Dates = Trim$(varTemp)
   iYears = lngDiffYears
   iMonths = lngDiffMonths
   iDays = lngDiffDays
   iHours = lngDiffHours
   iMinutes = lngDiffMinutes
   iSeconds = lngDiffSeconds


End_Diff2Dates:
   Exit Function


Err_Diff2Dates:
   Resume End_Diff2Dates


End Function

Then use:

Code:

=Diff2Dates("hn",[Resolution Date And Time],[Resolution TIme Frame])

**Bob Fitz** · 02-14-2017, 03:25 AM

Glad it worked for you.

It's strange i did try exactly the same code or i thought i did.

Can't really see any similarity. Yours uses DateDiff() function. Mine uses Diff2Date() function

**Bob Fitz** · 02-14-2017, 03:52 AM

I've just seen your last post.

The first piece of code that I posted is the code to a user defined function called Diff2Dates()
This must be copied and pasted to a NEW CODE MODULE

Then use the expression in the second code frame to show the result

**joym** · 02-14-2017, 05:35 PM

Originally Posted by Bob Fitz

I've just seen your last post.

The first piece of code that I posted is the code to a user defined function called Diff2Dates()
This must be copied and pasted to a NEW CODE MODULE

Then use the expression in the second code frame to show the result

thanks i have managed to make it work to calculate my Outage time. i tried inputting the same equation with and iif condition to calculate the duration SLA exceeded however i get an #error as a result

Code:

=IIf([Resolution Date And Time]>[Resolution TIme Frame],Diff2Dates("ymdhn”,[Resolution TIme Frame],[Resolution Date And Time]),0)

I get an error u entered a invalid string

**joym** · 02-14-2017, 06:15 PM

hi thanks i managed to solve the error i was getting for the iff function

**joym** · 02-14-2017, 09:40 PM

if i wanted to add a specific time frame to my Date And Time Fault Lodged Eg Date And Time Fault lodged is - 2/14/2017 3.22pm and i wanted to add 1 day 2 hours to this to show an outcome 2/15/2017 5.22pm how can u do this using the diff2date function.

I tried this

Code:

=Diff2Dates("dhn",1,2,0,[Date Fault Lodged])

the result i get is 1 Day

Why doesn't the hours not get calculated also i would like it to display it in the format "mm/dd/yyyy hh:mm:ss AM or PM

**Bob Fitz** · 02-15-2017, 03:29 AM

If you want to add or subtract time take a look at the DateAdd() function. See: https://www.techonthenet.com/access/...te/dateadd.php

DateDiff function

Thread Tools