Working out weekdays in a query - one date away from another but only weekdays.

[NickPlymouth]

Hi Guys, its been about 3 years since I last logged in, so hello and I hope you are all well. I am trying to work out how many days between two dates but only count weekdays (I also need to take out UK holidays but that can wait). I have looked on YT as you do and no matter what I am doing it still will not work, errors each time I run it. I can work out the exact days between two dates just by taking one away from another but not as weekdays only. Any assistance would be most welcome. Thank you. Sorry if you think this is beginners stuff, but I rarely do reports these days from queries as they are all set up. Regards, Nick.

 

[arnelgp]

you can create a function that count only the workdays:

public function fnCountWorkDays(byval dtStart As Date, dtEnd As Date) As Integer
' weekends are Saturday and Sunday
dim dt As Date, cnt As Integer
for dt = dtStart To dtEnd
    If Instr(1, "Sat/Sun", Format$(dt, "ddd")) = 0 then
        cnt = cnt + 1
    end if
next
fnCountWorkDays = cnt
end function

'example to count workdays in the month of january (the date is in EN-US format):
Dim workDays As Integer
workDays = fnCountWorkDays(#1/1/2022#, #1/31/2022#)

 

[NickPlymouth]

you can create a function that count only the workdays:

public function fnCountWorkDays(byval dtStart As Date, dtEnd As Date) As Integer
' weekends are Saturday and Sunday
dim dt As Date, cnt As Integer
for dt = dtStart To dtEnd
    If Instr(1, "Sat/Sun", Format$(dt, "ddd")) = 0 then
        cnt = cnt + 1
    end if
next
fnCountWorkDays = cnt
end function

'example to count workdays in the month of january (the date is in EN-US format):
Dim workDays As Integer
workDays = fnCountWorkDays(#1/1/2022#, #1/31/2022#)

OH MY GOODNESS, it worked first time. Thank you arnelgp, you are amazing. I learnt how to write queries and reports all on my own a long time ago, was never shown anything. I cannot thank you enough. Bravo.

 

[arnelgp]

you can also use this shortcut function:

Public Function fnCountWorkDays(ByVal dtStart As Date, dtEnd As Date) As Integer
' weekends are Saturday and Sunday
fnCountWorkDays = DateDiff("d", dtStart, dtEnd, vbSunday) + 1 - _
                  DateDiff("ww", dtStart, dtEnd, vbSaturday) - _
                  DateDiff("ww", dtStart, dtEnd, vbSunday)
End Function

 

[NickPlymouth]

you can also use this shortcut function:

Public Function fnCountWorkDays(ByVal dtStart As Date, dtEnd As Date) As Integer
' weekends are Saturday and Sunday
fnCountWorkDays = DateDiff("d", dtStart, dtEnd, vbSunday) + 1 - _
                  DateDiff("ww", dtStart, dtEnd, vbSaturday) - _
                  DateDiff("ww", dtStart, dtEnd, vbSunday)
End Function

Thank you so much, you must be a MS Access Guru. Best Wishes, Nick in Plymouth.

 

[Gasman]

Arnel

Hi Guys, its been about 3 years since I last logged in, so hello and I hope you are all well. I am trying to work out how many days between two dates but only count weekdays (I also need to take out UK holidays but that can wait). I have looked on YT as you do and no matter what I am doing it still will not work, errors each time I run it. I can work out the exact days between two dates just by taking one away from another but not as weekdays only. Any assistance would be most welcome. Thank you. Sorry if you think this is beginners stuff, but I rarely do reports these days from queries as they are all set up. Regards, Nick.

I think you might as well supply your holiday version now Arnel?

 

[Pat Hartman]

Here's one that has a bunch of useful date functions.

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

 

[Cotswold]

More date stuff.

I often fiddle about with string handling in all sorts of situations.
However, before I'm deluged with examples of all the alternatives, I just want to say that I do actually know there are several other ways of doing it. But I prefer this one. Just two lines and it's all there to see so clearly. Use it on a Form, or stick it in a Function and use it anywhere.

If you want to display today as 25th Jul 2022 then these two lines do it.

mVarDaySfx = Mid("stndrdthththththththththththththththththstndrdthththththththst", (((Day(Date) - 1 * 2) * 2) + 1), 2)
mVarDateToday = CStr(Day(Date)) + mVarDaySfx + " " + Mid("JanFebMarAprMayJunJulAugSepOctNovDec", (((Month(Date) - 1) * 3) + 1), 3) + " " + CStr(Year(Date))

 

[Gasman]

Where does mDaySfx come from?

 

[Cotswold]

Where does mDaySfx come from?

Carelessness but I'll call it a typo! The variables I used were maybe not so clear, so I altered them in the posting

 

[Gasman]

Carelessness but I'll call it a typo! The variables I used were maybe not so clear, so I altered them in the posting

I knew that.
It was really for the benefit of those people who just copy and paste, without any regard to what the code says or does.

Otherwise someone is gong to come back and say 'It does not work'