DateDiff Query without weekends (1 Viewer)

[Gismo]

Hi all,

Please could you assist?

I have to calculate the difference between 2 dates excluding weekends but not sure how

Days Offset: DateDiff("w",[CS Orders with Spares - Report]![Reqmt Date],Date())

 

[Gasman]

Create your own function to take into account the weekend days.

 

[Gismo]

Create your own function to take into account the weekend days.

I have no idea how to do this though

 

[bob fitz]

Perhaps you could use a query to calculate the count of weekdays. Something like:

SELECT Count(YourTableName.FieldNameToCount) AS NumOfWDays
FROM YourTableName
WHERE (((YourTableName.YourDateFieldName) Between [Start Date] And Date()) AND ((Weekday([YourDateFieldName]))<>1 And (Weekday([YourDateFieldName]))<>7));

You would need to replace the bold names with appropriate names from your db.

 

[Minty]

You can use the Weekday() function to tell what day of the week it is.
It's therefore possible to write a query that only adds up the days....

Bob beat me to it.

 

[Gismo]

Perhaps you could use a query to calculate the count of weekdays. Something like:

SELECT Count(YourTableName.FieldNameToCount) AS NumOfWDays
FROM YourTableName
WHERE (((YourTableName.YourDateFieldName) Between [Start Date] And Date()) AND ((Weekday([YourDateFieldName]))<>1 And (Weekday([YourDateFieldName]))<>7));

You would need to replace the bold names with appropriate names from your db.

Doesnt seem to work on my side, might be missing something

SELECT Count([CS Orders with Spares - Report]![Reqmt Date]) AS NumOfWDays, Date() AS [Today Date], [CS Orders with Spares - Report].[Reqmt Date], [CS Orders with Spares - Report].Material
FROM [CS Orders with Spares - Report]
GROUP BY Date(), [CS Orders with Spares - Report].[Reqmt Date], [CS Orders with Spares - Report].Material
HAVING ((((([CS Orders with Spares - Report]![Reqmt Date]) Between [Reqmt Date] And Date()) And ((Weekday([Reqmt Date]))<>1 And (Weekday([Reqmt Date]))<>7))));

 

[bob fitz]

Can you post a copy o the db

 

[Gasman]

Bob had a where, you have having?

 

[Gismo]

Bob had a where, you have having?

SELECT Count([CS Orders with Spares - Report]![Reqmt Date]) AS NumOfWDays, Date() AS [Today Date], [CS Orders with Spares - Report].[Reqmt Date], [CS Orders with Spares - Report].Material
FROM [CS Orders with Spares - Report]
WHERE ((([CS Orders with Spares - Report]![Reqmt Date]) Between [Reqmt Date] And Date()) AND ((Weekday([Reqmt Date]))<>1 And (Weekday([Reqmt Date]))<>7))
GROUP BY Date(), [CS Orders with Spares - Report].[Reqmt Date], [CS Orders with Spares - Report].Material;

 

[bob fitz]

I had no GroupBy clause either

 

[Gismo]

I had no GroupBy clause either

That is my material number and Date field

 

[bob fitz]

So, I'll ask again. Can you post a copy of the db.

 

[Gismo]

So, I'll ask again. Can you post a copy of the db.

Let me try
Need to do some exports

Please stand by

 

[Gismo]

Let me try
Need to do some exports

Please stand by

So what I need is to know what is the difference in days from date() to Requirement Date
I will need a negative days to offset to the amount of days to have the item issued the same day

 

[Gasman]

Here is my function idea.
You need to make it work for your negative numbers and test it further.

Function DaysNIW(dtStart As Date, dtEnd As Date) As Long
Dim dtDate As Date
Dim lngDays As Long

dtDate = dtStart
Do While dtDate <= dtEnd
    If Weekday(dtDate) <> 1 And Weekday(dtDate) <> 7 Then
        lngDays = lngDays + 1
    End If
    dtDate = dtDate + 1
Loop
DaysNIW = lngDays
End Function

SELECT DaysNIW([Reqmt Date],Date()) AS Expr1, [CS Orders with Spares - Report].[Reqmt Date]
FROM [CS Orders with Spares - Report]
GROUP BY [CS Orders with Spares - Report].Material, [CS Orders with Spares - Report].[Reqmt Date];

 

[bob fitz]

With a better understanding of the requirement I also tried the function way, as suggested by Gasman.
My version of it is attached.

 

[Gismo]

With a better understanding of the requirement I also tried the function way, as suggested by Gasman.
My version of it is attached.

Thank you so much for the help
It looks good so far
I will test it in the next few days

 

[Pat Hartman]

I've posted this link dozens of times. See if it helps you. Generally, you need a holiday table also to calculate work days. This sample has one.

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

 

[ebs17]

Such calculations as desired should often be carried out using the contents of tables. This is where query solutions come in handy. Queries are very good at dealing with tables. That's why I quickly have a planned calendar table that can then also be used well in other uses.
The calendar table here has a calDay (Date, PK) field and continuously contains the days of a sufficient period. Other fields contain calculated and saved formats for this date, so calWeekdayNumber is what the name says (starts with 1 for Monday). These format fields are also indexed.

SELECT
   R.Material,
   COUNT(*) AS Offset
FROM
   [CS Orders with Spares - Report] AS R
      INNER JOIN
         (
            SELECT
               calDay
            FROM
               tblCalendar
            WHERE
               calWeekdayNumber < 6
         ) AS C
         ON C.calDay BETWEEN Date()
            AND
         R.[Reqmt Date]
GROUP BY
   R.Material

This approach is easily scalable. It could be that you also want to take public holidays and company holidays into account. Corresponding tables could be integrated into the query and the performance will remain convincing.