CODEWITHSUNDEEP
exceldatefinddayofweek
Bradley Jones
Bradley Jones

Reputation: 61

Find Last Chosen Day of a Chosen Month

I have this code which allows me to find the first or last given day of the given month.

=DATE(B2,A2,1+7*n)-WEEKDAY(DATE(B2,A2,8-xday))

I want something which will find only the last given day of the given month without me having to figure out whether the month has 4 or 5 Mondays.

If someone can show me how to create the result I want in VBA that would be perfect.

Upvotes: 0

Views: 113

Answers (2)

Bradley Jones
Bradley Jones

Reputation: 61

The answer to my Question thanks to @Dave & edited by me is:

=IF(DATE(B2,D4,36)-DAY(DATE(B2,D4,8-xday))<DATE(B2,D4+1,1),DATE(B2,D4,36)-DAY(DATE(B2,D4,8-xday)),DATE(B2,D4,29)-DAY(DATE(B2,D4,8-xday)))

Where:

  • B2 = Year
  • D4 = Month (1-12)
  • Xday = Day (1-7)

Upvotes: 0

user4039065
user4039065

Reputation:

For this data layout,

      Last weekday in eomonth

The formula in D2 is,

=EOMONTH(A$2,0)-WEEKDAY(EOMONTH(A$2,0)-C2)*(WEEKDAY(EOMONTH(A$2,0))<>C2)

C2:C8 and c11:C17 are simply 1->7 formatted as ddd.

The VBA for that would be something like this for last Tuesday (weekday 3) in August.

dim lastDate as date
'last Tue in Aug-2015
lastDate = dateserial(2015, 9, 0)-abs(weekday(dateserial(2015, 9, 0)-3)*(weekday(dateserial(2015, 9, 0))<>3))
debug.print lastDate
'last Sat in Jul-2015
lastDate = dateserial(2015, 8, 0)-abs(weekday(dateserial(2015, 8, 0)-7)*(weekday(dateserial(2015, 8, 0))<>7))
debug.print lastDate

Note that VBA shortcut for the worksheet EOMONTH function is simply the zero day of the next month. The Abs function is necessary as VBA treats True as -1, not 1 like a worksheet.

Upvotes: 1

Related Questions