Solved

The last saturday of every month for any give month

Posted on 2011-02-20
7
822 Views
Last Modified: 2012-05-11
Thanks,
JP
0
Comment
Question by:easycapital
7 Comments
 
LVL 1

Expert Comment

by:dev00790
ID: 34937339
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
 
LVL 16

Assisted Solution

by:Peter Kwan
Peter Kwan earned 100 total points
ID: 34937366
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
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 34937388
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

Expert Comment

by:patrickab
ID: 34937406
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34937411
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 400 total points
ID: 34937669
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
 

Author Closing Comment

by:easycapital
ID: 34938530
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

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question