Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default formula for if result is friday, make it thursday.

I have the following formula:
=WORKDAY(DATE(YEAR(G1),MONTH(A46)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(G1),MONTH(A46)+1,0),2)-5)),-7)

basically it gives me the 8th last working day of the month. Now if the
result of that is a Friday, I want it to diplay Thursday's date. or if the
result is Monday, I want it to display Tuesday's date.

Does this make sense? any help greatly appreciated.
thanks
joe
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default formula for if result is friday, make it thursday.

On Wed, 6 Feb 2008 10:28:05 -0800, joe schmo
wrote:

I have the following formula:
=WORKDAY(DATE(YEAR(G1),MONTH(A46)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(G1),MONTH(A46)+1,0),2)-5)),-7)

basically it gives me the 8th last working day of the month. Now if the
result of that is a Friday, I want it to diplay Thursday's date. or if the
result is Monday, I want it to display Tuesday's date.

Does this make sense? any help greatly appreciated.
thanks
joe



How about:

=WORKDAY(DATE(YEAR(G1), MONTH(A46)+1,0),-8)-
(WEEKDAY(WORKDAY(DATE(YEAR(G1), MONTH(A46)+1,0),-8))=6)+
(WEEKDAY(WORKDAY(DATE(YEAR(G1), MONTH(A46)+1,0),-8))=2)

IF I am off by one day in interpreting your "8th last working day", merely
adjust the -8 factor accordingly.
--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
1st and 3rd Thursday formula David Excel Discussion (Misc queries) 13 April 16th 18 09:29 PM
How do I make a formula read a result rather than a formula Chris Excel Discussion (Misc queries) 7 June 20th 06 10:56 PM
If the result of a formula is negative make equal to zero Nrippe New Users to Excel 5 June 12th 06 10:39 PM
Make the result of a formula red (cannot be done in cond. format.) Susan Renee Excel Discussion (Misc queries) 4 May 11th 06 05:51 PM
Can I make a formula in Excel to display result in same cell? Neiko Excel Worksheet Functions 2 October 1st 05 10:36 PM


All times are GMT +1. The time now is 05:53 AM.

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"