MS Access query to to find out date difference between two dates

Hi,

I am trying to find out date difference between to dates using MS Access query. I need to exclude weekends/holidays from the result.

Please let me know

Thank you
A
Asatoma SadgamayaAnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Your query will need to call a function like this:

https://www.experts-exchange.com/questions/26746702/calculate-future-date-excluding-weekends.html#a34613565

Do you know how to do that?

Jim.
aikimarkCommented:
Try this expression.
datediff("d", dtFrom, dtTo) - (2 * datediff("ww", dtFrom, dtTo))

Open in new window

Asatoma SadgamayaAnalystAuthor Commented:
Thank you for your inputs.

Jim, Could you please explain bit more about how to use that script on the link on my query(how to call a vba script from access query). I really appreciate it.

Aikimark, I will use your code if the vba scrip Jim is talking about does not work. Because I need to exclude holiday dates along with Saturdays and Sundays from the date difference calculation

thank you so much
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The code I pointed you to would go in a standard module.

 You will also need to add a holiday table with the holiday dates and set the following correctly in the procedure:

  Const cbytWorkdaysOfWeek  As Byte = 5
  ' Name of table with holidays.
  Const cstrTableHoliday    As String = "tblHoliday"
  ' Name of date field in holiday table.
  Const cstrFieldHoliday    As String = "HolidayDate"


 Then in the query, you would define a column like this:

NumberOfDays:ISO_WorkdayDiff( [Date1], [Date2], True)

  Changing "Date1" and "Date2" to your actual field names.

Jim.
Asatoma SadgamayaAnalystAuthor Commented:
Jim,

I have got plenty of date columns. Can I use this script  to compare 2 different columns in same query.

For Eg. Column A, ColumnB.......ColumnZ

First I need to find the date difference between Column A and ColumnB
Secondly I need to find the date difference between Column B and ColumnC
and so on

do you think I can do this in one Query?
Thank you
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
do you think I can do this in one Query?

Certainly.   You can call the procedure as many times as you need:

NumberOfDays1:ISO_WorkdayDiff( [ColumnA], [ColumnB], True)
NumberOfDays2:ISO_WorkdayDiff( [ColumnB], [ColumnC], True)

and so on.

Jim.
Asatoma SadgamayaAnalystAuthor Commented:
Hi Jim, it is working but couple of issues below

1. Not showing negative number of days (I need to see if the date difference is negative value)
2. if any input dates are null then result is #Error. (If any of the input dates are null, give me null as result)

Apart from this, it is working fine.

Please let me know

Thank you
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Note this section in the code:

  ' Reverse dates if these have been input reversed.
  If datDateFrom > datDateTo Then
    datDateTemp = datDateFrom
    datDateFrom = datDateTo
    datDateTo = datDateTemp
  End If

I believe what your looking to do is comment that out, although I don't know what will happen after that.   You'll have to try it.  I would assume though that code is there so the from is always less than the to and the procedure counts on that.

You'll have to modify the procedure to check for that and return a Null if that's what you want.  i.e.

If Not IsDate(datDateFrom) or Not IsDate(datDateTo) then
    ISO_WorkdayDiff = Null
   Exit Function
End If

 and change the definition from long to variant:

Public Function ISO_WorkdayDiff( _
  ByVal datDateFrom As Date, _
  ByVal datDateTo As Date, _
  Optional ByVal booExcludeHolidays As Boolean) _
  As Variant

Jim.
Asatoma SadgamayaAnalystAuthor Commented:
Hi Jim,

Thanks for your reply.

1. I tried with it but giving wrong values

' Reverse dates if these have been input reversed.
  If datDateFrom > datDateTo Then
    datDateTemp = datDateFrom
    datDateFrom = datDateTo
    datDateTo = datDateTemp
  End If

2. May I know where I need input below bit of code in the function

If Not IsDate(datDateFrom) or Not IsDate(datDateTo) then
    ISO_WorkdayDiff = Null
   Exit Function
End If

3. I changed the definition successfully. :)

4. Does this VBA script consider Saturdays and Sundays as weekends and so exclude these days from the date difference calculation?

Thank you
A
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<1. I tried with it but giving wrong values>>

 Have you worked with code much or no (I am guessing not)?   I don't have the time today to walk through the code and figure out what changes might be needed.

It's not my code and like many date difference routines, I am sure it is written to always expect the From to be less than the To date.

 What is it that your trying to accomplish that you expect negative days?

<<2. May I know where I need input below bit of code in the function>>

 Right at the very start. Right before the date swap code would be a good spot.

<<4. Does this VBA script consider Saturdays and Sundays as weekends and so exclude these days from the date difference calculation?>>

 In the code:

  Const cbytWorkdaysOfWeek  As Byte = 5

  So yes, it does.

Jim.
Asatoma SadgamayaAnalystAuthor Commented:
Hi Jim,

