Season Numbering Work Calendar

[Lewzerrrr]

Hi,

My week 1 starts from August 27th 2017 which would be classed as AW17 (Autumn/Winter).
My week 27 starts from February 25th 2018 which would be classed as SS18 (Spring/Summer).

I need a piece of code that would pull the 17 or 18 from current work calendar.. if I use Format(Now(), "yy") this would obviously give me the 18 that I'm searching for at this exact time period but as soon as it gets to January 1st, it would be off as I'd be going into AW18 and would still be needing 18 up until March 3rd 2019.

WeekNumber = Format(Now() - 238, "ww")
WeekName = Format(Now() - 2, "dddd")


Select Case WeekNumber


    Case 1 To 26
    
        Season = "AW" & Format(Now(), "yy")
    
    Case 27 To 53
    
        Season = "SS" & Format(Now(), "yy")


End Select

If you need me to clarify anything then let me know,

Any help would be greatly appreciated.

 

[jmacleary]

This should do it I think:

WeekNumber = Format(Now() - 238, "ww")
WeekName = Format(Now() - 2, "dddd")


Select Case WeekNumber


    Case 1 To 26
    
        Season = "AW" & Format(Now()-56, "yy")
    
    Case 27 To 53
    
        Season = "SS" & Format(Now()-56, "yy")


End Select

 

[Brombrough]

Sub AWSS_tester()
'if you call the
Dim datSeasonStart As Date
Dim datSought As Date
Dim lngSeasonWeekNo As Long
'****************************************************************************
'This function will be good for any year
'enter with datSeasonStart = Start date for current year
'enter with datSought = date to be found
'returns week no

'tested with the following dates
'Debug.Print getSeasonWeekNo(#8/27/2017#, #2/24/2018#) answer = 26
'Debug.Print getSeasonWeekNo(#8/27/2017#, #8/26/2017#) answer = 0
'Debug.Print getSeasonWeekNo(#8/27/2017#, #9/1/2017#) answer = 1
'Debug.Print getSeasonWeekNo(#8/27/2017#, #6/5/2018#) answer = 41

'*****************************************************************************
datSeasonStart = #8/27/2017#
datSought = #4/23/2018# 'answer = 35

lngSeasonWeekNo = getSeasonWeekNo(datSeasonStart, datSought)

Debug.Print lngSeasonWeekNo

End Sub
Public Function getSeasonWeekNo(datSeasonStart As Date, datSought As Date) As Long
Dim datCheck As Date
Dim lngSWeekNo As Long


If datSought < datSeasonStart Then
getSeasonWeekNo = 0 'set weeks to 0 date before start of season
Exit Function
End If
lngSWeekNo = 0
datCheck = datSeasonStart


Do
datCheck = DateAdd("D", 7, datCheck)
lngSWeekNo = lngSWeekNo + 1
Loop Until datCheck >= datSought


getSeasonWeekNo = lngSWeekNo
End Function