Convert date to next Friday with month begin/end modifications

Marcus702

New Member
Joined
Nov 23, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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

DateReport "Week"
1/1/20201/10/2020
1/2/20201/10/2020
1/3/20201/10/2020
1/4/20201/10/2020
1/5/20201/10/2020
1/6/20201/10/2020
1/7/20201/10/2020
1/8/20201/10/2020
1/9/20201/10/2020
1/10/20201/10/2020
1/11/20201/17/2020
1/12/20201/17/2020
1/13/20201/17/2020
1/14/20201/17/2020
1/15/20201/17/2020
1/16/20201/17/2020
1/17/20201/17/2020
1/18/20201/24/2020
1/19/20201/24/2020
1/20/20201/24/2020
1/21/20201/24/2020
1/22/20201/24/2020
1/23/20201/24/2020
1/24/20201/24/2020
1/25/20201/31/2020
1/26/20201/31/2020
1/27/20201/31/2020
1/28/20201/31/2020
1/29/20201/31/2020
1/30/20201/31/2020
1/31/20201/31/2020
2/1/20202/7/2020
2/2/20202/7/2020
2/3/20202/7/2020
2/4/20202/7/2020
2/5/20202/7/2020
2/6/20202/7/2020
2/7/20202/7/2020
2/8/20202/14/2020
2/9/20202/14/2020
2/10/20202/14/2020
2/11/20202/14/2020
2/12/20202/14/2020
2/13/20202/14/2020
2/14/20202/14/2020
2/15/20202/21/2020
2/16/20202/21/2020
2/17/20202/21/2020
2/18/20202/21/2020
2/19/20202/21/2020
2/20/20202/21/2020
2/21/20202/21/2020
2/22/20202/29/2020
2/23/20202/29/2020
2/24/20202/29/2020
2/25/20202/29/2020
2/26/20202/29/2020
2/27/20202/29/2020
2/28/20202/29/2020
2/29/20202/29/2020
3/1/20203/6/2020
3/2/20203/6/2020
3/3/20203/6/2020
3/4/20203/6/2020
3/5/20203/6/2020
3/6/20203/6/2020
3/7/20203/13/2020
3/8/20203/13/2020
3/9/20203/13/2020
3/10/20203/13/2020
3/11/20203/13/2020
3/12/20203/13/2020
3/13/20203/13/2020
3/14/20203/20/2020
3/15/20203/20/2020
3/16/20203/20/2020
3/17/20203/20/2020
3/18/20203/20/2020
3/19/20203/20/2020
3/20/20203/20/2020
3/21/20203/27/2020
3/22/20203/27/2020
3/23/20203/27/2020
3/24/20203/27/2020
3/25/20203/27/2020
3/26/20203/27/2020
3/27/20203/27/2020
3/28/20203/31/2020
3/29/20203/31/2020
3/30/20203/31/2020
3/31/20203/31/2020
4/1/20204/10/2020
4/2/20204/10/2020
4/3/20204/10/2020
4/4/20204/10/2020
4/5/20204/10/2020
4/6/20204/10/2020
4/7/20204/10/2020
4/8/20204/10/2020
4/9/20204/10/2020
4/10/20204/10/2020
4/11/20204/17/2020
4/12/20204/17/2020
4/13/20204/17/2020
4/14/20204/17/2020
4/15/20204/17/2020
4/16/20204/17/2020
4/17/20204/17/2020
4/18/20204/24/2020
4/19/20204/24/2020
4/20/20204/24/2020
4/21/20204/24/2020
4/22/20204/24/2020
4/23/20204/24/2020
4/24/20204/24/2020
4/25/20204/30/2020
4/26/20204/30/2020
4/27/20204/30/2020
4/28/20204/30/2020
4/29/20204/30/2020
4/30/20204/30/2020
5/1/20205/8/2020
5/2/20205/8/2020
5/3/20205/8/2020
5/4/20205/8/2020
5/5/20205/8/2020
5/6/20205/8/2020
5/7/20205/8/2020
5/8/20205/8/2020
5/9/20205/15/2020
5/10/20205/15/2020
5/11/20205/15/2020
5/12/20205/15/2020
5/13/20205/15/2020
5/14/20205/15/2020
5/15/20205/15/2020
5/16/20205/22/2020
5/17/20205/22/2020
5/18/20205/22/2020
5/19/20205/22/2020
5/20/20205/22/2020
5/21/20205/22/2020
5/22/20205/22/2020
5/23/20205/31/2020
5/24/20205/31/2020
5/25/20205/31/2020
5/26/20205/31/2020
5/27/20205/31/2020
5/28/20205/31/2020
5/29/20205/31/2020
5/30/20205/31/2020
5/31/20205/31/2020
6/1/20206/5/2020
6/2/20206/5/2020
6/3/20206/5/2020
6/4/20206/5/2020
6/5/20206/5/2020
6/6/20206/12/2020
6/7/20206/12/2020
6/8/20206/12/2020
6/9/20206/12/2020
6/10/20206/12/2020
6/11/20206/12/2020
6/12/20206/12/2020
6/13/20206/19/2020
6/14/20206/19/2020
6/15/20206/19/2020
6/16/20206/19/2020
6/17/20206/19/2020
6/18/20206/19/2020
6/19/20206/19/2020
6/20/20206/26/2020
6/21/20206/26/2020
6/22/20206/26/2020
6/23/20206/26/2020
6/24/20206/26/2020
6/25/20206/26/2020
6/26/20206/26/2020
6/27/20206/30/2020
6/28/20206/30/2020
6/29/20206/30/2020
6/30/20206/30/2020
7/1/20207/10/2020
7/2/20207/10/2020
7/3/20207/10/2020
7/4/20207/10/2020
7/5/20207/10/2020
7/6/20207/10/2020
7/7/20207/10/2020
7/8/20207/10/2020
7/9/20207/10/2020
7/10/20207/10/2020
7/11/20207/17/2020
7/12/20207/17/2020
7/13/20207/17/2020
7/14/20207/17/2020
7/15/20207/17/2020
7/16/20207/17/2020
7/17/20207/17/2020
7/18/20207/24/2020
7/19/20207/24/2020
7/20/20207/24/2020
7/21/20207/24/2020
7/22/20207/24/2020
7/23/20207/24/2020
7/24/20207/24/2020
7/25/20207/31/2020
7/26/20207/31/2020
7/27/20207/31/2020
7/28/20207/31/2020
7/29/20207/31/2020
7/30/20207/31/2020
7/31/20207/31/2020
8/1/20208/7/2020
8/2/20208/7/2020
8/3/20208/7/2020
8/4/20208/7/2020
8/5/20208/7/2020
8/6/20208/7/2020
8/7/20208/7/2020
8/8/20208/14/2020
8/9/20208/14/2020
8/10/20208/14/2020
8/11/20208/14/2020
8/12/20208/14/2020
8/13/20208/14/2020
8/14/20208/14/2020
8/15/20208/21/2020
8/16/20208/21/2020
8/17/20208/21/2020
8/18/20208/21/2020
8/19/20208/21/2020
8/20/20208/21/2020
8/21/20208/21/2020
8/22/20208/31/2020
8/23/20208/31/2020
8/24/20208/31/2020
8/25/20208/31/2020
8/26/20208/31/2020
8/27/20208/31/2020
8/28/20208/31/2020
8/29/20208/31/2020
8/30/20208/31/2020
8/31/20208/31/2020
9/1/20209/4/2020
9/2/20209/4/2020
9/3/20209/4/2020
9/4/20209/4/2020
9/5/20209/11/2020
9/6/20209/11/2020
9/7/20209/11/2020
9/8/20209/11/2020
9/9/20209/11/2020
9/10/20209/11/2020
9/11/20209/11/2020
9/12/20209/18/2020
9/13/20209/18/2020
9/14/20209/18/2020
9/15/20209/18/2020
9/16/20209/18/2020
9/17/20209/18/2020
9/18/20209/18/2020
9/19/20209/25/2020
9/20/20209/25/2020
9/21/20209/25/2020
9/22/20209/25/2020
9/23/20209/25/2020
9/24/20209/25/2020
9/25/20209/25/2020
9/26/20209/30/2020
9/27/20209/30/2020
9/28/20209/30/2020
9/29/20209/30/2020
9/30/20209/30/2020
10/1/202010/9/2020
10/2/202010/9/2020
10/3/202010/9/2020
10/4/202010/9/2020
10/5/202010/9/2020
10/6/202010/9/2020
10/7/202010/9/2020
10/8/202010/9/2020
10/9/202010/9/2020
10/10/202010/16/2020
10/11/202010/16/2020
10/12/202010/16/2020
10/13/202010/16/2020
10/14/202010/16/2020
10/15/202010/16/2020
10/16/202010/16/2020
10/17/202010/23/2020
10/18/202010/23/2020
10/19/202010/23/2020
10/20/202010/23/2020
10/21/202010/23/2020
10/22/202010/23/2020
10/23/202010/23/2020
10/24/202010/31/2020
10/25/202010/31/2020
10/26/202010/31/2020
10/27/202010/31/2020
10/28/202010/31/2020
10/29/202010/31/2020
10/30/202010/31/2020
10/31/202010/31/2020
11/1/202011/6/2020
11/2/202011/6/2020
11/3/202011/6/2020
11/4/202011/6/2020
11/5/202011/6/2020
11/6/202011/6/2020
11/7/202011/13/2020
11/8/202011/13/2020
11/9/202011/13/2020
11/10/202011/13/2020
11/11/202011/13/2020
11/12/202011/13/2020
11/13/202011/13/2020
11/14/202011/20/2020
11/15/202011/20/2020
11/16/202011/20/2020
11/17/202011/20/2020
11/18/202011/20/2020
11/19/202011/20/2020
11/20/202011/20/2020
11/21/202011/30/2020
11/22/202011/30/2020
11/23/202011/30/2020
11/24/202011/30/2020
11/25/202011/30/2020
11/26/202011/30/2020
11/27/202011/30/2020
11/28/202011/30/2020
11/29/202011/30/2020
11/30/202011/30/2020
12/1/202012/4/2020
12/2/202012/4/2020
12/3/202012/4/2020
12/4/202012/4/2020
12/5/202012/11/2020
12/6/202012/11/2020
12/7/202012/11/2020
12/8/202012/11/2020
12/9/202012/11/2020
12/10/202012/11/2020
12/11/202012/11/2020
12/12/202012/18/2020
12/13/202012/18/2020
12/14/202012/18/2020
12/15/202012/18/2020
12/16/202012/18/2020
12/17/202012/18/2020
12/18/202012/18/2020
12/19/202012/25/2020
12/20/202012/25/2020
12/21/202012/25/2020
12/22/202012/25/2020
12/23/202012/25/2020
12/24/202012/25/2020
12/25/202012/25/2020
12/26/202012/31/2020
12/27/202012/31/2020
12/28/202012/31/2020
12/29/202012/31/2020
12/30/202012/31/2020
12/31/202012/31/2020
 

Some videos you may like

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
Joined
Feb 15, 2002
Messages
4,517
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Nov 23, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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))
)
)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,004
Messages
5,575,519
Members
412,672
Latest member
Tupelo1984
Top