# Convert date to next Friday with month begin/end modifications

#### Marcus702

##### New Member
Hi, I'm hoping someone can help me with a date conversion formula -- I need a single formula that references a single date cell.

I need to group calendar days by the end date of reporting "weeks" within each month, but I need a little more complexity for handling the first and last "weeks" of each month. The goal is to end up with "weeks" that end on a Fri (month end being the only possible exception) and that are not less than 4 days long -- the last "week" in the month will always end on the month end date. Let me see if I can adequately explain:
• Whole (7 day long) weeks in the middle of the month are defined as Sat-Fri weeks, and I want the Friday end date
• If the first Friday in the month is less than 3 days from the start of the month (First Fri - 1st of month < 3), then I need the 2nd Friday in the month assigned to the first three days of the month and the first whole, Sat-Fri week. The idea here is to avoid having a reporting period at the start of each month that is only 1-3 days long. If it's 4-7 days long, then it's the first reporting "week" of the month and ends on the first Friday of the month.
• Similarly, if the last Friday of the month is less than 3 days from the end of the month (End of month - last Fri < 3), then I need the end of the month date assigned to the last, whole Sat-Fri week in the month and the remaining 1-3 days of the month. If there are 4-7 days in the month after the last Friday, then they become the last reporting "week" of the month and are assigned the end of the month date.
I hope that makes sense. Here's how 2020 looks following the above rules (below)

Thank you in advance for any help you can provide me -- I sincerely appreciate it.

Respectfully,

