Greetings,

Tom Ogilvy gave a wonderful tutorial on the DATE() command, but there
was one thing missing. How do you get the last Sunday in a month?

Any help would be appreciated.

TIA

-Minitman

RefDate: [any date]
First of next month: =Date(Year(RefDate),Month(RefDate)+1,1)
Day of week of first of next month: =WeekDay(FirstOfNextMonth)
Last Sunday of current month: = FirstOfNextMonth-DayOfWeek+1

You could combine all of that into one formula if you wanted.




"Minitman" wrote in message
...
Greetings,

Tom Ogilvy gave a wonderful tutorial on the DATE() command, but there
was one thing missing. How do you get the last Sunday in a month?

Any help would be appreciated.

TIA

-Minitman

Hello Minitman,

I am not sure what Tom would do, but this is my approach. Find th
first Sunday back from the 1st of next month. This assumes Sunday i
the First day of the week.

Add a VBA Module to your project and copy this code to it. This macr
can be used as a Worksheet Formula or as a VBA Function call. Be sur
to format the Cell or Variable as a Date.

WORKSHEET FORMULA EXAMPLE
=LastSunday()


Code
-------------------
Public Function LastSunday() As Date

Dim D, X

X = DateSerial(Year(Now), Month(Now)+1, 1)
D = Weekday(X) - 1

LastSunday = X - D

End Function

-------------------

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=48476