Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trouble calculating day of month

Status
Not open for further replies.

vba317

Programmer
Mar 5, 2009
708
US
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

Code:
            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.
 
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
 
Yes, only the workingdays Mon through Friday during the month. I don't have a holiday table but I will create one.

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

Code:
[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]
 
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
 
vba317 said:
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
 
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.
 
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:

Code:
            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
 
Code:
[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.
 
Somewhat dated? But there still exist a fq (181-261) which provided a soloution ...

MichaelRed


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top