I sorted the negative issue using below formulae.

NumberOfDays1: IIf([Date2]<[Date1],0-ISO_WorkdayDiff([Date1],[Date2],True),ISO_WorkdayDiff([Date1],[Date2],True))

2. #Error is still showing even after amending the script

Public Function ISO_WorkdayDiff( _
  ByVal datDateFrom As Date, _
  ByVal datDateTo As Date, _
  Optional ByVal booExcludeHolidays As Boolean) _
  As Variant

' Purpose: Calculate number of working days between dates datDateFrom and datDateTo.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' May be freely used and distributed.

' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen
' 2000-10-03. Constants added.
'             Option for 5 or 6 working days per week added.
' 2008-06-12. Option to exclude holidays from the count of workdays.

  Const cbytWorkdaysOfWeek  As Byte = 5
  ' Name of table with holidays.
  Const cstrTableHoliday    As String = "tblHoliday"
  ' Name of date field in holiday table.
  Const cstrFieldHoliday    As String = "HolidayDate"

  Dim bytSunday             As Byte
  Dim intWeekdayDateFrom    As Integer
  Dim intWeekdayDateTo      As Integer
  Dim lngDays               As Long
  Dim datDateTemp           As Date
  Dim strDateFrom           As String
  Dim strDateTo             As String
  Dim lngHolidays           As Long
  Dim strFilter             As String
 
  If Not IsDate(datDateFrom) Or Not IsDate(datDateTo) Then
    ISO_WorkdayDiff = Null
   Exit Function
End If
 
  ' Reverse dates if these have been input reversed.
  If datDateFrom > datDateTo Then
    datDateTemp = datDateFrom
    datDateFrom = datDateTo
    datDateTo = datDateTemp
  End If
 
  ' Find ISO weekday for Sunday.
  bytSunday = Weekday(vbSunday, vbMonday)
 
  ' Find weekdays for the dates.
  intWeekdayDateFrom = Weekday(datDateFrom, vbMonday)
  intWeekdayDateTo = Weekday(datDateTo, vbMonday)
 
  ' Compensate weekdays' value for non-working days (weekends).
  intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
  intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)
 
  ' Calculate number of working days between the two weekdays, ignoring number of weeks.
  lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
  ' Add number of working days between the weeks of the two dates.
  lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, datDateTo, vbMonday, vbFirstFourDays))
 
  If booExcludeHolidays And lngDays > 0 Then
    strDateFrom = Format(datDateFrom, "yyyy\/mm\/dd")
    strDateTo = Format(datDateTo, "yyyy\/mm\/dd")
    strFilter = cstrFieldHoliday & " Between #" & strDateFrom & "# And #" & strDateTo & "# And Weekday(" & cstrFieldHoliday & ", 2) <= " & cbytWorkdaysOfWeek & ""
    lngHolidays = DCount("*", cstrTableHoliday, strFilter)
  End If
 
  ISO_WorkdayDiff = lngDays - lngHolidays

End Function

Thank you
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sorry, couple more changes I should have thought of.

Use the code below.  This also includes logic to indicate negative dates, so you can remove your IIF() in the query.   Just call the function.

 If From > to, then it will come back with a negative.

Jim.

Public Function ISO_WorkdayDiff( _
       ByVal varDateFrom As Variant, _
       ByVal varDateTo As Variant, _
       Optional ByVal booExcludeHolidays As Boolean) _
       As Variant

    ' Purpose: Calculate number of working days between dates datDateFrom and datDateTo.
    ' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
    ' May be freely used and distributed.

    ' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen
    ' 2000-10-03. Constants added.
    '             Option for 5 or 6 working days per week added.
    ' 2008-06-12. Option to exclude holidays from the count of workdays.
    ' 2019-02-07. Modified for use from query.

    Const cbytWorkdaysOfWeek As Byte = 5
    
    ' Name of table with holidays.
    Const cstrTableHoliday As String = "tblHoliday"
    
    ' Name of date field in holiday table.
    Const cstrFieldHoliday As String = "HolidayDate"

    Dim datDateFrom As Date
    Dim datDateTo As Date
    Dim bytSunday As Byte
    Dim intWeekdayDateFrom As Integer
    Dim intWeekdayDateTo As Integer
    Dim lngDays As Long
    Dim datDateTemp As Date
    Dim strDateFrom As String
    Dim strDateTo As String
    Dim lngHolidays As Long
    Dim strFilter As String
    Dim bolDatesReversed As Boolean

    If IsDate(varDateFrom) And IsDate(varDateTo) Then
        datDateFrom = CDate(varDateFrom)
        datDateTo = CDate(varDateTo)

        ' Reverse dates if these have been input reversed.
        If datDateFrom > datDateTo Then
            datDateTemp = datDateFrom
            datDateFrom = datDateTo
            datDateTo = datDateTemp
            bolDatesReversed = True
        Else
            bolDatesReversed = False
        End If

        ' Find ISO weekday for Sunday.
        bytSunday = Weekday(vbSunday, vbMonday)

        ' Find weekdays for the dates.
        intWeekdayDateFrom = Weekday(datDateFrom, vbMonday)
        intWeekdayDateTo = Weekday(datDateTo, vbMonday)

        ' Compensate weekdays' value for non-working days (weekends).
        intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
        intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)

        ' Calculate number of working days between the two weekdays, ignoring number of weeks.
        lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
        ' Add number of working days between the weeks of the two dates.
        lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, datDateTo, vbMonday, vbFirstFourDays))

        If booExcludeHolidays And lngDays > 0 Then
            strDateFrom = Format(datDateFrom, "yyyy\/mm\/dd")
            strDateTo = Format(datDateTo, "yyyy\/mm\/dd")
            strFilter = cstrFieldHoliday & " Between #" & strDateFrom & "# And #" & strDateTo & "# And Weekday(" & cstrFieldHoliday & ", 2) <= " & cbytWorkdaysOfWeek & ""
            lngHolidays = DCount("*", cstrTableHoliday, strFilter)
        End If

        If bolDatesReversed = True Then
            ISO_WorkdayDiff = -(lngDays - lngHolidays)
        Else
            ISO_WorkdayDiff = lngDays - lngHolidays
        End If

    Else
        ISO_WorkdayDiff = Null
    End If

