Split Monthly Budget to Weekly

S

shansakhi

Active Member
Joined
Apr 5, 2008
Messages
276
Office Version
  1. 365
Platform
  1. Windows
Hi Sir/ Madam,
I have Monthly budget which I would like to split to Weekly. Can you please assist me with formula.

Input Data:
MonthRevenue
Jan 50,000,000
Feb 42,000,000
Mar 46,000,000
Apr 60,000,000
May 48,000,000
Jun 45,000,000
Jul 58,000,000
Aug 62,000,000
Sep 43,000,000
Oct 45,000,000
Nov 47,000,000
Dec 58,000,000

Output .. The monthly Revenue needs to be populated in below Weekly Revenue column .....

*Week No is calculated considering 1st Jan is WED. (Formula Used: WEEKNUM(A2,13))

Start of WeekWeek NoWeekly Revenue
1-Jan-201
8-Jan-202
15-Jan-203
22-Jan-204
29-Jan-205
5-Feb-206
12-Feb-207
19-Feb-208
26-Feb-209
4-Mar-2010
11-Mar-2011
18-Mar-2012
25-Mar-2013
1-Apr-2014
8-Apr-2015
15-Apr-2016
22-Apr-2017
29-Apr-2018
6-May-2019
13-May-2020
20-May-2021
27-May-2022
3-Jun-2023
10-Jun-2024
17-Jun-2025
24-Jun-2026
1-Jul-2027
8-Jul-2028
15-Jul-2029
22-Jul-2030
29-Jul-2031
5-Aug-2032
12-Aug-2033
19-Aug-2034
26-Aug-2035
2-Sep-2036
9-Sep-2037
16-Sep-2038
23-Sep-2039
30-Sep-2040
7-Oct-2041
14-Oct-2042
21-Oct-2043
28-Oct-2044
4-Nov-2045
11-Nov-2046
18-Nov-2047
25-Nov-2048
2-Dec-2049
9-Dec-2050
16-Dec-2051
23-Dec-2052
30-Dec-2053


Regards,
Shan
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
OK, let's say that your "Input Data" exists on Sheet1, in the range A1:B13 (where row 1 is the header row),
and your "Output" is on Sheet2, in the range A1:C54.
Place this formula in cell C2 and copy down to cell C54:
=VLOOKUP(TEXT(A2,"mmm"),Sheet1!$A$2:$B$13,2,0)/SUMPRODUCT(--(MONTH($A$2:$A$54)=MONTH(A2)))
 
Upvote 0
Hi Joe4,
Sorry to bother you again. I am trying to use suggested formula in below data using different conditions like combining Station/category/Country/Month/Class for Vlookup but I am not able to. May I request you to kindly assist.
I have different Stations, categories, Countries and Classes.

=VLOOKUP(TEXT(A2,"mmm"),Sheet1!$A$2:$B$13,2,0)/SUMPRODUCT(--(MONTH($A$2:$A$54)=MONTH(A2)))

Input Data:
StationCategoryCountryMonthClassRevenue
LHREUROPEUNITED KINGDOMJANY 1,289,186
LHREUROPEUNITED KINGDOMFEBY 818,761
LHREUROPEUNITED KINGDOMMARY 933,127

