Return value of the last week of the month based on the year and month

stevekho · Sep 14, 2013

Hello,

I have a problem and i dont really know how to fix it. I have a column with some dates all distanced by one week -

Date	Nothing	Number
7 jan 2001		50
14 Jan 2001		65
21 Jan 2001		70
28 Jan 2001		68
4 Feb 2002		70
11 Feb 2002		42
18 Feb 2002		46
25 Feb 2002		29

I would like a formula that based on the month and year will return the last value it can find for this month and year - So if we have Jan & 2001 it will return 68 (last week), Feb & 2001, it will return 29 (last week).
The month could sometime be 4 or 5 weeks long so it is really the last value of the month.

Thanks so much for your help

Oeldere · Sep 14, 2013

Maybe like this?

Excel Workbook

A

B

C

D

E

F

G

1

Date

Nothing

Number

year

month

combine

count

2

7-jan-01

50

2001

01

200101

1

3

14-jan-01

65

2001

01

200101

2

4

21-jan-01

70

2001

01

200101

3

5

28-jan-01

68

2001

01

200101

4

6

4-feb-02

70

2002

02

200202

1

7

11-feb-02

42

2002

02

200202

2

8

18-feb-02

46

2002

02

200202

3

9

25-feb-02

29

2002

02

200202

4

Blad2

Cell Formulas
Range	Formula
D2	=YEAR(A2)
D3	=YEAR(A3)
D4	=YEAR(A4)
D5	=YEAR(A5)
D6	=YEAR(A6)
D7	=YEAR(A7)
D8	=YEAR(A8)
D9	=YEAR(A9)
E2	=MONTH(A2)
E3	=MONTH(A3)
E4	=MONTH(A4)
E5	=MONTH(A5)
E6	=MONTH(A6)
E7	=MONTH(A7)
E8	=MONTH(A8)
E9	=MONTH(A9)
F2	=D2&TEXT(E2,"00")
F3	=D3&TEXT(E3,"00")
F4	=D4&TEXT(E4,"00")
F5	=D5&TEXT(E5,"00")
F6	=D6&TEXT(E6,"00")
F7	=D7&TEXT(E7,"00")
F8	=D8&TEXT(E8,"00")
F9	=D9&TEXT(E9,"00")
G2	=COUNTIF($F$2:$F2,F2)
G3	=COUNTIF($F$2:$F3,F3)
G4	=COUNTIF($F$2:$F4,F4)
G5	=COUNTIF($F$2:$F5,F5)
G6	=COUNTIF($F$2:$F6,F6)
G7	=COUNTIF($F$2:$F7,F7)
G8	=COUNTIF($F$2:$F8,F8)
G9	=COUNTIF($F$2:$F9,F9)

Excel Workbook

K

L

M

19

20

Som van Number

Kolomlabels

21

Rijlabels

4

Eindtotaal

22

200101

68

23

200202

29

24

Eindtotaal

97

Blad2

AlphaFrog · Sep 14, 2013

Try this where D1 and E1 have the Month\Year values.

=INDEX(C:C,MATCH(EOMONTH(DATEVALUE(D1&" 1, "&E1),0),A:A,1))

jeffreybrown · Sep 14, 2013

Cross post

Oeldere · Sep 14, 2013

Another approach.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

1

Date

Nothing

Number

year

month

combine

count

result

4

5

2

7-jan-01

50

2001

01

200101

1

200101

68

0

3

14-jan-01

65

2001

01

200101

2

200202

29

0

4

21-jan-01

70

2001

01

200101

3

200203

13

0

5

28-jan-01

68

2001

01

200101

4

200204

18

17

18

6

4-feb-02

70

2002

02

200202

1

200205

22

0

7

11-feb-02

42

2002

02

200202

2

200206

26

0

8

18-feb-02

46

2002

02

200202

3

200207

31

30

31

9

25-feb-02

29

2002

02

200202

4

200208

35

0

10

4-mrt-02

29

2002

03

200203

1

200209

40

39

40

11

11-mrt-02

11

2002

03

200203

2

200210

44

0

12

18-mrt-02

12

2002

03

200203

3

13

25-mrt-02

13

2002

03

200203

4

14

1-apr-02

14

2002

04

200204

1

15

8-apr-02

15

2002

04

200204

2

16

15-apr-02

16

2002

04

200204

3

17

22-apr-02

17

2002

04

200204

4

18

29-apr-02

18

2002

04

200204

5

19

6-mei-02

19

2002

05

200205

1

20

13-mei-02

20

2002

05

200205

2

21

20-mei-02

21

2002

05

200205

3

22

27-mei-02

22

2002

05

200205

4

Blad2

