If date is older than 6 months and value is X, then output this

user_3943033 0
Viewed 179

I have two columns in excel, one with a date and one with a rating 'low, medium, high'. I'm trying to write a formula to put in a third column that checks:

  1. If A2 = Low and B2 (date) is older than 12 months from today(), output Overdue
  2. If A2 = Medium and B2 (date) is older than 6 months from today(), output Overdue
  3. If A2 = High and B2 (date) is older than 3 months from today(), output Overdue

If these parameters aren't met, output "Current".

This is what I have so far but I'm well aware its not right :) Could someone please point me in the right direction?

Thanks,

=IF(AND(A2="Low",LOOKUP(DATEDIF(B2,TODAY(),"m"),{0,3,6,12},{"Current", "Current" "Current","Overdue"}),(A2="Medium",LOOKUP(DATEDIF(B2,TODAY(),"m"),{0,3,6,12},{"Current","Current","Overdue","Overdue"}),(A2="High",LOOKUP(DATEDIF(B2,TODAY(),"m"),{0,3,6,12},{"Current","Overdue","Overdue","Overdue"})
return-value excel-formula excel date
2 Answers

If you can use the excel 365 then I’d suggest creating an excel lambda function:

=LAMBDA(level, date, LET(months, IFS(level="Low",12,level="Medium",6,level="High",3,TRUE,0), IF(date < EDATE(Today(), -months),"Overdue", "Current")))

so to break it down, this is a custom function that expects 2 parameters :

  • level is the value/reference that has low/medium/high. this is used to determine the number of months for the threshold - 12/6/3 respectively and 0 for anything else though you could change that as needed.
  • date is the date to check if its beyond the threshold. this is compared against today minus the number of months calculated from the level. if this is before that date then this function will return “Overdue”. otherwise “Current”.

You would create a workbook level named reference with this function as the value. enter image description here

Here I named it IsCurrent. Then you just use that function where you want the output. e.g. enter image description here

user_4935660

What I would suggest is to "outsource" the settings for the overdue months.

This is a good habit, as everyone looking at the table can see those settings and propably adjust them - without going into the formula.

And it is possible to use these settings in another formula :-)

If you use Excel 365 you can make the formula more readable/understandable with the LET-Formula.

enter image description here

=LET( OverdueMonthsForRating, IFNA(INDEX(configOverdue[Overdue months],MATCH([@Rating],configOverdue[Rating],0)),0), OverdueDate,IF(OverdueMonthsForRating>0, EDATE([@Date],OverdueMonthsForRating),TODAY()), IF(OverdueDate<TODAY(),"overdue","current") )

OverdueMonthsForRating is using a classic INDEX/MATCH to retrieve the number of months according to the Rating. In case Rating is not found 0 is returned

OverdueDate calculates - using EDATE - the overdue date based on the ratings date and OverdueMonthsForRating. In case Rating is not found TODAY is returned

Finally this date is evaluated against TODAY and the status is returned.

Classic Excel formula w/o LET:

=IF(EDATE([@Date],IFNA(INDEX(configOverdue[Overdue months],MATCH([@Rating],configOverdue[Rating],0)),TODAY()))<TODAY(),"overdue","current")

user_165784240
Related