Calculate between two dates

[icsupt]

I want to run a report that will calculate between two dates. I want to only report if [DateStarted] is >=6 days from [DateSubmit].

The two fields are [DateSubmit] and [DateStarted].

How would I do this? In a query?

I will need step by step instructions.

Also, if I can get it to calculate in business days, that would be bonus. But I will need two examples - one that calculate in business days and one that calculates all days.

Thanks in advance.

 

[BobStubbs]

Base your report on a query. In the query, include the fields you want to list on the report. Also create a calculated field, using the formula:

[DateStarted] - [DateSubmit]

Run this query - you should see numbers in the calculated column, representing the number of days between the two dates.

Now add the formula >=6 to the Criteria line of this column, and run the query again. You should see only those records where the dates differ by six days or more.

To get the number of business days between the two dates, you could use this function which I wrote for one of my programs:

Function BusinessDays(dtStartDate As Date, dtEndDate As Date)
'Returns number of business days between two dates as an integer
'Returns -1 if the start date is greater than the end date

Dim intDayCount As Integer
Dim J As Integer
Dim dtNextDate As Date

'----------------------------------------------------------------
'- Set the first date to check                                  -
'----------------------------------------------------------------
dtNextDate = dtStartDate

'----------------------------------------------------------------
'- Return error code of -1 if Start Date is later than End Date -
'----------------------------------------------------------------
If dtStartDate > dtEndDate Then
    BusinessDays = -1
Else
    
'----------------------------------------------------------------
'- Loop through all dates between Start Date and End Date       -
'----------------------------------------------------------------
    Do Until dtNextDate >= dtEndDate
        
'----------------------------------------------------------------
'If it's a weekday (Monday to Friday) add 1 to the day counter
'----------------------------------------------------------------
        If WeekDay(dtNextDate, vbMonday) <= 5 Then
            intDayCount = intDayCount + 1
        End If
        
        dtNextDate = dtNextDate + 1
    Loop
    

'------------------------------------------------------------------
'- Return number of business days between the start and end dates -
'------------------------------------------------------------------
    BusinessDays = intDayCount
    
End If

End Function

Copy this code into a module, and try it manually via the Debug window. Example (NB I am using European date format dd/mm/yyyy here) - type:

? BusinessDays("01/10/2004", "10/10/2004")

The function should return '6' - there are six business days between 1 October and 10 October 2004 - including 1 October itself.

Use this function in your calculated query column:

BusinessDays([DateSubmit],[DateStarted])

This should give you the result which you need.



Bob Stubbs

 

[icsupt]

Thank you very much. I'm going to try this out.

 

[icsupt]

I used your first example and it worked fine. Thanks!

 

[icsupt]

I used your code and believe we are on the right path.

1. Can I allow for holidays (I have a tblholiday that has Holidate and Holiday)?

2. If no DateStart and today is >=6 days from the DateSubmit, then this should be on the report. I.e. if project submitted 10 business/holiday days ago, then this should appear on report. Also, when no DateStart, it is appearing as #Error.

3. If the difference between DateSubmit and DateStart is >=6, then I want to show on report. I.e. [DateStarted] is >=6 days from [DateSubmit].

Thanks again.

 

[BobStubbs]

Thinking about each of your points ...

1. You can check each date against your holiday table, by changing part of my sample code:

'-----------------------------------------------------
'- If it's a weekday (Monday to Friday), and not a   -
'- holiday, add 1 to the day counter                 -
'-----------------------------------------------------
        If WeekDay(dtNextDate, vbMonday) <= 5 Then
            If IsHoliday(dtNextDate) then
                intDayCount = intDayCount + 1
            End If        
        End If
        
        dtNextDate = dtNextDate + 1
    Loop

i.e. insert a call to your 'is it a holiday' function, so you only increment the day counter if the day is a weekday, and not in the holiday list.


2. In the query, add another calculated column, showing:

BusinessDays([DateSubmit],Date())

Now add a second selection criterion line, showing:

'Is Null' for the [DateStart] field, and >=6 to the new calculated field. Then records will be included if [DateStart] is blank, and [DateSubmit] is six or more working days ago.


3. Again in the query, add a third calculated column to show the result of [DateSubmit] - [DateStart], and a third selection line applying >=6 to this column.

I hope this all makes sense!


Bob Stubbs