?
Solved

The last saturday of every month for any give month

Posted on 2011-02-20
7
Medium Priority
?
834 Views
Last Modified: 2012-05-11
Thanks,
JP
0
Comment
Question by:easycapital
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 400 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 1600 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

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 article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

718 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