Solved

The last saturday of every month for any give month

Posted on 2011-02-20
7
816 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DBF to ... Converter 5 49
Excel copy picture into Outlook email 7 47
increment numbers by 10 11 31
Error in formula not increasing value 2 15
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now