Mark

 Date Report "Week" 1/1/2020 1/10/2020 1/2/2020 1/10/2020 1/3/2020 1/10/2020 1/4/2020 1/10/2020 1/5/2020 1/10/2020 1/6/2020 1/10/2020 1/7/2020 1/10/2020 1/8/2020 1/10/2020 1/9/2020 1/10/2020 1/10/2020 1/10/2020 1/11/2020 1/17/2020 1/12/2020 1/17/2020 1/13/2020 1/17/2020 1/14/2020 1/17/2020 1/15/2020 1/17/2020 1/16/2020 1/17/2020 1/17/2020 1/17/2020 1/18/2020 1/24/2020 1/19/2020 1/24/2020 1/20/2020 1/24/2020 1/21/2020 1/24/2020 1/22/2020 1/24/2020 1/23/2020 1/24/2020 1/24/2020 1/24/2020 1/25/2020 1/31/2020 1/26/2020 1/31/2020 1/27/2020 1/31/2020 1/28/2020 1/31/2020 1/29/2020 1/31/2020 1/30/2020 1/31/2020 1/31/2020 1/31/2020 2/1/2020 2/7/2020 2/2/2020 2/7/2020 2/3/2020 2/7/2020 2/4/2020 2/7/2020 2/5/2020 2/7/2020 2/6/2020 2/7/2020 2/7/2020 2/7/2020 2/8/2020 2/14/2020 2/9/2020 2/14/2020 2/10/2020 2/14/2020 2/11/2020 2/14/2020 2/12/2020 2/14/2020 2/13/2020 2/14/2020 2/14/2020 2/14/2020 2/15/2020 2/21/2020 2/16/2020 2/21/2020 2/17/2020 2/21/2020 2/18/2020 2/21/2020 2/19/2020 2/21/2020 2/20/2020 2/21/2020 2/21/2020 2/21/2020 2/22/2020 2/29/2020 2/23/2020 2/29/2020 2/24/2020 2/29/2020 2/25/2020 2/29/2020 2/26/2020 2/29/2020 2/27/2020 2/29/2020 2/28/2020 2/29/2020 2/29/2020 2/29/2020 3/1/2020 3/6/2020 3/2/2020 3/6/2020 3/3/2020 3/6/2020 3/4/2020 3/6/2020 3/5/2020 3/6/2020 3/6/2020 3/6/2020 3/7/2020 3/13/2020 3/8/2020 3/13/2020 3/9/2020 3/13/2020 3/10/2020 3/13/2020 3/11/2020 3/13/2020 3/12/2020 3/13/2020 3/13/2020 3/13/2020 3/14/2020 3/20/2020 3/15/2020 3/20/2020 3/16/2020 3/20/2020 3/17/2020 3/20/2020 3/18/2020 3/20/2020 3/19/2020 3/20/2020 3/20/2020 3/20/2020 3/21/2020 3/27/2020 3/22/2020 3/27/2020 3/23/2020 3/27/2020 3/24/2020 3/27/2020 3/25/2020 3/27/2020 3/26/2020 3/27/2020 3/27/2020 3/27/2020 3/28/2020 3/31/2020 3/29/2020 3/31/2020 3/30/2020 3/31/2020 3/31/2020 3/31/2020 4/1/2020 4/10/2020 4/2/2020 4/10/2020 4/3/2020 4/10/2020 4/4/2020 4/10/2020 4/5/2020 4/10/2020 4/6/2020 4/10/2020 4/7/2020 4/10/2020 4/8/2020 4/10/2020 4/9/2020 4/10/2020 4/10/2020 4/10/2020 4/11/2020 4/17/2020 4/12/2020 4/17/2020 4/13/2020 4/17/2020 4/14/2020 4/17/2020 4/15/2020 4/17/2020 4/16/2020 4/17/2020 4/17/2020 4/17/2020 4/18/2020 4/24/2020 4/19/2020 4/24/2020 4/20/2020 4/24/2020 4/21/2020 4/24/2020 4/22/2020 4/24/2020 4/23/2020 4/24/2020 4/24/2020 4/24/2020 4/25/2020 4/30/2020 4/26/2020 4/30/2020 4/27/2020 4/30/2020 4/28/2020 4/30/2020 4/29/2020 4/30/2020 4/30/2020 4/30/2020 5/1/2020 5/8/2020 5/2/2020 5/8/2020 5/3/2020 5/8/2020 5/4/2020 5/8/2020 5/5/2020 5/8/2020 5/6/2020 5/8/2020 5/7/2020 5/8/2020 5/8/2020 5/8/2020 5/9/2020 5/15/2020 5/10/2020 5/15/2020 5/11/2020 5/15/2020 5/12/2020 5/15/2020 5/13/2020 5/15/2020 5/14/2020 5/15/2020 5/15/2020 5/15/2020 5/16/2020 5/22/2020 5/17/2020 5/22/2020 5/18/2020 5/22/2020 5/19/2020 5/22/2020 5/20/2020 5/22/2020 5/21/2020 5/22/2020 5/22/2020 5/22/2020 5/23/2020 5/31/2020 5/24/2020 5/31/2020 5/25/2020 5/31/2020 5/26/2020 5/31/2020 5/27/2020 5/31/2020 5/28/2020 5/31/2020 5/29/2020 5/31/2020 5/30/2020 5/31/2020 5/31/2020 5/31/2020 6/1/2020 6/5/2020 6/2/2020 6/5/2020 6/3/2020 6/5/2020 6/4/2020 6/5/2020 6/5/2020 6/5/2020 6/6/2020 6/12/2020 6/7/2020 6/12/2020 6/8/2020 6/12/2020 6/9/2020 6/12/2020 6/10/2020 6/12/2020 6/11/2020 6/12/2020 6/12/2020 6/12/2020 6/13/2020 6/19/2020 6/14/2020 6/19/2020 6/15/2020 6/19/2020 6/16/2020 6/19/2020 6/17/2020 6/19/2020 6/18/2020 6/19/2020 6/19/2020 6/19/2020 6/20/2020 6/26/2020 6/21/2020 6/26/2020 6/22/2020 6/26/2020 6/23/2020 6/26/2020 6/24/2020 6/26/2020 6/25/2020 6/26/2020 6/26/2020 6/26/2020 6/27/2020 6/30/2020 6/28/2020 6/30/2020 6/29/2020 6/30/2020 6/30/2020 6/30/2020 7/1/2020 7/10/2020 7/2/2020 7/10/2020 7/3/2020 7/10/2020 7/4/2020 7/10/2020 7/5/2020 7/10/2020 7/6/2020 7/10/2020 7/7/2020 7/10/2020 7/8/2020 7/10/2020 7/9/2020 7/10/2020 7/10/2020 7/10/2020 7/11/2020 7/17/2020 7/12/2020 7/17/2020 7/13/2020 7/17/2020 7/14/2020 7/17/2020 7/15/2020 7/17/2020 7/16/2020 7/17/2020 7/17/2020 7/17/2020 7/18/2020 7/24/2020 7/19/2020 7/24/2020 7/20/2020 7/24/2020 7/21/2020 7/24/2020 7/22/2020 7/24/2020 7/23/2020 7/24/2020 7/24/2020 7/24/2020 7/25/2020 7/31/2020 7/26/2020 7/31/2020 7/27/2020 7/31/2020 7/28/2020 7/31/2020 7/29/2020 7/31/2020 7/30/2020 7/31/2020 7/31/2020 7/31/2020 8/1/2020 8/7/2020 8/2/2020 8/7/2020 8/3/2020 8/7/2020 8/4/2020 8/7/2020 8/5/2020 8/7/2020 8/6/2020 8/7/2020 8/7/2020 8/7/2020 8/8/2020 8/14/2020 8/9/2020 8/14/2020 8/10/2020 8/14/2020 8/11/2020 8/14/2020 8/12/2020 8/14/2020 8/13/2020 8/14/2020 8/14/2020 8/14/2020 8/15/2020 8/21/2020 8/16/2020 8/21/2020 8/17/2020 8/21/2020 8/18/2020 8/21/2020 8/19/2020 8/21/2020 8/20/2020 8/21/2020 8/21/2020 8/21/2020 8/22/2020 8/31/2020 8/23/2020 8/31/2020 8/24/2020 8/31/2020 8/25/2020 8/31/2020 8/26/2020 8/31/2020 8/27/2020 8/31/2020 8/28/2020 8/31/2020 8/29/2020 8/31/2020 8/30/2020 8/31/2020 8/31/2020 8/31/2020 9/1/2020 9/4/2020 9/2/2020 9/4/2020 9/3/2020 9/4/2020 9/4/2020 9/4/2020 9/5/2020 9/11/2020 9/6/2020 9/11/2020 9/7/2020 9/11/2020 9/8/2020 9/11/2020 9/9/2020 9/11/2020 9/10/2020 9/11/2020 9/11/2020 9/11/2020 9/12/2020 9/18/2020 9/13/2020 9/18/2020 9/14/2020 9/18/2020 9/15/2020 9/18/2020 9/16/2020 9/18/2020 9/17/2020 9/18/2020 9/18/2020 9/18/2020 9/19/2020 9/25/2020 9/20/2020 9/25/2020 9/21/2020 9/25/2020 9/22/2020 9/25/2020 9/23/2020 9/25/2020 9/24/2020 9/25/2020 9/25/2020 9/25/2020 9/26/2020 9/30/2020 9/27/2020 9/30/2020 9/28/2020 9/30/2020 9/29/2020 9/30/2020 9/30/2020 9/30/2020 10/1/2020 10/9/2020 10/2/2020 10/9/2020 10/3/2020 10/9/2020 10/4/2020 10/9/2020 10/5/2020 10/9/2020 10/6/2020 10/9/2020 10/7/2020 10/9/2020 10/8/2020 10/9/2020 10/9/2020 10/9/2020 10/10/2020 10/16/2020 10/11/2020 10/16/2020 10/12/2020 10/16/2020 10/13/2020 10/16/2020 10/14/2020 10/16/2020 10/15/2020 10/16/2020 10/16/2020 10/16/2020 10/17/2020 10/23/2020 10/18/2020 10/23/2020 10/19/2020 10/23/2020 10/20/2020 10/23/2020 10/21/2020 10/23/2020 10/22/2020 10/23/2020 10/23/2020 10/23/2020 10/24/2020 10/31/2020 10/25/2020 10/31/2020 10/26/2020 10/31/2020 10/27/2020 10/31/2020 10/28/2020 10/31/2020 10/29/2020 10/31/2020 10/30/2020 10/31/2020 10/31/2020 10/31/2020 11/1/2020 11/6/2020 11/2/2020 11/6/2020 11/3/2020 11/6/2020 11/4/2020 11/6/2020 11/5/2020 11/6/2020 11/6/2020 11/6/2020 11/7/2020 11/13/2020 11/8/2020 11/13/2020 11/9/2020 11/13/2020 11/10/2020 11/13/2020 11/11/2020 11/13/2020 11/12/2020 11/13/2020 11/13/2020 11/13/2020 11/14/2020 11/20/2020 11/15/2020 11/20/2020 11/16/2020 11/20/2020 11/17/2020 11/20/2020 11/18/2020 11/20/2020 11/19/2020 11/20/2020 11/20/2020 11/20/2020 11/21/2020 11/30/2020 11/22/2020 11/30/2020 11/23/2020 11/30/2020 11/24/2020 11/30/2020 11/25/2020 11/30/2020 11/26/2020 11/30/2020 11/27/2020 11/30/2020 11/28/2020 11/30/2020 11/29/2020 11/30/2020 11/30/2020 11/30/2020 12/1/2020 12/4/2020 12/2/2020 12/4/2020 12/3/2020 12/4/2020 12/4/2020 12/4/2020 12/5/2020 12/11/2020 12/6/2020 12/11/2020 12/7/2020 12/11/2020 12/8/2020 12/11/2020 12/9/2020 12/11/2020 12/10/2020 12/11/2020 12/11/2020 12/11/2020 12/12/2020 12/18/2020 12/13/2020 12/18/2020 12/14/2020 12/18/2020 12/15/2020 12/18/2020 12/16/2020 12/18/2020 12/17/2020 12/18/2020 12/18/2020 12/18/2020 12/19/2020 12/25/2020 12/20/2020 12/25/2020 12/21/2020 12/25/2020 12/22/2020 12/25/2020 12/23/2020 12/25/2020 12/24/2020 12/25/2020 12/25/2020 12/25/2020 12/26/2020 12/31/2020 12/27/2020 12/31/2020 12/28/2020 12/31/2020 12/29/2020 12/31/2020 12/30/2020 12/31/2020 12/31/2020 12/31/2020

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Dave Patton

##### Well-known Member
The following gives the First and Last Fridays of a month. I hope this helps.
You hopefully can adjust for your rules.
T202011b.xlsm
BCD
1First FridayLast Friday
2Nov 23, 2020Fri Nov 6, 2020Fri Nov 27, 2020
3
3a
Cell Formulas
RangeFormula
C2C2=WORKDAY.INTL(EOMONTH(B2,-1),1,"1111011")
D2D2=WORKDAY.INTL(EOMONTH(B2,0)+1,-1,"1111011")

Last edited:

#### Marcus702

##### New Member
Thanks, Dave.

I figured it out:
=IF(
([DATE]+7-WEEKDAY([DATE]+1))-([DATE]-DAY([DATE])+1)<3,
([DATE]+7-WEEKDAY([DATE]+1))+7,
IF(
EOMONTH([DATE],0)-([DATE]+7-WEEKDAY([DATE]+1))<=3,
EOMONTH([DATE],0),
([DATE]+7-WEEKDAY([DATE]+1))
)
)

Replies
0
Views
27
Replies
5
Views
40
Replies
10
Views
235
Replies
2
Views
52
Replies
3
Views
41