Weekday Series

[ClaraBarton]

I need 728 successive weekdays starting with a date.

Between #5/1/2024# And DateAdd("w",727,#5/1/2024#)

This makes the correct number of days but it includes weekends. Doesn't the "w" limit it?
So I limit the days to

<>7 and <>1

This limits the dates to weekdays but brings the total to 520.
What formula would give me 727 week days?

 

[plog]

That is weird. "w" is equivalent to "d" as far as I can tell when it comes to DateAdd.

What formula would give me 727 week days?

You could use TOP in your SELECT clause to limit it to 727. This will require an ORDER BY and also to remove the upper limit on your WHERE:

SELECT TOP 727 DateField
FROM YourTableNameHere
WHERE DateField >= #5/1/2024# AND WeekDay(DateField)<>7 AND WeekDay(DateField)<>1
ORDER BY DateField

 

[Gasman]

Perhaps someone should tell Microsoft?

DateAdd Function - Microsoft Support

support.microsoft.com

 

[sonic8]

That is weird. "w" is equivalent to "d" as far as I can tell when it comes to DateAdd.

Not weird at all. Every day is a weekday. - At least, I'm not aware of any day that is not part of the week.
You confuse weekday and workday, which Microsoft explicitly addresses in the documentation of the VBA DateAdd function:

Note
When you use the "w" interval (which includes all the days of the week, Sunday through Saturday) to add days to a date, the DateAdd function adds the total number of days that you specified to the date, instead of adding just the number of workdays (Monday through Friday) to the date, as you might expect.

 

[Gasman]

Missed this

Note

When you use the "w" interval (which includes all the days of the week, Sunday through Saturday) to add days to a date, the DateAdd function adds the total number of days that you specified to the date, instead of adding just the number of workdays (Monday through Friday) to the date, as you might expect.

 

[arnelgp]

here is another demo to get 728 weekdays (no sat/sun).
see the query.

 

[ClaraBarton]

Well! I missed that too. I'm using your query arnelgp! Exactly what I need. Thank you very much.

 

[plog]

That is weird. "w" is equivalent to "d" as far as I can tell when it comes to DateAdd.

Not weird at all. Every day is a weekday.

Yes weird. Your semantic argument doesn't counter my logical point.

In a built in function with a custom argument list, there's nothing to be achieved with synonyms (w = d) but confusion.

 

[sonic8]

In a built in function with a custom argument list, there's nothing to be achieved with synonyms (w = d) but confusion.

You got a valid point there.
I guess, the intention was to keep the options for the Interval argument the same for all date functions using this argument. "d" and "w" have different effects for some date functions, e.g. DatePart, but not for others. The documentation should be more clear on this, but I currently don't feel inclined to create and submit a pull request to this effect.

 

[Pat Hartman]

Perhaps someone should tell Microsoft?

I reported it using the option on the help entry and I also reported it to the MVP group. I'm pretty sure that no code correction will happen so the best we can hope for is a clarification to the documentation.

P

Thread 'Sample Date Functions'

Dec 18, 2012

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:

1. Business days between two dates.
2. Business day + days = next business date
3. Easter (Western)
4. Age
5. First Day of Quarter
6. First Day of Week
7. First Day of Month
8. First Day of Previous Month
9. First Day of Next Month
10. Last Day of Quarter
11. Last Day of Week
12. Last Day of Month
13. Last Day of Previous Month
14. Last Day of Next Month
15. First Weekday (sun, mon, etc) following...

• Pat Hartman
• Replies: 4
• Forum: Sample Databases

• 

 

[Gasman]

I reported it using the option on the help entry and I also reported it to the MVP group. I'm pretty sure that no code correction will happen so the best we can hope for is a clarification to the documentation.

P

Thread 'Sample Date Functions'

Dec 18, 2012

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:

1. Business days between two dates.
2. Business day + days = next business date
3. Easter (Western)
4. Age
5. First Day of Quarter
6. First Day of Week
7. First Day of Month
8. First Day of Previous Month
9. First Day of Next Month
10. Last Day of Quarter
11. Last Day of Week
12. Last Day of Month
13. Last Day of Previous Month
14. Last Day of Next Month
15. First Weekday (sun, mon, etc) following...

• Pat Hartman
• Replies: 4
• Forum: Sample Databases

• 

No need @Pat Hartman, as I missed the entry in post #10.