Converting Month to Weeks from the current date

J

jajatidev

Board Regular
Joined
Jul 29, 2016
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Hi,
I'm seeking to develop a formula in Excel to calculate the number of weeks left in a month two weeks from the current week.
I use the following formula to get the week and then 7 days are added and dragged to get the next 16 weeks.

Excel Formula: 
14+(TODAY()-WEEKDAY(TODAY())+2)

However, I'm seeking ways to write the formula to automate this calculation so that manual interventions to adjust the cell references can be avoided.
listed below are the points to consider in the formula
  • Week 1 starts on the first Monday of the month; the days before that belong to the last week of the previous month
  • quantity is split equally against the number of weeks in the month
  • quantity for the remaining weeks of the month is considered
Identifying the week no of the month and then splitting the quantity is where I need advise.

Book9
EFGHIJK
9Apr-24May-24Jun-24Jul-24Aug-24
10200200200200200
11
1204/22/202404/29/202405/06/202405/13/202405/20/202405/27/202406/03/2024
1340405050505050
Sheet1
Cell Formulas
RangeFormula
E12E12=14+(TODAY()-WEEKDAY(TODAY())+2)
F12:K12F12=E12+7
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Perhaps something like this:

DEFGHIJKLMNOPQRSTU
9Apr 2024May 2024Jun 2024Jul 2024Aug 2024
10200200200200200
11
12WeekStart22 Apr 202429 Apr 20246 May 202413 May 202420 May 202427 May 20243 Jun 202410 Jun 202417 Jun 202424 Jun 20241 Jul 20248 Jul 202415 Jul 202422 Jul 202429 Jul 20245 Aug 202412 Aug 2024
134040505050505050505040404040405050
Sheet1
Cell Formulas
RangeFormula
E12:U12E12=LET(N,17,SEQUENCE(,N,16+TODAY()-WEEKDAY(TODAY()),7))
E13:U13E13=LET(d,E9:I10,w,E12#,XLOOKUP(E12#,INDEX(d,1,),INDEX(d,2,),,1)/(4+(DAY(w-DAY(w)+35)<WEEKDAY(w-DAY(w)-1))))
Dynamic array formulas.

EDIT: Should probably point out that in the example above, Today() is 10 April 2024
 
Last edited:
Upvote 0
StephenCrump said:
Perhaps something like this:

DEFGHIJKLMNOPQRSTU
9Apr 2024May 2024Jun 2024Jul 2024Aug 2024
10200200200200200
11
12WeekStart22 Apr 202429 Apr 20246 May 202413 May 202420 May 202427 May 20243 Jun 202410 Jun 202417 Jun 202424 Jun 20241 Jul 20248 Jul 202415 Jul 202422 Jul 202429 Jul 20245 Aug 202412 Aug 2024
134040505050505050505040404040405050
Sheet1
Cell Formulas
RangeFormula
E12:U12E12=LET(N,17,SEQUENCE(,N,16+TODAY()-WEEKDAY(TODAY()),7))
E13:U13E13=LET(d,E9:I10,w,E12#,XLOOKUP(E12#,INDEX(d,1,),INDEX(d,2,),,1)/(4+(DAY(w-DAY(w)+35)<WEEKDAY(w-DAY(w)-1))))
Dynamic array formulas.

EDIT: Should probably point out that in the example above, Today() is 10 April 2024
Click to expand...
Thank you, Stephen. The suggestion works on the 1st row of the dataset. How do I extend it to the rest of the data with multiple rows?

Sample For Formulas & Functions.xlsx
BCDEFGHIJKLMNOPQRS
3PartApr-24May-24Jun-24Jul-24Aug-24
4A200200200200200
5B300400500600400
6C100150200200200
7D100100100100100
8
9WeekStart04/29/2405/06/2405/13/2405/20/2405/27/2406/03/2406/10/2406/17/2406/24/2407/01/2407/08/2407/15/2407/22/2407/29/2408/05/2408/12/2408/19/24
10A4050505050505050504040404040505050
11B4050505050505050504040404040505050
12C4050505050505050504040404040505050
13D4050505050505050504040404040505050
Sheet6
Cell Formulas
RangeFormula
C9:S9C9=LET(N,17,SEQUENCE(,N,16+TODAY()-WEEKDAY(TODAY()),7))
C10:S13C10=LET(d,$C$3:G4,w,$C$9#,XLOOKUP($C$9#,INDEX(d,1,),INDEX(d,2,),,1)/(4+(DAY(w-DAY(w)+35)<WEEKDAY(w-DAY(w)-1))))
Dynamic array formulas.
 
Upvote 0
Sorry for the late reply. I've been away for a week.

Try:
ABCDEFGHIJKLMNOPQRS
1
2
3Part30 Apr 202431 May 202430 Jun 202431 Jul 202431 Aug 2024
4A200200200200200
5B300400500600400
6C100150200200200
7D100100100100100
8
9WeekStart29 Apr 20246 May 202413 May 202420 May 202427 May 20243 Jun 202410 Jun 202417 Jun 202424 Jun 20241 Jul 20248 Jul 202415 Jul 202422 Jul 202429 Jul 20245 Aug 202412 Aug 202419 Aug 2024
10A4050505050505050504040404040505050
11C2037.537.537.537.5505050504040404040505050
12D2025252525252525252020202020252525
13B60100100100100125125125125120120120120120100100100
Sheet1
Cell Formulas
RangeFormula
C9:S9C9=LET(N,17,SEQUENCE(,N,16+TODAY()-WEEKDAY(TODAY()),7))
C10:S13C10=LET(MonthEnd,C$3:G$3,Vals,C$4:G$7,w,$C$9#,IFERROR(XLOOKUP(w,MonthEnd,XLOOKUP(B10,B$4:B$7,Vals),,1)/(4+(DAY(w-DAY(w)+35)<WEEKDAY(w-DAY(w)-1))),"-"))
Dynamic array formulas.
 
Upvote 0
StephenCrump said:
Sorry for the late reply. I've been away for a week.

Try:
ABCDEFGHIJKLMNOPQRS
1
2
3Part30 Apr 202431 May 202430 Jun 202431 Jul 202431 Aug 2024
4A200200200200200
5B300400500600400
6C100150200200200
7D100100100100100
8
9WeekStart29 Apr 20246 May 202413 May 202420 May 202427 May 20243 Jun 202410 Jun 202417 Jun 202424 Jun 20241 Jul 20248 Jul 202415 Jul 202422 Jul 202429 Jul 20245 Aug 202412 Aug 202419 Aug 2024
10A4050505050505050504040404040505050
11C2037.537.537.537.5505050504040404040505050
12D2025252525252525252020202020252525
13B60100100100100125125125125120120120120120100100100
Sheet1
Cell Formulas
RangeFormula
C9:S9C9=LET(N,17,SEQUENCE(,N,16+TODAY()-WEEKDAY(TODAY()),7))
C10:S13C10=LET(MonthEnd,C$3:G$3,Vals,C$4:G$7,w,$C$9#,IFERROR(XLOOKUP(w,MonthEnd,XLOOKUP(B10,B$4:B$7,Vals),,1)/(4+(DAY(w-DAY(w)+35)<WEEKDAY(w-DAY(w)-1))),"-"))
Dynamic array formulas.
Click to expand...

I am trying to address issues one after another. So far the suggested function is projecting the desired results. However, now I'm looking at rounding up the decimals where if the monthly forecast is 1 unit then instead of 0.25 units/week is it possible to round up the number to 1 unit under any of the weeks for the month?

Sample For Formulas & Functions.xlsx
BCDEFGHIJKLMNOPQR
3PartApr-24May-24Jun-24Jul-24Aug-24Sep-24Oct-24
4A1111111
5B3333333
6C4444444
7D5555555
8
9
10WeekStart05/06/2405/13/2405/20/2405/27/2406/03/2406/10/2406/17/2406/24/2407/01/2407/08/2407/15/2407/22/2407/29/2408/05/2408/12/2408/19/24
11B0.750.750.750.750.750.750.750.750.60.60.60.60.60.750.750.75
12D1.251.251.251.251.251.251.251.25111111.251.251.25
13C111111110.80.80.80.80.8111
14A0.250.250.250.250.250.250.250.250.20.20.20.20.20.250.250.25
Sheet6
Cell Formulas
RangeFormula
C3C3=EOMONTH(TODAY(),0)
D3:I3D3=EOMONTH(C3,1)
C10:R10C10=LET(N,16,SEQUENCE(,N,16+TODAY()-WEEKDAY(TODAY()),7))
C11:R14C11=LET(MonthEnd,C$3:I$3,Vals,C$4:I$8,w,$C$10#,IFERROR(XLOOKUP(w,MonthEnd,XLOOKUP(B11,B$4:B$8,Vals),,1)/(4+(DAY(w-DAY(w)+35)<WEEKDAY(w-DAY(w)-1))),"-"))
Dynamic array formulas.
 
Upvote 0
The part of the formula returning the numbers is this bit, let's call it YourFormula

YourFormula: XLOOKUP(w,MonthEnd,XLOOKUP(B11,B$4:B$8,Vals),,1)/(4+(DAY(w-DAY(w)+35)<WEEKDAY(w-DAY(w)-1)))

I'm not clear whether you want Minimum = 1, in which case replace with:

MAX(1,YourFormula)

Or perhaps always round up to the next integer, in which case:

ROUNDUP(YourFormula,0)
 
Upvote 0
StephenCrump said:
The part of the formula returning the numbers is this bit, let's call it YourFormula

YourFormula: XLOOKUP(w,MonthEnd,XLOOKUP(B11,B$4:B$8,Vals),,1)/(4+(DAY(w-DAY(w)+35)<WEEKDAY(w-DAY(w)-1)))

I'm not clear whether you want Minimum = 1, in which case replace with:

MAX(1,YourFormula)

Or perhaps always round up to the next integer, in which case:

ROUNDUP(YourFormula,0)
Click to expand...

I have already tried ROUND & ROUNDUP. However, exploring ways to improve on the requirement-splitting approach for the decimal places.
For Example: In months where the requirement is an odd number, with the current formula the quantity gets split equally across the weeks for that month creating decimals. Is it possible to sum up the decimals into a whole number under one of the weeks?

It's just a thought I'm exploring. Not sure whether I'm over-stretching with my ask.
 
Upvote 0
I think you mean something along these lines?

BCDEFGHIJKLMNOPQR
3PartApr 2024May 2024Jun 2024Jul 2024Aug 2024Sep 2024Oct 2024
4A1111111
5B3333333
6C6666666
7D7777777
8
9WeekStart6 May 202413 May 202420 May 202427 May 20243 Jun 202410 Jun 202417 Jun 202424 Jun 20241 Jul 20248 Jul 202415 Jul 202422 Jul 202429 Jul 20245 Aug 202412 Aug 202419 Aug 2024
10A1000100010000100
11B1110111011100111
12C2211221121111221
13D2221222122111222
Sheet1
Cell Formulas
RangeFormula
C3C3=EOMONTH(TODAY(),0)
D3:I3D3=EOMONTH(C3,1)
C9:R9C9=LET(N,16,SEQUENCE(,N,16+TODAY()-WEEKDAY(TODAY()),7))
C10:R13C10=LET(MonthEnd,C$3:I$3,Vals,C$4:I$7,Parts,B$4:B$7,w,$C$9#,N,4+(DAY(w-DAY(w)+35)<WEEKDAY(w-DAY(w)-1)),X,XLOOKUP(w,MonthEnd,XLOOKUP(B10,Parts,Vals),,1),r,ROUNDUP(X/(N),0),IFERROR(r-(ROUNDUP(DAY(C$9#)/7,0)>X+N-N*r),"-"))
Dynamic array formulas.
 
Upvote 0
Solution
th
StephenCrump said:
I think you mean something along these lines?

BCDEFGHIJKLMNOPQR
3PartApr 2024May 2024Jun 2024Jul 2024Aug 2024Sep 2024Oct 2024
4A1111111
5B3333333
6C6666666
7D7777777
8
9WeekStart6 May 202413 May 202420 May 202427 May 20243 Jun 202410 Jun 202417 Jun 202424 Jun 20241 Jul 20248 Jul 202415 Jul 202422 Jul 202429 Jul 20245 Aug 202412 Aug 202419 Aug 2024
10A1000100010000100
11B1110111011100111
12C2211221121111221
13D2221222122111222
Sheet1
Cell Formulas
RangeFormula
C3C3=EOMONTH(TODAY(),0)
D3:I3D3=EOMONTH(C3,1)
C9:R9C9=LET(N,16,SEQUENCE(,N,16+TODAY()-WEEKDAY(TODAY()),7))
C10:R13C10=LET(MonthEnd,C$3:I$3,Vals,C$4:I$7,Parts,B$4:B$7,w,$C$9#,N,4+(DAY(w-DAY(w)+35)<WEEKDAY(w-DAY(w)-1)),X,XLOOKUP(w,MonthEnd,XLOOKUP(B10,Parts,Vals),,1),r,ROUNDUP(X/(N),0),IFERROR(r-(ROUNDUP(DAY(C$9#)/7,0)>X+N-N*r),"-"))
Dynamic array formulas.
[/RANGE
Click to expand...
Cell Formulas
RangeFormula
Dynamic array formulas.
This is great, exactly in line with the needs. Thanks this is great.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Calculating Week No of the Month
Replies
3
Views
349
jajatidev
J
J
  • Question Question
Date Calculator
Replies
3
Views
81
Joe4
Joe4
Deepakchoudhary
  • Solved
Weekday marking based on a date in during current week
Replies
2
Views
292
Deepakchoudhary
Deepakchoudhary
Z
  • Question Question
Dynamic Working and Week days remaining until end of month from a specific date in the month
Replies
3
Views
637
Peter_SSs
Peter_SSs
T
  • Solved
Automatically generate dates for specific days of the week on any given month
Replies
6
Views
294
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,816
Messages
6,181,143
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