Output:
Start of WeekWeek NoStationCategoryCountryMonthClassRevenue
1-Jan-201LHREUROPEUNITED KINGDOMJANY
8-Jan-202LHREUROPEUNITED KINGDOMJANY
15-Jan-203LHREUROPEUNITED KINGDOMJANY
22-Jan-204LHREUROPEUNITED KINGDOMJANY
29-Jan-205LHREUROPEUNITED KINGDOMJANY
5-Feb-206LHREUROPEUNITED KINGDOMJANY
12-Feb-207LHREUROPEUNITED KINGDOMJANY
19-Feb-208LHREUROPEUNITED KINGDOMJANY
26-Feb-209LHREUROPEUNITED KINGDOMJANY
4-Mar-2010LHREUROPEUNITED KINGDOMJANY
11-Mar-2011LHREUROPEUNITED KINGDOMJANY
18-Mar-2012LHREUROPEUNITED KINGDOMJANY
25-Mar-2013LHREUROPEUNITED KINGDOMJANY
1-Apr-2014LHREUROPEUNITED KINGDOMJANY
8-Apr-2015LHREUROPEUNITED KINGDOMJANY
15-Apr-2016LHREUROPEUNITED KINGDOMJANY
22-Apr-2017LHREUROPEUNITED KINGDOMJANY
29-Apr-2018LHREUROPEUNITED KINGDOMJANY
6-May-2019LHREUROPEUNITED KINGDOMJANY
13-May-2020LHREUROPEUNITED KINGDOMJANY
20-May-2021LHREUROPEUNITED KINGDOMJANY
27-May-2022LHREUROPEUNITED KINGDOMJANY
3-Jun-2023LHREUROPEUNITED KINGDOMJANY
10-Jun-2024LHREUROPEUNITED KINGDOMJANY
17-Jun-2025LHREUROPEUNITED KINGDOMJANY
24-Jun-2026LHREUROPEUNITED KINGDOMJANY
1-Jul-2027LHREUROPEUNITED KINGDOMJANY
8-Jul-2028LHREUROPEUNITED KINGDOMJANY
15-Jul-2029LHREUROPEUNITED KINGDOMJANY
22-Jul-2030LHREUROPEUNITED KINGDOMJANY
29-Jul-2031LHREUROPEUNITED KINGDOMJANY
5-Aug-2032LHREUROPEUNITED KINGDOMJANY
12-Aug-2033LHREUROPEUNITED KINGDOMJANY
19-Aug-2034LHREUROPEUNITED KINGDOMJANY
26-Aug-2035LHREUROPEUNITED KINGDOMJANY
2-Sep-2036LHREUROPEUNITED KINGDOMJANY
9-Sep-2037LHREUROPEUNITED KINGDOMJANY
16-Sep-2038LHREUROPEUNITED KINGDOMJANY
23-Sep-2039LHREUROPEUNITED KINGDOMJANY
30-Sep-2040LHREUROPEUNITED KINGDOMJANY
7-Oct-2041LHREUROPEUNITED KINGDOMJANY
14-Oct-2042LHREUROPEUNITED KINGDOMJANY
21-Oct-2043LHREUROPEUNITED KINGDOMJANY
28-Oct-2044LHREUROPEUNITED KINGDOMJANY
4-Nov-2045LHREUROPEUNITED KINGDOMJANY
11-Nov-2046LHREUROPEUNITED KINGDOMJANY
18-Nov-2047LHREUROPEUNITED KINGDOMJANY
25-Nov-2048LHREUROPEUNITED KINGDOMJANY
2-Dec-2049LHREUROPEUNITED KINGDOMJANY
9-Dec-2050LHREUROPEUNITED KINGDOMJANY
16-Dec-2051LHREUROPEUNITED KINGDOMJANY
23-Dec-2052LHREUROPEUNITED KINGDOMJANY
30-Dec-2053LHREUROPEUNITED KINGDOMJANY
1-Jan-201LHREUROPEUNITED KINGDOMFEBY
8-Jan-202LHREUROPEUNITED KINGDOMFEBY
15-Jan-203LHREUROPEUNITED KINGDOMFEBY
22-Jan-204LHREUROPEUNITED KINGDOMFEBY
29-Jan-205LHREUROPEUNITED KINGDOMFEBY
5-Feb-206LHREUROPEUNITED KINGDOMFEBY
12-Feb-207LHREUROPEUNITED KINGDOMFEBY
19-Feb-208LHREUROPEUNITED KINGDOMFEBY
26-Feb-209LHREUROPEUNITED KINGDOMFEBY
4-Mar-2010LHREUROPEUNITED KINGDOMFEBY
11-Mar-2011LHREUROPEUNITED KINGDOMFEBY
18-Mar-2012LHREUROPEUNITED KINGDOMFEBY
25-Mar-2013LHREUROPEUNITED KINGDOMFEBY
1-Apr-2014LHREUROPEUNITED KINGDOMFEBY
8-Apr-2015LHREUROPEUNITED KINGDOMFEBY
15-Apr-2016LHREUROPEUNITED KINGDOMFEBY
22-Apr-2017LHREUROPEUNITED KINGDOMFEBY
29-Apr-2018LHREUROPEUNITED KINGDOMFEBY
6-May-2019LHREUROPEUNITED KINGDOMFEBY
13-May-2020LHREUROPEUNITED KINGDOMFEBY
20-May-2021LHREUROPEUNITED KINGDOMFEBY
27-May-2022LHREUROPEUNITED KINGDOMFEBY
3-Jun-2023LHREUROPEUNITED KINGDOMFEBY
10-Jun-2024LHREUROPEUNITED KINGDOMFEBY
17-Jun-2025LHREUROPEUNITED KINGDOMFEBY
24-Jun-2026LHREUROPEUNITED KINGDOMFEBY
1-Jul-2027LHREUROPEUNITED KINGDOMFEBY
8-Jul-2028LHREUROPEUNITED KINGDOMFEBY
15-Jul-2029LHREUROPEUNITED KINGDOMFEBY
22-Jul-2030LHREUROPEUNITED KINGDOMFEBY
29-Jul-2031LHREUROPEUNITED KINGDOMFEBY
5-Aug-2032LHREUROPEUNITED KINGDOMFEBY
12-Aug-2033LHREUROPEUNITED KINGDOMFEBY
19-Aug-2034LHREUROPEUNITED KINGDOMFEBY
26-Aug-2035LHREUROPEUNITED KINGDOMFEBY
2-Sep-2036LHREUROPEUNITED KINGDOMFEBY
9-Sep-2037LHREUROPEUNITED KINGDOMFEBY
16-Sep-2038LHREUROPEUNITED KINGDOMFEBY
23-Sep-2039LHREUROPEUNITED KINGDOMFEBY
30-Sep-2040LHREUROPEUNITED KINGDOMFEBY
7-Oct-2041LHREUROPEUNITED KINGDOMFEBY
14-Oct-2042LHREUROPEUNITED KINGDOMFEBY
21-Oct-2043LHREUROPEUNITED KINGDOMFEBY
28-Oct-2044LHREUROPEUNITED KINGDOMFEBY
4-Nov-2045LHREUROPEUNITED KINGDOMFEBY
11-Nov-2046LHREUROPEUNITED KINGDOMFEBY
18-Nov-2047LHREUROPEUNITED KINGDOMFEBY
25-Nov-2048LHREUROPEUNITED KINGDOMFEBY
2-Dec-2049LHREUROPEUNITED KINGDOMFEBY
9-Dec-2050LHREUROPEUNITED KINGDOMFEBY
16-Dec-2051LHREUROPEUNITED KINGDOMFEBY
23-Dec-2052LHREUROPEUNITED KINGDOMFEBY
30-Dec-2053LHREUROPEUNITED KINGDOMFEBY
1-Jan-201LHREUROPEUNITED KINGDOMMARY
8-Jan-202LHREUROPEUNITED KINGDOMMARY
15-Jan-203LHREUROPEUNITED KINGDOMMARY
22-Jan-204LHREUROPEUNITED KINGDOMMARY
29-Jan-205LHREUROPEUNITED KINGDOMMARY
5-Feb-206LHREUROPEUNITED KINGDOMMARY
12-Feb-207LHREUROPEUNITED KINGDOMMARY
19-Feb-208LHREUROPEUNITED KINGDOMMARY
26-Feb-209LHREUROPEUNITED KINGDOMMARY
4-Mar-2010LHREUROPEUNITED KINGDOMMARY

Regards,
Shan
 
Upvote 0
Try this:

=VLOOKUP(TEXT(A2,"mmm"),Sheet1!$D$2:$F$13,3,0)/SUMPRODUCT(--(MONTH($A$2:$A$54)=MONTH(A2)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
lookup return from range of dates
Replies
4
Views
87
Peter_SSs
Peter_SSs
A
  • Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
239
amolvijay
A
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
15
Views
1K
Wishmaster40
W
vinzent
  • Question
Formula to get the 4th workday before end of month
Replies
8
Views
121
Tetra201
Tetra201
V
  • Solved
Week Number
Replies
2
Views
99
vmjan02
V

Forum statistics

Threads
1,214,553
Messages
6,120,179
Members
448,948
Latest member
spamiki

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