check if last week of month,quarter,semester

[p27br]

Hi

I have a list of week numbers.
I want to have a function that returns a value if the week is the last of the month, a value if last of quarter.
For example :

if islastweekofmonth(weeknumber) and islastweekofquarter(weeknumber) then do something

is there a function that exists ?

thanks for any suggestions

 

[BobJacksonNCI]

I don't have the complete answer, but enjoy working with dates. Specifically, I've only created a handful of functions.

But, I can tell you how to determine the week number of the last week of the current month and the current quarter.

To get the last week number for the current month:

datepart("ww",DateSerial(Year(Date()),month(date())+1,0))

To get the last week number for the current quarter:

datepart("ww",DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+4,0))

HTH,
Bob

 

[Loomah]

This could get messy!

This function will (or at least should) tell you if a specific week number is the last week of the month. I don't really have time to convert it for periods but it shouldn't be hard. There is probably a much easier solution but in the absence of that this will suck your system resources enough!!

Function IsItTheLastWeekOfMonth(week As Integer) As Boolean
Dim iJan As Integer
Dim iFeb As Integer
Dim iMar As Integer
Dim iApr As Integer
Dim iMay As Integer
Dim iJun As Integer
Dim iJul As Integer
Dim iAug As Integer
Dim iSep As Integer
Dim iOct As Integer
Dim iNov As Integer
Dim iDec As Integer
Dim iYear As Integer


iYear = Year(Now)
iJan = weeknum(edate("1 / 1 /" & iYear, 1) - 1)
iFeb = weeknum(edate("1 / 1 /" & iYear, 2) - 1)
iMar = weeknum(edate("1 / 1 /" & iYear, 3) - 1)
iApr = weeknum(edate("1 / 1 /" & iYear, 4) - 1)
iMay = weeknum(edate("1 / 1 /" & iYear, 5) - 1)
iJun = weeknum(edate("1 / 1 /" & iYear, 6) - 1)
iJul = weeknum(edate("1 / 1 /" & iYear, 7) - 1)
iAug = weeknum(edate("1 / 1 /" & iYear, 8) - 1)
iSep = weeknum(edate("1 / 1 /" & iYear, 9) - 1)
iOct = weeknum(edate("1 / 1 /" & iYear, 10) - 1)
iNov = weeknum(edate("1 / 1 /" & iYear, 11) - 1)
iDec = weeknum(edate("1 / 1 /" & iYear, 12) - 1)
Application.Volatile

Select Case week
    Case iJan, iFeb, iMar, iApr, iMay, iJun, _
        iJul, iAug, iSep, iOct, iNov, iDec
            IsItTheLastWeekOfMonth = True
    Case Else
            IsItTheLastWeekOfMonth = False
End Select
End Function

You could probably combine this with the suggestions Bob has given you - somehow.

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[p27br]

thanks for the suggestions guys, it's enough to get me started.