Cell Formulas
Range	Formula
D2	=YEAR(A2)
D3	=YEAR(A3)
D4	=YEAR(A4)
D5	=YEAR(A5)
D6	=YEAR(A6)
D7	=YEAR(A7)
D8	=YEAR(A8)
D9	=YEAR(A9)
D10	=YEAR(A10)
D11	=YEAR(A11)
D12	=YEAR(A12)
D13	=YEAR(A13)
D14	=YEAR(A14)
D15	=YEAR(A15)
D16	=YEAR(A16)
D17	=YEAR(A17)
D18	=YEAR(A18)
D19	=YEAR(A19)
D20	=YEAR(A20)
D21	=YEAR(A21)
D22	=YEAR(A22)
E2	=MONTH(A2)
E3	=MONTH(A3)
E4	=MONTH(A4)
E5	=MONTH(A5)
E6	=MONTH(A6)
E7	=MONTH(A7)
E8	=MONTH(A8)
E9	=MONTH(A9)
E10	=MONTH(A10)
E11	=MONTH(A11)
E12	=MONTH(A12)
E13	=MONTH(A13)
E14	=MONTH(A14)
E15	=MONTH(A15)
E16	=MONTH(A16)
E17	=MONTH(A17)
E18	=MONTH(A18)
E19	=MONTH(A19)
E20	=MONTH(A20)
E21	=MONTH(A21)
E22	=MONTH(A22)
F2	=D2&TEXT(E2,"00")
F3	=D3&TEXT(E3,"00")
F4	=D4&TEXT(E4,"00")
F5	=D5&TEXT(E5,"00")
F6	=D6&TEXT(E6,"00")
F7	=D7&TEXT(E7,"00")
F8	=D8&TEXT(E8,"00")
F9	=D9&TEXT(E9,"00")
F10	=D10&TEXT(E10,"00")
F11	=D11&TEXT(E11,"00")
F12	=D12&TEXT(E12,"00")
F13	=D13&TEXT(E13,"00")
F14	=D14&TEXT(E14,"00")
F15	=D15&TEXT(E15,"00")
F16	=D16&TEXT(E16,"00")
F17	=D17&TEXT(E17,"00")
F18	=D18&TEXT(E18,"00")
F19	=D19&TEXT(E19,"00")
F20	=D20&TEXT(E20,"00")
F21	=D21&TEXT(E21,"00")
F22	=D22&TEXT(E22,"00")
G2	=COUNTIF($F$2:$F2,F2)
G3	=COUNTIF($F$2:$F3,F3)
G4	=COUNTIF($F$2:$F4,F4)
G5	=COUNTIF($F$2:$F5,F5)
G6	=COUNTIF($F$2:$F6,F6)
G7	=COUNTIF($F$2:$F7,F7)
G8	=COUNTIF($F$2:$F8,F8)
G9	=COUNTIF($F$2:$F9,F9)
G10	=COUNTIF($F$2:$F10,F10)
G11	=COUNTIF($F$2:$F11,F11)
G12	=COUNTIF($F$2:$F12,F12)
G13	=COUNTIF($F$2:$F13,F13)
G14	=COUNTIF($F$2:$F14,F14)
G15	=COUNTIF($F$2:$F15,F15)
G16	=COUNTIF($F$2:$F16,F16)
G17	=COUNTIF($F$2:$F17,F17)
G18	=COUNTIF($F$2:$F18,F18)
G19	=COUNTIF($F$2:$F19,F19)
G20	=COUNTIF($F$2:$F20,F20)
G21	=COUNTIF($F$2:$F21,F21)
G22	=COUNTIF($F$2:$F22,F22)
I2	=IF(K2=0,J2,K2)
I3	=IF(K3=0,J3,K3)
I4	=IF(K4=0,J4,K4)
I5	=IF(K5=0,J5,K5)
I6	=IF(K6=0,J6,K6)
I7	=IF(K7=0,J7,K7)
I8	=IF(K8=0,J8,K8)
I9	=IF(K9=0,J9,K9)
I10	=IF(K10=0,J10,K10)
I11	=IF(K11=0,J11,K11)
J2	=SUMPRODUCT(($F$2:$F$44=$H2)(($G$2:$G$44)=J$1)($C$2:$C$44))
J3	=SUMPRODUCT(($F$2:$F$44=$H3)(($G$2:$G$44)=J$1)($C$2:$C$44))
J4	=SUMPRODUCT(($F$2:$F$44=$H4)(($G$2:$G$44)=J$1)($C$2:$C$44))
J5	=SUMPRODUCT(($F$2:$F$44=$H5)(($G$2:$G$44)=J$1)($C$2:$C$44))
J6	=SUMPRODUCT(($F$2:$F$44=$H6)(($G$2:$G$44)=J$1)($C$2:$C$44))
J7	=SUMPRODUCT(($F$2:$F$44=$H7)(($G$2:$G$44)=J$1)($C$2:$C$44))
J8	=SUMPRODUCT(($F$2:$F$44=$H8)(($G$2:$G$44)=J$1)($C$2:$C$44))
J9	=SUMPRODUCT(($F$2:$F$44=$H9)(($G$2:$G$44)=J$1)($C$2:$C$44))
J10	=SUMPRODUCT(($F$2:$F$44=$H10)(($G$2:$G$44)=J$1)($C$2:$C$44))
J11	=SUMPRODUCT(($F$2:$F$44=$H11)(($G$2:$G$44)=J$1)($C$2:$C$44))
K2	=SUMPRODUCT(($F$2:$F$44=$H2)(($G$2:$G$44)=K$1)($C$2:$C$44))
K3	=SUMPRODUCT(($F$2:$F$44=$H3)(($G$2:$G$44)=K$1)($C$2:$C$44))
K4	=SUMPRODUCT(($F$2:$F$44=$H4)(($G$2:$G$44)=K$1)($C$2:$C$44))
K5	=SUMPRODUCT(($F$2:$F$44=$H5)(($G$2:$G$44)=K$1)($C$2:$C$44))
K6	=SUMPRODUCT(($F$2:$F$44=$H6)(($G$2:$G$44)=K$1)($C$2:$C$44))
K7	=SUMPRODUCT(($F$2:$F$44=$H7)(($G$2:$G$44)=K$1)($C$2:$C$44))
K8	=SUMPRODUCT(($F$2:$F$44=$H8)(($G$2:$G$44)=K$1)($C$2:$C$44))
K9	=SUMPRODUCT(($F$2:$F$44=$H9)(($G$2:$G$44)=K$1)($C$2:$C$44))
K10	=SUMPRODUCT(($F$2:$F$44=$H10)(($G$2:$G$44)=K$1)($C$2:$C$44))
K11	=SUMPRODUCT(($F$2:$F$44=$H11)(($G$2:$G$44)=K$1)($C$2:$C$44))
C11	=ROW()
C12	=ROW()
C13	=ROW()
C14	=ROW()
C15	=ROW()
C16	=ROW()
C17	=ROW()
C18	=ROW()
C19	=ROW()
C20	=ROW()
C21	=ROW()
C22	=ROW()