End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Asatoma SadgamayaAnalystAuthor Commented:
That was awesome Jim, That did work for me.

One issue I saw is that data display in character format. So I applied below formula to convert it to Number data type, for my further calculations.

NumberOfDays1: IIf(IsNumeric(ISO_WorkdayDiff([Date2],[Date1],True)),Val(ISO_WorkdayDiff([Date2],[Date1],True)),Null)

Apart from that all worked perfectly fine. No #Error in the column, I can see negative values where ever it supposed to.

Many thanks for your time and patience.

All the best
A
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Change this:

       If bolDatesReversed = True Then
            ISO_WorkdayDiff = -(lngDays - lngHolidays)
        Else
            ISO_WorkdayDiff = lngDays - lngHolidays
        End If

to:

       If bolDatesReversed = True Then
            ISO_WorkdayDiff = Clng(-(lngDays - lngHolidays))
        Else
            ISO_WorkdayDiff = Clng(lngDays - lngHolidays)
        End If

You really don't want IIF()'s in a query if you can avoid them.

Jim.
Asatoma SadgamayaAnalystAuthor Commented:
Hi Jim, that did not make any difference I am afraid.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
What is the issue?   It should be returning a numeric for sure at this point.

Jim.
Asatoma SadgamayaAnalystAuthor Commented:
Hi Jim, I have done the changes as you said, but still shows numeric values as text.

Thank you
Gustav BrockCIOCommented:
The function will either return the number of days, or - if either Date1 or Date2 not are valid date values or expressions - Null.
Thus, you could simply use:

NumberOfDays1: ISO_WorkdayDiff([Date2],[Date1],True)

Open in new window

Asatoma SadgamayaAnalystAuthor Commented:
Hi Gustav,

Please find the file attached.

NumberOfDays2: ISO_WorkdayDiff([Date2],[Date1],True)  ---> I get date difference in character format.

NumberOfDays1: IIf(IsNumeric(ISO_WorkdayDiff([Date2],[Date1],True)),Val(ISO_WorkdayDiff([Date2],[Date1],True)),Null)  ---> I get date difference in number format

It means, I need to apply this iif(IsNumeric statement through out where ever I need to find date difference.

Thank you
A
NumberOfDays.jpg
Gustav BrockCIOCommented:
I see.
You could reduce it a bit:

NumberOfDays1: IIf([Date1]+[Date2] Is Null;Null;Val(ISO_WorkdayDiff([Date2],[Date1],True))

Open in new window

If you only have valid dates for both parameters, Val alone will do it:

NumberOfDays1: Val(ISO_WorkdayDiff([Date2],[Date1],True)

Open in new window

If it is only display, just set the Format property to: 0
Asatoma SadgamayaAnalystAuthor Commented:
Thanks Gustav, Why I need this in number datatype is because I need to use these fields (NumberOfDays1, NumberOfDays2, NumberOfDays3... which I am going to create) for further calculations.

Is it possible, so that, when I call the function using NumberOfDays1: ISO_WorkdayDiff([Date2],[Date1],True), it gives number format values? Else, it is going to be complex calculations with iif statements all the fields

Or else I need to do the calculations on the next level query

Thank you
Gustav BrockCIOCommented:
OK, that makes sense.
Asatoma SadgamayaAnalystAuthor Commented:
Thanks everyone

Best
A
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.