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

cgfitzp

New Member
Joined
Oct 11, 2010
Messages
11
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
768
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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)
 

cgfitzp

New Member
Joined
Oct 11, 2010
Messages
11
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
 

cgfitzp

New Member
Joined
Oct 11, 2010
Messages
11

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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,679
Messages
5,660,263
Members
418,570
Latest member
Aleandre

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
Top