finding the date of last weekend of a month

[newvbie]

Hello, as captioned, how can I use vba to find out the last weekend of a month? Second question: Are there any codes to evenly populate names (without repeating the same names until the second round) on a list to a calendar except Sundays?

Sorry for too many questions.

Cheers.

 

[Andrew Fergus]

Hi

The following sample shows how to find the last Saturday in a given month:

Private Sub GetLastSaturday()

Dim DayNum As Integer, _
    startDate As Date, _
    lastDate As Date, _
    firstWeekEnd As Date

'Pass a value from your spreadsheet to here
'I hard-coded this for testing / illustrative purposes only
startDate = DateSerial(2006, 10, 1) 

'Find the last day of the month
lastDate = DateAdd("d", -1, DateSerial(Year(DateAdd("m", 1, startDate)), _
        Month(DateAdd("m", 1, startDate)), 1))

'Get the day number where 1 = Sunday
DayNum = Weekday(lastDate, vbSunday)

If DayNum < 7 Then
    'Last day is not a Saturday
    'so subtract the weekday number to find the last Saturday
    firstWeekEnd = DateAdd("d", -Weekday(lastDate, vbSunday), lastDate)
Else
    'last day is a Saturday
    firstWeekEnd = lastDate
End If

'Included for testing purpose only
'Instead you should either return the value to a cell (or the function)
MsgBox "The last Saturday for " & Month(startDate) & "/" & Year(startDate) _
    & " is " & Format(firstWeekEnd, "mm/dd/yyyy")

End Sub

You will need to modify this to suit - eg pick up a value from your spreadsheet or pass a value back. Or you could set this up as a function and pass the 'firstDate' into the function and return the last Saturday.

You will notice I mentioned this returns the last Saturday in a month - I did this because a weekend usually starts on a Saturday. Even if the Sunday falls into the following month, this functions still returns the last Saturday in the month. Is that what you wanted or did you want the last complete weekend?

I don't understand your second question sorry. You may need to elaborate.

Andrew
{edited spelling typo}

 

[sunnyland]

Hello to you both,

using a function as suggested by Andrew, with a different approach.
Enter 1 in second parameter if you want the function to return the last sunday.if not leave it blank of put zero

Function LASTWEEKENDOFMONTH(Optional MYDATE As Date, Optional zeroforsaturdayoneforsunday As Boolean) As Date
Dim debut As Byte
Select Case Month(MYDATE)
Case 1, 3, 5, 7, 8, 10, 12
debut = 31
Case 4, 6, 9, 11
debut = 30
Case 2 And Year(MYDATE) Mod 4 = 0
debut = 29
Case 2 And Year(MYDATE) Mod 4 <> 0
debut = 28
End Select
For x = debut To 1 Step -1
Select Case Weekday(VBA.DateSerial(Year(MYDATE), Month(MYDATE), x), vbMonday)
Case 6 And zeroforsaturdayoneforsunday = False
LASTWEEKENDOFMONTH = (VBA.DateSerial(Year(MYDATE), Month(MYDATE), x))
Exit Function
Case 7 And zeroforsaturdayoneforsunday = True
LASTWEEKENDOFMONTH = (VBA.DateSerial(Year(MYDATE), Month(MYDATE), x))
Exit Function
End Select
Next
End Function

I think the second question means:
Is it possible to have a kind of dates series without the sunday in it: ie Monday to Saturday.

This is what I understood of the 2nd question.
Need confirmation to be sure this is what it means

 

[newvbie]

Hello there. Thanks for your help. Sorry for not making myself understood. I indeed did not provide sufficient information about what I needed. What I meant in my prior second question is: I got 15-20 employee names who are not on leave each day. Each month, say 30 days therein, I got to populate these names on a monthly schedule (arranging them to work at a customer counter), one name each day. The names are usually populated evenly in a month without repeating them until the second round (because the 15 or 20 names in the first round of 15 or 20 days have been allocated). Currently, It takes me about 2 hours mannually preparing such a schedule. I'm pondering if it can be done by vba code in excel.

Cheers friends.

 

[barry houdini]

Hi Andrew,

just a note on your code

I don't think it's really necessary to check whether the last day of the month is a Saturday. You can simply take the first day of the next month and subtract it's weekday, e.g. with a worksheet formula

=DATE(YEAR(A1),MONTH(A1)+1,1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))

or, taking the liberty of changing your code, I think it would be

Private Sub GetLastSaturday()

Dim DayNum As Integer, _
    startDate As Date, _
    firstnextmonth As Date, _
    firstWeekEnd As Date

'Pass a value from your spreadsheet to here
'I hard-coded this for testing / illustrative purposes only
startDate = DateSerial(2006, 10, 1)

'Find the first day of the next month
firstnextmonth = DateSerial(Year(DateAdd("m", 1, startDate)), _
        Month(DateAdd("m", 1, startDate)), 1)
        
'subtract the weekday

firstWeekEnd = DateAdd("d", -Weekday(firstnextmonth, vbSunday), firstnextmonth)

'Included for testing purpose only
'Instead you should either return the value to a cell (or the function)
MsgBox "The last Saturday for " & Month(startDate) & "/" & Year(startDate) _
    & " is " & Format(firstWeekEnd, "mm/dd/yyyy")

End Sub

 

[SydneyGeek]

Here's an approach that takes a few steps but should speed things up for you. No VBA.

1. Type all the staff names down one column of a blank sheet, then select them. Tools > Options > Custom Lists
2. In the dialog, click the Import List button at the bottom right. That creates a custom list of your staff names. You can edit / update at any time.
3. In the schedule sheet, type the first day of the month (eg, 1/1/2006). Select and fill down -- you'll have a list of dates incrementing by one. Let's say this starts in A2. In B2, enter the formula =WEEKDAY(A2), and fill down. Now, autofilter the list for anything with a 1 in column B. Those are your Sundays. Select the visible dates, Edit > Go To > Special > Visible Cells Only, then Edit > Delete > Entire Row. Remove the autofilter.
Now you have a list of dates without Sundays.

OK. In C2, type the name of the first person in your list. Double-click and the list will fill down, wrapping around as required. Once you have the system in place, it's a case of --

A. Extend the dates and the WEEKDAY calculation
B. Autofilter and remove the Sundays.
C. Go to the last name on the list, select it and fill down.

Sound like a mouthful but give it a go.

Denis