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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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

W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
S
  • Solved
WORKDAY.INTL does not block out holidays
2
Replies
13
Views
551
shawnbull
S
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
376
kevin9999
K
A
  • Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
259
amolvijay
A
P
  • Question
Returning a value based on Day & Date
Replies
5
Views
348
felixstraube
F

Forum statistics

Threads
1,215,088
Messages
6,123,057
Members
449,091
Latest member
ikke

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