Latest month compared with latest date

[thanksamillion101]

Hello, Thank you in advance!!

I have a spreadsheet that I need a formula to find latest month in the first range (abbreviated month) and calculate if the latest date in another range (date by month) is greater or equal to the first range (latest abbrev month). The formula will help determine if an appointment has been missed and return "Name" if latest date is not in this year. The first range indicates scheduled months and the second, indicates the latest appointment date.

A

C

D

E

F

G

H

I

Jul

K

L

M

N

O

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

1​

Name

Latest Date

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

2​

P.Thomas

2/1/21

Feb

Jul

2/1/21

3​

K.Lymon

6/8/22

Jun

6/8/22

4​

S.Kay

4/3/22

Apr

5/3/22

5​

J.Ferri

6/1/21

Mar

Jun

Sep

Dec

3/1/22

6/1/22

9/3/21

12/15/21

6​

P.Lena

6/8/21

Jun

6/8/21

7​

K.Henry

8/3/22

Aug

9/3/22

I have the first portion of the formula to determine the latest month in the first range. (Formula is in AC2) I will be generating list for September.
LOOKUP(2,1/(D2:O2<>""),D2:O2) If(Max(Q2:AB2?????

cell AC2 with formula will return "P.Thomas"because Feb and Jul appt has been missed.
cell AC3 with formula will return " " because June appt was not missed.
cell AC4 with formula will return "" because although Apr appt was missed, there was an appt in May.
cell AC5 with formula will return "J.Ferri" because the next appt for this year is September's appt. and Dec not yet due.

Hope I have explained the formula I am needing and thank you for your help!

 

[KRice]

This may need additional work. Could you explain why AC5 should return a name when they haven't missed an appointment? (I think the answer hinges on whether column C (latest date) is used or the dates in range Q:AB...probably a typo)

MrExcel_20220826.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Name		Latest Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	P.Thomas		2/1/2021		Feb					Jul								2/1/2021											P.Thomas
3	K.Lymon		6/8/2022						Jun													6/8/2022
4	S.Kay		4/3/2022				Apr														5/3/2022
5	J.Ferri		6/1/2021			Mar			Jun			Sep			Dec				3/1/2022		6/1/2022				9/3/2021			12/15/2021
6	P.Lena		6/8/2021						Jun													6/8/2021							P.Lena
7	K.Henry		8/3/2022								Aug														9/3/2022
Sheet8

Cell Formulas
Range	Formula
AC2:AC7	AC2	=IFERROR(IF(MAX(Q2:AB2)>=DATE(YEAR(TODAY()),AGGREGATE(14,6,(COLUMN($D$1:$O$1)-COLUMN($D1)+1)/(ISTEXT(D2:O2)*((COLUMN($D$1:$O$1)-COLUMN($D1)+1)<MONTH(TODAY()))),1),1),"",A2),"")

 

[thanksamillion101]

Could you explain why AC5 should return a name when they haven't missed an appointment?

Because I am generating the list for upcoming appointments in Sept in which they are due

 

[KRice]

I'm not sure the point of my question was understood. The row for J. Ferri shows the "Latest Date" as 6/1/2021, but there is no 6/1/2021 in the log of actual appointment dates (there is 6/1/2022). So it would appear that J. Ferri has not missed any appointments so far this year (in 2022). And according to the objective stated in post #1:

The formula will help determine if an appointment has been missed and return "Name" if latest date is not in this year.

...I don't understand why a "Name" should be displayed.

But your last post suggests the purpose of the formula is not to determine if the last scheduled appointment has been satisfied, but whether the individual has any upcoming appointments. So I am confused about the objective...could you clarify, please? Do you want to know both?...perhaps whether the last scheduled appointment has been satisfied (if not then display name) OR if the individual has any upcoming appointments for the remainder of this year then also display their name? If so, then regarding this "look ahead" feature, how many months ahead is the threshold for showing a name?

 

[thanksamillion101]

I'm not sure the point of my question was understood. The row for J. Ferri shows the "Latest Date" as 6/1/2021, but there is no 6/1/2021 in the log of actual appointment dates (there is 6/1/2022). So it would appear that J. Ferri has not missed any appointments so far this year (in 2022). And according to the objective stated in post #1:

...I don't understand why a "Name" should be displayed.

But your last post suggests the purpose of the formula is not to determine if the last scheduled appointment has been satisfied, but whether the individual has any upcoming appointments. So I am confused about the objective...could you clarify, please? Do you want to know both?...perhaps whether the last scheduled appointment has been satisfied (if not then display name) OR if the individual has any upcoming appointments for the remainder of this year then also display their name? If so, then regarding this "look ahead" feature, how many months ahead is the threshold for showing a name?

"cell AC5 with formula will return "J.Ferri" because the next appt for this year is September's appt. "

I have the first portion of the formula to determine the latest month in the first range. (Formula is in AC2) I will be generating list for September

Column C, Latest Date does have typos, J.Ferri should be 6/1/22 and K.Henry should be 9/3/22.

 

[thanksamillion101]

I'm not sure the point of my question was understood. The row for J. Ferri shows the "Latest Date" as 6/1/2021, but there is no 6/1/2021 in the log of actual appointment dates (there is 6/1/2022). So it would appear that J. Ferri has not missed any appointments so far this year (in 2022). And according to the objective stated in post #1:

...I don't understand why a "Name" should be displayed.

But your last post suggests the purpose of the formula is not to determine if the last scheduled appointment has been satisfied, but whether the individual has any upcoming appointments. So I am confused about the objective...could you clarify, please? Do you want to know both?...perhaps whether the last scheduled appointment has been satisfied (if not then display name) OR if the individual has any upcoming appointments for the remainder of this year then also display their name? If so, then regarding this "look ahead" feature, how many months ahead is the threshold for showing a name?

I apologize... to answer your question, yes, both. Although, an appt has not been missed, there is an upcoming appointment for the specified month, which I can specify in AC1, which I generate each month.

 

[KRice]

I have the first portion of the formula to determine the latest month in the first range. (Formula is in AC2)

Is this correct? Do you mean you have a formula in C2 (and down) for finding the latest actual appointment date? AC2 (and down) seems to be where the names (or blanks) should appear.
There was another error in column C (S. Kay at 5/3/2022 rather than 4/3/2022)...I've used a formula to extract the latest actual appointment date in column C. Then the formula in column AC has been revised to display a name if either of the following conditions is true:
1. The individual missed their latest scheduled appointment (scheduled in the cols D:O block).
2. The individual has an upcoming appointment (scheduled in the cols Q:AB block) for the month following the current (today's) month.
A variation of the AC formula is shown in col. AD, except it allows a user input to define "today" in cell AD1 (I used this for some debugging). Have a look and let me know if this is closer to the desired behavior.

MrExcel_20220826.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	Name		Latest Actual Appt Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		12/15/2021	<-- "today"
2	P.Thomas		2/1/2021		Feb													2/1/2021											P.Thomas
3	K.Lymon		6/8/2022						Jun													6/8/2022
4	S.Kay		5/3/2022				Apr														5/3/2022
5	J.Ferri		6/1/2022			Mar			Jun			Sep			Dec				3/1/2022		6/1/2022				9/3/2021			12/15/2021	J.Ferri
6	P.Lena		6/8/2021						Jun													6/8/2021							P.Lena
7	K.Henry		9/3/2022								Aug														9/3/2022
8	a		10/1/2022									Sep														10/1/2022			a
9	b		4/15/2022	Jan	Feb								Oct							4/15/2022										b
10	c		4/15/2022		Feb									Nov						4/15/2022
11	d		5/15/2021			Mar									Dec						5/15/2021								d
Sheet8

Cell Formulas
Range	Formula
AC2:AC11	AC2	=IFERROR(IF(INDEX(D2:O2,,IF(MONTH(TODAY())=12,1,MONTH(TODAY())+1))<>"",A2,IF(C2>=DATE(YEAR(TODAY()),AGGREGATE(14,6,(COLUMN($D:$O)-COLUMN($D:$D)+1)/(ISTEXT(D2:O2)*((COLUMN($D:$O)-COLUMN($D:$D)+1)<MONTH(TODAY()))),1),1),"",A2)),"")
AD2:AD11	AD2	=IFERROR(IF(INDEX(D2:O2,,IF(MONTH($AD$1)=12,1,MONTH($AD$1)+1))<>"",A2,IF(C2>=DATE(YEAR($AD$1),AGGREGATE(14,6,(COLUMN($D:$O)-COLUMN($D:$D)+1)/(ISTEXT(D2:O2)*((COLUMN($D:$O)-COLUMN($D:$D)+1)<MONTH($AD$1))),1),1),"",A2)),"")
C2:C11	C2	=MAX(Q2:AB2)