ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   find date of last weekday of a month (https://www.excelbanter.com/excel-discussion-misc-queries/116500-find-date-last-weekday-month.html)

TUNGANA KURMA RAJU
find date of last weekday of a month
 
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.

Biff
find date of last weekday of a month
 
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.



TUNGANA KURMA RAJU
find date of last weekday of a month
 
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.




Bob Phillips
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.

Leo Heuser
find date of last weekday of a month
 
"TUNGANA KURMA RAJU" skrev i en
meddelelse ...
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.

Another option:

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

The generic formula is:

=DATE(YEAR(A1),MONTH(A1)+1,1)-(MOD(MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))-1,7)+X,7)+1)

If Sunday = weekday number 1:
X = 6,5,4,3,2,1,0 for last Sunday, Monday, Thuesday, Wednesday,
Thursday, Friday, Saturday
or put another way:
X = 7 - weekday number


--
Best regards
Leo Heuser

Followup to newsgroup only please.

Leo Heuser
find date of last weekday of a month
 
"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

Ron Rosenfeld
find date of last weekday of a month
 
On Fri, 27 Oct 2006 21:27:02 -0700, TUNGANA KURMA RAJU
wrote:

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.

=DATE(YEAR(A1),MONTH(A1)+1,1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)+1)
--ron

Ron Rosenfeld
find date of last weekday of a month
 
On Fri, 27 Oct 2006 23:22:03 -0700, TUNGANA KURMA RAJU
wrote:

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


=DATE(YEAR(BuyDate),MONTH(BuyDate)+1+(BuyDateDATE (YEAR(BuyDate),
MONTH(BuyDate)+1,1)-WEEKDAY(DATE(YEAR(BuyDate),MONTH(BuyDate)+1,1)
+2)),1)-WEEKDAY(DATE(YEAR(BuyDate),MONTH(BuyDate)+1+(BuyDa teDATE(
YEAR(BuyDate),MONTH(BuyDate)+1,1)-WEEKDAY(DATE(YEAR(BuyDate),
MONTH(BuyDate)+1,1)+2)),1)+2)
--ron

Ron Rosenfeld
find date of last weekday of a month
 
On Sat, 28 Oct 2006 13:42:37 +0200, "Leo Heuser"
wrote:

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

Another option:

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

The generic formula is:

=DATE(YEAR(A1),MONTH(A1)+1,1)-(MOD(MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))-1,7)+X,7)+1)

If Sunday = weekday number 1:
X = 6,5,4,3,2,1,0 for last Sunday, Monday, Thuesday, Wednesday,
Thursday, Friday, Saturday
or put another way:
X = 7 - weekday number

I think you can eliminate the MOD functions as WEEKDAY already effectively does
a MOD 7

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

WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))+1


--ron

Leo Heuser
find date of last weekday of a month
 
"Ron Rosenfeld" skrev i en meddelelse
...
On Sat, 28 Oct 2006 13:42:37 +0200, "Leo Heuser"
wrote:

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

Another option:

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

The generic formula is:

=DATE(YEAR(A1),MONTH(A1)+1,1)-(MOD(MOD(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))-1,7)+X,7)+1)

If Sunday = weekday number 1:
X = 6,5,4,3,2,1,0 for last Sunday, Monday, Thuesday, Wednesday,
Thursday, Friday, Saturday
or put another way:
X = 7 - weekday number

I think you can eliminate the MOD functions as WEEKDAY already effectively
does
a MOD 7

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

WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))+1


--ron

You are right. Thanks, Ron!

Leo


All times are GMT +1. The time now is 01:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com