finding the date of last weekend of a month

newvbie

Board Regular
Joined
Mar 18, 2006
Messages
69
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

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

Code:
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

Well-known Member
Joined
Jan 27, 2006
Messages
912
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

Code:
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

Board Regular
Joined
Mar 18, 2006
Messages
69
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Code:
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Forum statistics

Threads
1,136,260
Messages
5,674,693
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top