Solved Numbering 'business' or 'work' days for a particular month

[alan2013]

Hi.
I've already been searching around for a way of doing the following, but I haven't yet been able to find it.

I want to have a 'continuous' form showing various fields, including a dtDeadlineDate.

Alongside the dtDeadlineDate field, I'll have a unbBusinessDay to capture the business day number for that particular month.

For example : If a deadline date falls on Tues 8th Nov 22, I want unbBusinessDay to be set to 6 (etc, etc).

Can anyone help me find a way of doing this ?
Thanks in advance for any help.

 

[arnelgp]

what is "unbBusinessDay"?
what does "unb" means?

 

[CJ_London]

what does your data look like?

 

[alan2013]

what is "unbBusinessDay"?
what does "unb" means?

unbound ie. it's an unbound control

 

[alan2013]

what does your data look like?

The dtDeadlineDate field is in Medium Date format. Is that what you mean ?

 

[arnelgp]

If a deadline date falls on Tues 8th Nov 22, I want unbBusinessDay to be set to 6 (etc, etc).

do you mean your "calendar" schedule will start on the 6 (first sunday where the deadline falls)?

 

[alan2013]

do you mean your "calendar" schedule will start on the 6 (first sunday where the deadline falls)?

In my example :
dtDeadlineDate is 08-Nov-22
unbBusinessDay is to be 6

 

[CJ_London]

The dtDeadlineDate field is in Medium Date format. Is that what you mean ?

No, but since we now know what unbBusinessDay is

are there multiple lines of data? i.e can there be more than one dtDeadlineDate on a line?
Is this control in the header or on the individual lines?
Do you need to take account of bank holidays or other closed dates?

 

[alan2013]

No, but since we now know what unbBusinessDay is

are there multiple lines of data? i.e can there be more than one dtDeadlineDate on a line?
Is this control in the header or on the individual lines?
Do you need to take account of bank holidays or other closed dates?

Multiple lines of data : Yes
More than one dtDeadlineDate on a line : No, just one per line
Location of dtDeadlineDate & unbBusinessDay : in the 'Detail' section, in individual lines
Bank holidays, etc : Don't need to take account of those.
The 'working week' is Mon - Fri

 

[Minty]

If you need to do a lot of date calculations that involve non-standard periods or day counts create a calendar table.
Populate it with all the calculations per day that you need.

I have a couple of clients who work with British Tax periods and Tax Financial Years e.g. 6-April 2022 to 5-April 2023
I often need to know which financial week or month it is within these bounds and I simply have a table that has all the calculations already made.

This is very specific to a particular weekly based planning tool where weeks start on a Monday but something like

FullDate

Period

ISOWeek

WorkingDay

DateYear

DateMonth

DaysInMonth

DayNo

WeekdayName

WeekdayNo

WeekOfMonth

WorkWeekCommences

WorkWeekEnding

WorkDaysInWorkWeek

29/09/2022​

202209​

39​

Y

2022​

9​

30​

29​

Thursday

5​

5​

26/09/2022​

30/09/2022​

5​

30/09/2022​

202209​

39​

Y

2022​

9​

30​

30​

Friday

6​

5​

26/09/2022​

30/09/2022​

5​

01/10/2022​

202210​

39​

N

2022​

10​

31​

1​

Saturday

7​

1​

0​

02/10/2022​

202210​

39​

N

2022​

10​

31​

2​

Sunday

1​

1​

0​

03/10/2022​

202210​

40​

Y

2022​

10​

31​

3​

Monday

2​

2​

03/10/2022​

07/10/2022​

5​

04/10/2022​

202210​

40​

Y

2022​

10​

31​

4​

Tuesday

3​

2​

03/10/2022​

07/10/2022​

5​

05/10/2022​

202210​

40​

Y

2022​

10​

31​

5​

Wednesday

4​

2​

03/10/2022​

07/10/2022​

5​

06/10/2022​

202210​

40​

Y

2022​

10​

31​

6​

Thursday

5​

2​

03/10/2022​

07/10/2022​

5​

07/10/2022​

202210​

40​

Y

2022​

10​

31​

7​

Friday

6​

2​

03/10/2022​

07/10/2022​

5​

08/10/2022​

202210​

40​

N

2022​

10​

31​

8​

Saturday

7​

2​

03/10/2022​

07/10/2022​

5​

09/10/2022​

202210​

40​

N

2022​

10​

31​

9​

Sunday

1​

2​

03/10/2022​

07/10/2022​

5​

10/10/2022​

202210​

41​

Y

2022​

10​

31​

10​

Monday

2​

3​

10/10/2022​

14/10/2022​

5​

11/10/2022​

202210​

41​

Y

2022​

10​

31​

11​

Tuesday

3​

3​

10/10/2022​

14/10/2022​

5​

12/10/2022​

202210​

41​

Y

2022​

10​

31​

12​

Wednesday

4​

3​

10/10/2022​

14/10/2022​

5​

13/10/2022​

202210​

41​

Y

2022​

10​

31​

13​

Thursday

5​

3​

10/10/2022​

14/10/2022​

5​

14/10/2022​

202210​

41​

Y

2022​

10​

31​

14​

Friday

6​

3​

10/10/2022​

14/10/2022​

5​

15/10/2022​

202210​

41​

N

2022​

10​

31​

15​

Saturday

7​

3​

10/10/2022​

14/10/2022​

5​

16/10/2022​

202210​

41​

N

2022​

10​

31​

16​

Sunday

1​

3​

10/10/2022​

14/10/2022​

5​

17/10/2022​

202210​

42​

Y

2022​

10​

31​

17​

Monday

2​

4​

17/10/2022​

21/10/2022​

5​

18/10/2022​

202210​

42​

Y

2022​

10​

31​

18​

Tuesday

3​

4​

17/10/2022​

21/10/2022​

5​

19/10/2022​

202210​

42​

Y

2022​

10​

31​

19​

Wednesday

4​

4​

17/10/2022​

21/10/2022​

5​

 

[arnelgp]

you can create a Function in a Module:

Public Function CountWorkDays(ByVal endDate As Variant) As Integer
Dim startDate As Date
Dim i As Date, j As Integer
If IsNull(endDate) Then
    Exit Function
End If
startDate = DateSerial(Year(endDate), Month(endDate), 1)
For i = startDate To endDate
    If InStr(1, "/Sat/Sun/", Format$(i, "/ddd/")) <> 0 Then
    Else
        j = j + 1
    End If
Next
CountWorkDays = j
End Function

now the ControlSource of unbBusinessDay textbox:

=CountWorkDays(dtDeadlineDate)


The result of the function where dtDeadlineDate = Nov 8, 2022:
Result: 6

 

[alan2013]

arnelgp, I've introduced your Function, and it works well. Thank you.

minty, I can see how your method would work for this scenario....and many others. Thank you too.

Thanks also to CJ_London.