formula for if result is friday, make it thursday.

[joe schmo]

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

 

[Ron Rosenfeld]

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