Return latest date for year

[thanksamillion101]

Hello and thank you in advance! I have a formula to return the latest date for each month, however it will not work for returning the latest date for a certain year. The information is pulled from Sheet 2 into Sheet 1. Please help


I added month name abbrev in column T and could also add a column U to specify the year, but would rather use a formula to determine the latest date for specific months and year using only column S dates. Hope I explained correctly.


The formula I have currently will work for 2019 but not 2020 or 2021.

IF($A2="","",IFERROR(1/(1/MAX(IF(($P:$P=$A2)*($Q:$Q=$B2)*($T:$T=D$1)*($R:$R=$O$2),$S:$S,""))),"")) Returns latest date for the month

IF($A2="","",IFERROR(1/(1/(IF((P:P=$A2)*(R:R=$O$2)*(Q:Q=$B2),MAX((YEAR(S:S)=$K$1)*S:S)))),"Err")) Returns latest date for the year (only works for 2019)

Name

State

Jan

Feb

Mar

Apr

May

Jun

2019

2020

2021

Sheet 2

Customer

Co

Source

Dates

Month

Red

T

2/3/21

5/2/20

5/22/19

Err

Err

0101-A

Red

T

0101-A

5/22/2019​

May

Blue

V

3/1/21

Green

C

0101-A

6/1/2021​

Jun

Red

T

2/3/21

5/2/20

Blue

V

0101-A

3/1/2021​

Mar

Red

T

2/3/21

5/2/20

Red

T

0101-A

5/2/2020​

May

Green

C

6/1/21

Red

T

0101-A

2/3/2021​

Feb

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

 

[thanksamillion101]

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

Name

State

Jan

Feb

Mar

Apr

May

Jun

2019

2020

2021

Sheet2

Customer

Co

Source

Dates

Month

2

Red

T

2/3/21

5/2/20

5/22/19

Err

0101-A

Red

T

0101-A

5/22/2019​

May

3

Blue

V

3/1/21

Green

C

0101-A

6/1/2021​

Jun

4

Red

T

2/3/21

5/2/20

Blue

V

0101-A

3/1/2021​

Mar

5

Red

T

2/3/21

5/2/20

Red

T

0101-A

5/2/2020​

May

6

Green

C

6/1/21

Red

T

0101-A

2/3/2021​

Feb