The last saturday of every month for any give month

Thanks,
JP
easycapitalAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
barry houdiniConnect With a Mentor Commented:
Hello Patrick,

That formula gives me a Wednesday, 30th March 2011

Juan,

If you want the last Saturday of the current month you could do that with this formula

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

assuming today's date in A1

....or shorter with EOMONTH

=EOMONTH(A1,0)+1-WEEKDAY(EOMONTH(A1,0)+1)

If you then want a list of "last Saturdays" then assuming you have one of the above formulas in A3 then this formula in A4 copied doqwn will give you as many subsequent last Saturdays as you need

=A3+35-(DAY(A3+35)<8)*7

See attached,

To test you can try putting different dates in A1 to see what results you get.....

regards, barry
26834429.xls
0
 
dev00790Commented:
How do you want this?

E.g. when a user puts a date into a certain cell on a worksheet, to output the date of the last saturday of that month into an adjacent cell?

So for 01/02/2011 (UK date format) in cell A1 - to output  26/02/2011 in cell B1?

0
 
Peter KwanConnect With a Mentor Analyst ProgrammerCommented:
Assume your date value is at cell A1, then at other cell, use this formula:

=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)+1))
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Peter KwanAnalyst ProgrammerCommented:
Sorry, should be

=DATE(YEAR(A1),MONTH(A1)+1,0)-MOD(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)+1),7)
Book1.xlsx
0
 
patrickabCommented:
Juan,

Try:

=EOMONTH(TODAY(),1)-(8-WEEKDAY(EOMONTH(TODAY(),2)))

You will need to install the Analysis ToolPak to get EOMONTH()

Hope it does what you want.

Patrick
0
 
patrickabCommented:
Juan,

ps. That formula give the date for the last Saturday in March 2011. Thus the use of EOMONTH((Today(),1) as it's next month. For April 2011 you would need to use EOMONTH((Today(),2)

Patrick
0
 
easycapitalAuthor Commented:
Patrick,
Good to see you again in the questions that I am posting.  On this one, I would have to agree with Barry.
Pkwan,
Thank you for the formula.
Barry,
Many thanks for the 2 approaches - extremely helpful and very well documented.

Thanks,
JP
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.