Report needs monthly number based on date

[tcfd1166]

I have a report that shows our company "tour" number. A tour starts every 4 weeks throughout the year, so there will be 13 tours in a year. This number is not month based. I currently use the following formula.
=-Int(Format(Date(),"ww")/-4) in a text box that is based off of current date. My issue shows every 1st week and 4th week of that particular tour. The 4th week, I have to add a "-1" outside of the () to make the tour number stay correct. Then the following week begins the next "tour" week and I have to remove the "-1". Is there any other formula that I can use in the text box that I don't have to adjust every 1st and 4th week?

 

[Micron]

Perhaps you need to incorporate week function on some other day than Sunday (default)? Some sample data that produces desired and undesired results would help. Since this year January had 5 weeks (based on Sunday start) I'm thinking that might be your problem. Perhaps base your week start date on the day you do these tours.

 

[tcfd1166]

Perhaps you need to incorporate week function on some other day than Sunday (default)? Some sample data that produces desired and undesired results would help. Since this year January had 5 weeks (based on Sunday start) I'm thinking that might be your problem. Perhaps base your week start date on the day you do these tours.

Our tours start on Sunday, run for 28 days.

 

[Micron]

Some sample data that produces desired and undesired results would help.

It could be that your expression will work but you need to +1 because otherwise the starting point is 0 and it should be 1.
Could be that using DateDiff function to get number of days elapsed since the date that corresponds to the1st Sunday in January & divide by 28, but still would need to add 1. I'm still just assuming that the current number should be 5, not 4.

 

[jackd]

You might start with a function like the following and adjust to your needs

Function getSundays(Dstart As Date, dEnd As Date)
    Dim TourNumber As Integer
       Do While Dstart <= dEnd
        If Weekday(Dstart) = vbSunday Then
            TourNumber = TourNumber + 1
            Debug.Print "Tour " & TourNumber & " starts " & WeekdayName(Weekday(Dstart)) & "  " & Dstart
            Dstart = DateAdd("d", 28, Dstart)
        Else
            Dstart = DateAdd("d", 1, Dstart)
        End If
    Loop
End Function

Test routine

Sub TestGetSundays()
Dim i As String
i = getSundays(#1/1/2022#, #1/15/2023#)
End Sub

Sample output:
Tour 1 starts Sunday 02-Jan-22
Tour 2 starts Sunday 30-Jan-22
Tour 3 starts Sunday 27-Feb-22
Tour 4 starts Sunday 27-Mar-22
Tour 5 starts Sunday 24-Apr-22
Tour 6 starts Sunday 22-May-22
Tour 7 starts Sunday 19-Jun-22
Tour 8 starts Sunday 17-Jul-22
Tour 9 starts Sunday 14-Aug-22
Tour 10 starts Sunday 11-Sep-22
Tour 11 starts Sunday 09-Oct-22
Tour 12 starts Sunday 06-Nov-22
Tour 13 starts Sunday 04-Dec-22
Tour 14 starts Sunday 01-Jan-23

Good luck.