stevekho · Sep 14, 2013

jeffreybrown said:
Cross post

Sorry for the cross post guys! I wont do it again

Aladin Akyurek · Sep 14, 2013

C2: Feb
D2: 2002

E2, control+shift+enter, not just enter:

=MAX(IF(TEXT(A2:A9,"mmm")=C2,IF(YEAR(A2:A9)=D2,A2:A9)))

Is this what you are after?

jim may · Sep 14, 2013

Alpha Frog's Excellent Formula can be shortened (I think)... and Without having to use the Helper Columns D & E Tks Alpha Frog

=INDEX(C:C,MATCH(EOMONTH(A2,0),A:A,1))

Aladin Akyurek · Sep 14, 2013

AlphaFrog said:
Try this where D1 and E1 have the Month\Year values.

=INDEX(C:C,MATCH(EOMONTH(DATEVALUE(D1&" 1, "&E1),0),A:A,1))

jim may said:
Alpha Frog's Excellent Formula can be shortened (I think)... and Without having to use the Helper Columns D & E Tks Alpha Frog

=INDEX(C:C,MATCH(EOMONTH(A2,0),A:A,1))

Since A2 is part of the range in column A and the OP is specifying the look up value in two parts (e.g., Feb and 2002), the following shortens a bit of the excellent formula AlphaFrog suggests:

=INDEX(C:C,MATCH(EOMONTH(D1&E1,0),A:A,1))

where D1 = Feb and E1 = 2002, for example. This requires that the dates are in ascending order. (I missed the C-range on iPad, hence that unnecessary array formula.)

markmzz · Sep 14, 2013

Maybe this:

Layout

Date	Nothing	Number		Month/Year	Result
07/jan/01		50		jan/01	68
14/jan/01		65		fev/02	29
21/jan/01		70
28/jan/01		68
04/fev/02		70
11/fev/02		42		Month	Year	Result
18/fev/02		46		Jan	2001	68
25/fev/02		29		Fev	2002	29
*********	********	*********	**	***********	*********	******

PS: in my Excel Fev = Feb

Formulas

Array formula - use Ctrl+Shift+Enter and not only Enter

Code:

F2-> =VLOOKUP(MAX(IF($A$2:$A$9>=E2,IF($A$2:$A$9<=EOMONTH(E2,0),$A$2:$A$9))),$A$2:$C$9,3,0)

G8-> =VLOOKUP(MAX(IF($A$2:$A$9>=1*(E8&F8),IF($A$2:$A$9<=EOMONTH(E8&F8,0),$A$2:$A$9))),$A$2:$C$9,3,0)

Markmzz

Return value of the last week of the month based on the year and month

stevekho

New Member

Excel Facts

Oeldere

Well-known Member

AlphaFrog

MrExcel MVP

jeffreybrown

Well-known Member

Oeldere

Well-known Member

stevekho

New Member

Aladin Akyurek

MrExcel MVP

jim may

Well-known Member

Aladin Akyurek

MrExcel MVP

markmzz

MrExcel MVP

Similar threads

Forum statistics

Share this page

Return value of the last week of the month based on the year and month

New Member

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

New Member

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock