Latest month compared with latest date

thanksamillion101

New Member
Joined
Jul 8, 2020
Messages
41
Office Version
  1. 2010
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.

ACDEFGHIJulKLMNOQRSTUVWXYZAAAB
1​
NameLatest DateJanFebMarAprMayJunJulAugSepOctNovDecJanFebMarAprMayJunJulAugSepOctNovDec
2​
P.Thomas2/1/21FebJul2/1/21
3​
K.Lymon6/8/22Jun6/8/22
4​
S.Kay4/3/22Apr5/3/22
5​
J.Ferri6/1/21MarJunSepDec3/1/226/1/229/3/2112/15/21
6​
P.Lena6/8/21Jun6/8/21
7​
K.Henry8/3/22Aug9/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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1NameLatest DateJanFebMarAprMayJunJulAugSepOctNovDecJanFebMarAprMayJunJulAugSepOctNovDec
2P.Thomas2/1/2021FebJul2/1/2021P.Thomas
3K.Lymon6/8/2022Jun6/8/2022 
4S.Kay4/3/2022Apr5/3/2022 
5J.Ferri6/1/2021MarJunSepDec3/1/20226/1/20229/3/202112/15/2021 
6P.Lena6/8/2021Jun6/8/2021P.Lena
7K.Henry8/3/2022Aug9/3/2022 
Sheet8
Cell Formulas
RangeFormula
AC2:AC7AC2=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),"")
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1NameLatest Actual Appt DateJanFebMarAprMayJunJulAugSepOctNovDecJanFebMarAprMayJunJulAugSepOctNovDec12/15/2021<-- "today"
2P.Thomas2/1/2021Feb2/1/2021P.Thomas 
3K.Lymon6/8/2022Jun6/8/2022  
4S.Kay5/3/2022Apr5/3/2022  
5J.Ferri6/1/2022MarJunSepDec3/1/20226/1/20229/3/202112/15/2021J.Ferri 
6P.Lena6/8/2021Jun6/8/2021P.Lena 
7K.Henry9/3/2022Aug9/3/2022  
8a10/1/2022Sep10/1/2022a 
9b4/15/2022JanFebOct4/15/2022 b
10c4/15/2022FebNov4/15/2022  
11d5/15/2021MarDec5/15/2021d 
Sheet8
Cell Formulas
RangeFormula
AC2:AC11AC2=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:AD11AD2=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:C11C2=MAX(Q2:AB2)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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