Find last Friday before last full week of the month

[daland1]

Hi,

I would need a formula that would find the last Friday before the last full week of a month (week with holidays are not considered full week). For example, in 2014, the formula would give the following dates for each month:
Jan 24, 2014
Feb 21, 2014
Mar 21, 2014
Apr 18, 2014
May 23, 2014
Jun 20, 2014
Jul 18, 2014
Aug 22. 2014
Sep 19, 2014
Oct 24, 2014
Nov 21, 2014
Dec 12, 2014

I found this thread but doesn't work http://www.mrexcel.com/forum/excel-questions/623932-get-date-last-friday-month.html

Hope someone can help!

Regards!

 

[Ron Coderre]

Which version of Excel are you using?

 

[daland1]

Excel 2010

 

[Matt Rogers]

Hi.

Test it:

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='3' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='108,75pt'><col width='182,25pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >Date</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >Friday in the
last full week</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >Nov/2014</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >11/28/2014</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >Jan/2015</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >01/30/2015</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >Jul/2015</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >07/24/2015</td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Arial; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>B2</td><td><Span style='color:#222222'>=IF</Span><Span style='color:#0000DD'>(WEEKDAY</Span><Span style='color:#222222'>(EOMONTH</Span><Span style='color:#0000DD'>(A2,0)</Span><Span style='color:#222222'>,2)</Span><Span style='color:#0000DD'>>=6,EOMONTH</Span><Span style='color:#222222'>(A2,0)</Span><Span style='color:#0000DD'>-WEEKDAY</Span><Span style='color:#222222'>(EOMONTH</Span><Span style='color:#0000DD'>(A2,0)</Span><Span style='color:#222222'>,2)</Span><Span style='color:#0000DD'>+5,EOMONTH</Span><Span style='color:#222222'>(A2,0)</Span><Span style='color:#0000DD'>-WEEKDAY</Span><Span style='color:#222222'>(EOMONTH</Span><Span style='color:#0000DD'>(A2,0)</Span><Span style='color:#222222'>,2)</Span><Span style='color:#0000DD'>-2)</Span><Span style='color:#222222'></Span></td></tr></table>

 

[daland1]

Hi Matt,

I should mention it's a full work week (excluding weekend).

It's not quite it since your formula is finding the last Friday of the month except for July 2015 based on your results. I need the Friday before the last full work week of a month.

Thanks!

 

[Ron Coderre]

Listing your holidays would help.

With A1:A12 containing beginning-of-month dates

This regular formula, copied down, returns the first Friday before the last full week (holidays taken into consideration) of the month

B1: =WORKDAY.INTL(EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0)),-1,"1111011",$F$1:$F$1)
-(NETWORKDAYS(WORKDAY.INTL(EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0)),-1,"1111011",$F$1:$F$1),EOMONTH(A1,0),$F$1:$F$1)<>6)*7

I'm not a "date formula" guy, so that can probably be improved.

With those formulas, these are the results:

Fri, Jan 24, 2014
Fri, Feb 21, 2014
Fri, Mar 21, 2014
Fri, Apr 18, 2014
Fri, May 23, 2014
Fri, Jun 20, 2014
Fri, Jul 18, 2014
Fri, Aug 22, 2014
Fri, Sep 19, 2014
Fri, Oct 24, 2014
Fri, Nov 21, 2014
Fri, Dec 12, 2014

Is that something you can work with?

 

[daland1]

Hi Ron,

This is perfect! Can you explain a bit your formula? This would help me for next time.

Regards!

 

[Matt Rogers]

@Ron:
WOW ... !

 

[Ron Coderre]

Thanks.

 

[Ron Coderre]

I can try...

With this formula:

 =WORKDAY.INTL(EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0)),-1,"1111011",$F$1:$F$1)
-(NETWORKDAYS(WORKDAY.INTL(EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0)),-1,"1111011",$F$1:$F$1),EOMONTH(A1,0),$F$1:$F$1)<>6)*7

The first section: WORKDAY.INTL(EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0)),-1,"1111011",$F$1:$F$1)
returns the Friday prior to the last saturday of the referenced month

This section: EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0))
returns the last saturday of the referenced month

This section: WORKDAY.INTL(EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0)),-1,"1111011",$F$1:$F$1),
returns the Friday prior to the last saturday of the referenced month

and THIS section: NETWORKDAYS(WORKDAY.INTL(EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0)),-1,"1111011",$F$1:$F$1),EOMONTH(A1,0),$F$1:$F$1)
returns the count of workdays from the Friday prior to the last saturday of the referenced month through the end of the month

If that number is not 6...then subtract 7 more days

Side note:
This reference: $F$1:$F$1
is the holiday list...I just put 25-Dec-2014 in that cell.
I hope that helps.