Calculate Month End Dates (Weekend dates) based on the previous weekend dates and update values

A

Adit

New Member
Joined
Jul 8, 2023
Messages
15
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hello I am trying to calculate month end dates, however should be weekend dates (Saturday being weekend) based on previous week end dates, Please see below example

At any point I will have rolling 52 weeks. Col Z is the 52th week ending date in July 06/07/24 , thereafter in cell C5 the date should be automatically calculated as following month end date , however that should be week end date.

Next I also want that the total weekly sales should be updated against following year's month end date calculated as per above. e.g.

Sum(15/07/23 + 22/07/23 + 29/07/23 + 05/08/23) = 22 , update in Week end date 03/08/24 and so on...

So looking for help on how to setup above dynamically so that when the week end dates chnage , the month end dates get calcuated along with values.

Many thanks in advance.
1689231740603.png
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
I don't really follow your text comments. But, I think you want a month end date based on the date in Row 2, if that is correct, try this:


Book1
ABCDEFGHIJKLMNOP
1Fiscal Week151617181920212223242526272829
2Date (W/E)2023-07-152023-07-222023-07-292023-08-052023-08-122023-08-192023-08-262023-09-022023-09-092023-09-162023-09-232023-09-302023-10-072023-10-142023-10-21
3Sales25510245781011125010012
4
52024-08-032024-08-312024-09-282024-10-262024-11-232024-12-212025-01-182025-02-152025-04-122025-05-102025-06-07
62211
7
8
9Month End of Date in row 2:2023-06-302023-06-302023-06-302023-07-312023-07-312023-07-312023-07-312023-08-312023-08-312023-08-312023-08-312023-08-312023-09-292023-09-29
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24Holidays
252023-01-01
262023-01-17
272023-02-20
282023-05-27
292023-07-04
302023-09-06
312023-10-08
322023-11-11
332023-11-23
342023-12-25
35
36
Sheet1
Cell Formulas
RangeFormula
B9:O9B9=WORKDAY.INTL(B2-DAY(B2)+1,-1,"0000011",$A$25:$A$36)
Book1
ABCDEFGHIJKLMNOP
1Fiscal Week151617181920212223242526272829
2Date (W/E)2023-07-152023-07-222023-07-292023-08-052023-08-122023-08-192023-08-262023-09-022023-09-092023-09-162023-09-232023-09-302023-10-072023-10-142023-10-21
3Sales25510245781011125010012
4
52024-08-032024-08-312024-09-282024-10-262024-11-232024-12-212025-01-182025-02-152025-04-122025-05-102025-06-07
62211
7
8
9Month End of Date in row 2:2023-06-302023-06-302023-06-302023-07-312023-07-312023-07-312023-07-312023-08-312023-08-312023-08-312023-08-312023-08-312023-09-292023-09-29
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24Holidays
252023-01-01
262023-01-17
272023-02-20
282023-05-27
292023-07-04
302023-09-06
312023-10-08
322023-11-11
332023-11-23
342023-12-25
35
36
Sheet1
Cell Formulas
RangeFormula
B9:O9B9=WORKDAY.INTL(B2-DAY(B2)+1,-1,"0000011",$A$25:$A$36)
Book1
ABCDEFGHIJKLMNOP
1Fiscal Week151617181920212223242526272829
2Date (W/E)2023-07-152023-07-222023-07-292023-08-052023-08-122023-08-192023-08-262023-09-022023-09-092023-09-162023-09-232023-09-302023-10-072023-10-142023-10-21
3Sales25510245781011125010012
4
52024-08-032024-08-312024-09-282024-10-262024-11-232024-12-212025-01-182025-02-152025-04-122025-05-102025-06-07
62211
7
8
9Month End of Date in row 2:2023-06-302023-06-302023-06-302023-07-312023-07-312023-07-312023-07-312023-08-312023-08-312023-08-312023-08-312023-08-312023-09-292023-09-29
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24Holidays
252023-01-01
262023-01-17
272023-02-20
282023-05-27
292023-07-04
302023-09-06
312023-10-08
322023-11-11
332023-11-23
342023-12-25
35
36
Sheet1
Cell Formulas
RangeFormula
B9:O9B9=WORKDAY.INTL(B2-DAY(B2)+1,-1,"0000011",$A$25:$A$36)
 
Upvote 0
awoohaw said:
I don't really follow your text comments. But, I think you want a month end date based on the date in Row 2, if that is correct, try this:


