find date of last weekday of a month
A straight-forward way
=IF(A1MAX(IF(WEEKDAY(ROW(INDIRECT(A1&":"&A1+32-DAY(A1+32))),2)=5,ROW(INDIRE
CT(A1&":"&A1+32-DAY(A1+32))))),
MAX(IF(WEEKDAY(ROW(INDIRECT(A1+7&":"&A1+39-DAY(A1+39))),2)=5,ROW(INDIRECT(A1
+7&":"&A1+39-DAY(A1+39))))),
MAX(IF(WEEKDAY(ROW(INDIRECT(A1&":"&A1+32-DAY(A1+32))),2)=5,ROW(INDIRECT(A1&"
:"&A1+32-DAY(A1+32))))))
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"TUNGANA KURMA RAJU" wrote in
message ...
Thank you Biff, I want another tough task to achieve.
Month end(last) thursday of a month is expirydate of a product.If I buy
the
product before or last thursday of the month the expiry date is date of
the
last thursday.The new expiry cycle starts from last friday.How to write a
formula to achieve this.
Example:
cellA1(Buy date) -------------------------------Cell B1(Expiry date)
01-OCT-2006------THE RESULT WILL BE ----- 26-OCT-2006
25-OCT-2006 ------ ------DO------------------26-OCT-2006
26-OCT-2006------ ----------DO------------------26-OCT-2006
27-OCT-2006----- ----------DO------------------30-NOV-2006
29-NOV-2006-----------------DO------------------30-NOV-2006
01-SEP-2006------------------DO-----------------28-SEP-2006
01-DEC-2006-----------------DO------------------28-DEC-2006
"Biff" wrote:
Here's one way:
Array entered (CTRL,SHIFT,ENTER):
=MAX(IF(WEEKDAY(ROW(INDIRECT(A1&":"&A1+32-DAY(A1+32))),2)=5,ROW(INDIRECT(A1&
":"&A1+32-DAY(A1+32)))))
This one's a few keystrokes shorter but requires the Analysis ToolPak
add-in:
=MAX(IF(WEEKDAY(ROW(INDIRECT(A1&":"&EOMONTH(A1,0)) ),2)=5,ROW(INDIRECT(A1&":"
&EOMONTH(A1,0)))))
Biff
"TUNGANA KURMA RAJU" wrote
in
message ...
I need a function/formula to get date of last friday of a month.
if cell A1=10-OCT-2006 --- RESULT IN cell B1=27-OCT-2006
if cell A1=01-SEP-2006 ----RESULT IN CELL B1=29-sep-2006.
|