Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > find date of last weekday of a month
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 171
Default 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.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,688
Default 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.


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 171
Default 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.



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,593
Default 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.





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 266
Default 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.



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 266
Default 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



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,651
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,651
Default 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
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,651
Default 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
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 266
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
holiday dates bucci Excel Worksheet Functions 4 June 15th 06 09:35 AM
Find category value based on date range? thewiz1972 Excel Worksheet Functions 5 June 10th 06 03:28 AM
average value from a table TUNGANA KURMA RAJU Excel Discussion (Misc queries) 15 March 21st 06 06:52 AM
Get month from date [email protected] Excel Discussion (Misc queries) 2 November 9th 05 02:43 PM
Pulling a date in the current month Brian Excel Worksheet Functions 2 October 24th 05 05:57 PM


All times are GMT +1. The time now is 03:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement