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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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

K
  • Solved
Amounts distributed by months based on two dates.
Replies
4
Views
153
Peter_SSs
Peter_SSs
S
  • Solved
Conditional formatting - Automatic banding of months
Replies
4
Views
150
etaf
etaf
L
  • Solved
Expected Delivery Date - Specific Delivery Week Days
Replies
6
Views
388
bobsan42
bobsan42
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
2K
phiero21
P
Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
647
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

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