If MONTH =Jan, Feb, etc use that months vale

cgfitzp

New Member
Joined
Oct 11, 2010
Messages
16
Hello All;

I have a sheet used to determine daily volumes based on Total Volume for each month. I divide this total by the number of work days, then multiply by 6 the number of work days each week to get average weekly volume.

I want to determine which month each week is in and use the weekly volume for that month.

I use 53 weeks for the year so have to repeat the process for 12 months and 52 weeks as the first week will always be January.

The test starting in G14 ;
IF C13=JAN use B6,
IF C13= FEB use C6
Using column D for the date would be OK, I put the column C month to hep me visualize the test.

I would repeat the MONTH test for each week G14 to G66


Thanks,
Chet
ABCDEFG
1
2JanFebMarAprMayJun
34,6604,0115,0104,4124,6274,600
4
5272526262626
610369631156101810681062
7
8Monday Start Date12/27/2021
9Week Includes
1001-01-YYYY
1
12DateDayYear WkWk Test
13Dec
12/27/2021​
Monday​
2021_531036<<Default B6
14Jan
1/3/2022​
Monday​
2022_021036
15Jan
1/10/2022​
Monday​
2022_031036
16Jan
1/17/2022​
Monday​
2022_041036
17Jan
1/24/2022​
Monday​
2022_051036
18Jan
1/31/2022​
Monday​
2022_06963
19Feb
2/7/2022​
Monday​
2022_07963
20Feb
2/14/2022​
Monday​
2022_08963
21Feb
2/21/2022​
Monday​
2022_09963
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Going with your explanation (not your manual result)
Book1
BCDEFGHI
2JanFebMarAprMayJun
34,664,0115,014,4124,6274,6
4
5272526262626
610369631156101810681062
7
8Monday Start Date12/27/2021
9Week Includes
1001-01-YYYY
11
12DateDayYear WkWk TestUsing CUsing D
13Dec27/12/2021Monday2021_53103610361036
14Jan3/01/2022Monday2022_02103610361036
15Jan10/01/2022Monday2022_03103610361036
16Jan17/01/2022Monday2022_04103610361036
17Jan24/01/2022Monday2022_05103610361036
18Jan31/01/2022Monday2022_0696310361036
19Feb7/02/2022Monday2022_07963963963
20Feb14/02/2022Monday2022_08963963963
21Feb21/02/2022Monday2022_09963963963
Sheet3
Cell Formulas
RangeFormula
H13:H21H13=IFERROR(INDEX($B$6:$G$6,MATCH(C13,$B$2:$G$2,0)),$B$6)
I13:I21I13=IFERROR(INDEX($B$6:$G$6,MATCH(TEXT(D13,"mmm"),$B$2:$G$2,0)),$B$6)
 
Upvote 0
Thank you for quick response, I've been out of the office, my wife had major surgery, i'll work with your suggestions today, Chet
 
Upvote 0

GraH;​

The version using the dates works perfectly, thank You.

=IFERROR(INDEX($B$6:$G$6,MATCH(C13,$B$2:$G$2,0)),$B$6) always returned the value on "B6"

THanks Again,
Chet
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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