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:
Thank you in advance for any help you can provide me -- I sincerely appreciate it.
Respectfully,
Mark
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.
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 |