Trouble calculating day of month

[vba317]

I am trying to develop a procedure that will calculate the working days of the month and assign that number to a variable. Currently I am doing it manually I was hoping there is a way to calulate it so I don't have to do it every month. Any help is appreciated.

Tom

            dteDate = Date
            If dteDay = #3/29/2013# Then intDay = 1
            If dteDay = #4/1/2013# Then intDay = 2
            If dteDay = #4/2/2013# Then intDay = 3
            If dteDay = #4/3/2013# Then intDay = 4
            If dteDay = #4/4/2013# Then intDay = 5
            If dteDay = #4/5/2013# Then intDay = 6
            If dteDay = #4/8/2013# Then intDay = 7
            If dteDay = #4/9/2013# Then intDay = 8
            If dteDay > #4/9/2013# Then
            For intI = 9 To 30
            intDay = intDay = Weekday(Date, vbMonday)
            Next intI
            dtetime = #2:00:00 AM#
            'dtetime = #12:15:00 PM#
            'dtetime = #6:00:00 PM#
            'if
            If dtetime > #12:00:00 AM# Or dtetime <= #8:00:00 AM# Then intTime = 0.1
            If dtetime > #8:00:01 AM# Or dtetime <= #11:00:00 AM# Then intTime = 0.2
            If dtetime > #11:00:01 AM# Or dtetime <= #1:00:00 PM# Then intTime = 0.3
            If dtetime > #1:00:01 PM# Or dtetime <= #3:00:00 PM# Then intTime = 0.4
            If dtetime > #3:00:01 PM# Or dtetime <= #5:00:00 PM# Then intTime = 0.5
            If dtetime > #5:00:01 PM# Or dtetime <= #11:59:59 PM# Then intTime = 0.6
            intDayTotal = intDay + intTime
            strSQL = "INSERT INTO tblTime (ClientID,ClientName,Day,RptProcStart)" _
            & " VALUES (" & liClntID & ",'" & strUCI & "'," & intDayTotal & ",Now())"
            CurrentDb.Execute strSQL

My problem is my loop from 9-30 is not calculating the intDay properly.

 

[Andrzejek]

Could you specify what you mean by ‘working days of the month’?
Month can have 28, 29, 30, or 31 days.
Are you looking for number of ‘working days’, ie Monday – Friday in any given month?
Do you need to by-pass any holidays? If so, do you have those holidays listed somewhere in a table?



Have fun.

---- Andy

 

[vba317]

Yes, only the workingdays Mon through Friday during the month. I don't have a holiday table but I will create one.

Tom

 

[strongm]

Her's a slightly obscure version I wrote about 11 years ago for thread222-361893

[blue][green]' Assumes EndDate>= StartDate[/green]
Private Function CountWeekdaysBetweenDates(Startdate As Date, EndDate As Date) As Long
    If Weekday(Startdate, vbMonday) > 5 Then Startdate = DateAdd("d", 3 - Weekday(Startdate, vbSaturday), Startdate)
    If Weekday(EndDate, vbMonday) > 5 Then EndDate = DateAdd("d", -Weekday(EndDate, vbSaturday), EndDate)
    CountWeekdaysBetweenDates = DateDiff("d", Startdate, EndDate) - 2 * (DateDiff("ww", Startdate, EndDate))
End Function[/blue]

 

[vba317]

strongm,
Your code moves me in the right direction. The countWeekdaysBetweenDates does count the number of days. So in my example the startdate is 3/29/2013, the end date is 4/30/2013. The countWeekdaysBetweenDates is 22. Since my goal is to know that 3/29/2013 is the first day of the month and 4/1/2013 is the second day of the month, etc. Is there a formula I can use to assign 3/29 as 1?

Tom

 

[Andrzejek]

3/29/2013 is the first day of the month and 4/1/2013 is the second day of the month

What?

In my calendar 3/29/2013 is the 29th day of March, and 4/1/2013 is first day of April.

Have fun.

---- Andy

 

[strongm]

Ah, so your goal is NOT to calculate the working days of the month? I think perhaps you need to explain exactly whaty youy are trying to achieve. Then we can help you better.

 

[vba317]

I am trying to track client closings in the batches I do them. The first couple of clients close on the last working day of the month. So 3/29 is Day1. Day 2 is Mon April 1st. Day 3 is April 2nd. The last day of closings is the 9th working day that would be day 9. I track this data in a table. I also want to track when I ran the query. So I cut the day into 6 parts and assigned a number to the part 0.1 -0.6. So if I ran the process on day 1 before 8:00 am the intDayTotal would be 1.1. I was hoping to develop a routine that would automate my manual process of:

            If dteDay = #3/29/2013# Then intDay = 1
            If dteDay = #4/1/2013# Then intDay = 2
            If dteDay = #4/2/2013# Then intDay = 3
            If dteDay = #4/3/2013# Then intDay = 4
            If dteDay = #4/4/2013# Then intDay = 5
            If dteDay = #4/5/2013# Then intDay = 6
            If dteDay = #4/8/2013# Then intDay = 7
            If dteDay = #4/9/2013# Then intDay = 8
            If dteDay > #4/9/2013# Then
            For intI = 9 To 30
            intDay = intDay = Weekday(Date, vbMonday)
            Next intI

 

[strongm]

[blue]Public Sub Test()
    Dim intDay As Long
    intDay = CalcintDay("March 2013", "5 Apr 2013")
    Debug.Print intDay
End Sub

Private Function GetLastWorkingDayofMonth(Mydate As Date) As Date
    Mydate = DateAdd("d", -1, DateSerial(Year(Mydate), Month(Mydate) + 1, 1))
    If Weekday(Mydate, vbMonday) > 5 Then GetLastWorkingDayofMonth = DateAdd("d", -Weekday(Mydate, vbSaturday), Mydate)
End Function

' Assumes EndDate>= StartDate
Private Function CountWeekdaysBetweenDates(Startdate As Date, EndDate As Date) As Long
    If Weekday(Startdate, vbMonday) > 5 Then Startdate = DateAdd("d", 3 - Weekday(Startdate, vbSaturday), Startdate)
    If Weekday(EndDate, vbMonday) > 5 Then EndDate = DateAdd("d", -Weekday(EndDate, vbSaturday), EndDate)
    CountWeekdaysBetweenDates = DateDiff("d", Startdate, EndDate) - 2 * (DateDiff("ww", Startdate, EndDate))
End Function

Public Function CalcintDay(BatchMonth As Date, ActualDate As Date) As Long
    CalcintDay = CountWeekdaysBetweenDates(GetLastWorkingDayofMonth(BatchMonth), ActualDate) + 1 ' since start day is 1, not 0
End Function[/blue]

Note that I've not really introduced any new code, as the GetLastWorkingDayofMonth function merely reuses one line from the CountWeekdaysBetweenDates function.

 

[MichaelRed]

Somewhat dated? But there still exist a fq (181-261) which provided a soloution ...

MichaelRed