"TUNGANA KURMA RAJU" skrev i en
meddelelse ...
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

With my formula:

=IF(A1DATE(YEAR(A1),MONTH(A1)+1,1)-(MOD(MOD(WEEKDAY(DATE(YEAR(A1),
MONTH(A1)+1,1))-1,7)+2,7)+1),DATE(YEAR(A1),MONTH(A1)+2,1)-(MOD(MOD(
WEEKDAY(DATE(YEAR(A1),MONTH(A1)+2,1))-1,7)+2,7)+1),DATE(YEAR(A1),
MONTH(A1)+1,1)-(MOD(MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))-1,7)+2,7)+1))

Leo Heuser