Find last Friday before last full week of the month

daland1

New Member
Joined
Oct 2, 2007
Messages
5
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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Matt Rogers

Well-known Member
Joined
Sep 23, 2011
Messages
1,030
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

New Member
Joined
Oct 2, 2007
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
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
Code:
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:
Code:
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

New Member
Joined
Oct 2, 2007
Messages
5

ADVERTISEMENT

Hi Ron,

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

Regards!
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
I can try...

With this formula:
Code:
 =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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,984
Messages
5,767,440
Members
425,414
Latest member
chwein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top