Book1
ABCDEFGHIJKLMNOP
1Fiscal Week151617181920212223242526272829
2Date (W/E)2023-07-152023-07-222023-07-292023-08-052023-08-122023-08-192023-08-262023-09-022023-09-092023-09-162023-09-232023-09-302023-10-072023-10-142023-10-21
3Sales25510245781011125010012
4
52024-08-032024-08-312024-09-282024-10-262024-11-232024-12-212025-01-182025-02-152025-04-122025-05-102025-06-07
62211
7
8
9Month End of Date in row 2:2023-06-302023-06-302023-06-302023-07-312023-07-312023-07-312023-07-312023-08-312023-08-312023-08-312023-08-312023-08-312023-09-292023-09-29
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24Holidays
252023-01-01
262023-01-17
272023-02-20
282023-05-27
292023-07-04
302023-09-06
312023-10-08
322023-11-11
332023-11-23
342023-12-25
35
36
Sheet1
Cell Formulas
RangeFormula
B9:O9B9=WORKDAY.INTL(B2-DAY(B2)+1,-1,"0000011",$A$25:$A$36)
Book1
ABCDEFGHIJKLMNOP
1Fiscal Week151617181920212223242526272829
2Date (W/E)2023-07-152023-07-222023-07-292023-08-052023-08-122023-08-192023-08-262023-09-022023-09-092023-09-162023-09-232023-09-302023-10-072023-10-142023-10-21
3Sales25510245781011125010012
4
52024-08-032024-08-312024-09-282024-10-262024-11-232024-12-212025-01-182025-02-152025-04-122025-05-102025-06-07
62211
7
8
9Month End of Date in row 2:2023-06-302023-06-302023-06-302023-07-312023-07-312023-07-312023-07-312023-08-312023-08-312023-08-312023-08-312023-08-312023-09-292023-09-29
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24Holidays
252023-01-01
262023-01-17
272023-02-20
282023-05-27
292023-07-04
302023-09-06
312023-10-08
322023-11-11
332023-11-23
342023-12-25
35
36
Sheet1
Cell Formulas
RangeFormula
B9:O9B9=WORKDAY.INTL(B2-DAY(B2)+1,-1,"0000011",$A$25:$A$36)
Book1
ABCDEFGHIJKLMNOP
1Fiscal Week151617181920212223242526272829
2Date (W/E)2023-07-152023-07-222023-07-292023-08-052023-08-122023-08-192023-08-262023-09-022023-09-092023-09-162023-09-232023-09-302023-10-072023-10-142023-10-21
3Sales25510245781011125010012
4
52024-08-032024-08-312024-09-282024-10-262024-11-232024-12-212025-01-182025-02-152025-04-122025-05-102025-06-07
62211
7
8
9Month End of Date in row 2:2023-06-302023-06-302023-06-302023-07-312023-07-312023-07-312023-07-312023-08-312023-08-312023-08-312023-08-312023-08-312023-09-292023-09-29
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24Holidays
252023-01-01
262023-01-17
272023-02-20
282023-05-27
292023-07-04
302023-09-06
312023-10-08
322023-11-11
332023-11-23
342023-12-25
35
36
Sheet1
Cell Formulas
RangeFormula
B9:O9B9=WORKDAY.INTL(B2-DAY(B2)+1,-1,"0000011",$A$25:$A$36)
Click to expand...
Hi, Thanks for looking into this.Don't think the above resolves the challenge. Let me know which part you don't understand and will try to explain in a different way.
 
Upvote 0
Adit said:
Hi, Thanks for looking into this.Don't think the above resolves the challenge. Let me know which part you don't understand and will try to explain in a different way.
Click to expand...
all of it. can you do this for me:

1. Please take a few week ending dates (i suggest august or september 2023 since those months have month ending on Sat or Sun.)
Please give example of what answer you want.

2. Pleas explain your summation question again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Calculating a YTD Balance based on end date?
Replies
2
Views
112
des378
D
K
  • Solved
Amounts distributed by months based on two dates.
Replies
4
Views
263
Peter_SSs
Peter_SSs
S
  • Solved
Conditional formatting - Automatic banding of months
Replies
4
Views
171
etaf
etaf
L
  • Solved
Expected Delivery Date - Specific Delivery Week Days
Replies
6
Views
495
bobsan42
bobsan42
K
  • Question Question
Trying to make a formula to figure out what total hours in the month will be, based on what I already know
Replies
1
Views
50
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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