[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 840
  • Last Modified:

The last saturday of every month for any give month

Thanks,
JP
0
easycapital
Asked:
easycapital
2 Solutions
 
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 KwanCommented:
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
 
Peter KwanCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
 
barry houdiniCommented:
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now