Convert date to next Friday with month begin/end modifications

M

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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
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:
Upvote 0
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))
)
)
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Pivot Table Total
Replies
5
Views
240
neasds
N
M
  • Solved
Formula Help.
Replies
3
Views
53
Fluff
Fluff
N
  • Solved
Pivot Table Not Working
Replies
3
Views
147
neasds
N
H
  • Solved
Search for topics based on their groups.
Replies
7
Views
138
Peter_SSs
Peter_SSs
B
  • Solved
Match Project Number & Sum of Charges Past a Certain Date
Replies
3
Views
194
etaf
etaf

Forum statistics

Threads
1,214,821
Messages
6,121,762
Members
449,048
Latest member
excelknuckles

Share this page

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
Back
Top