Determine if a date is past due or current

[halefamily104]

Hello There!

I have a question. I am trying to find a formula that calculates between two dates, the "next Treatment Due" and today's date and determines if the date in the Next Treatment Due is past due or current. Here's the breakdown. I have three columns. First is named "Last Treatment Completed" (EE95), next is "Next Treatment Due" (EF95) and last is "Treatment Done" (EG95). I need to show in another column if the "Next Treatment Due" is current or past due. Currently, this formula does it:

=IF(ISBLANK(EF95),"",IF(EF95<TODAY(),"Overdue","Not due"))

Here's the problem. in the "Treatment Done" column, a date will be entered so I need to look at that date and change the status to "Current". How can I do this?

Trying this but currently its showing "Overdue" insead of "Current"

=IF(ISBLANK(EF16),"",IF(EF16<TODAY(),"Overdue",OR IF(EG16>TODAY(),"current")))


Thanks in Advance!

 

[Joe4]

Since you already checked for Blank and less than, you shouldn't need to check for the last condition, just return "current", i.e.

=IF(ISBLANK(EF16),"",IF(EF16<TODAY(),"Overdue","current"))

 

[halefamily104]

Since you already checked for Blank and less than, you shouldn't need to check for the last condition, just return "current", i.e.

=IF(ISBLANK(EF16),"",IF(EF16<TODAY(),"Overdue","current"))

Thanks So Much Joe4! The only problem is the formula needs to also look in EG16, not just EF16. If there is a date in EF16, then the formula should return "Current"

 

[RoryA]

A gentle reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

UtterAccess.com

www.utteraccess.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Joe4]

Thanks So Much Joe4! The only problem is the formula needs to also look in EG16, not just EF16. If there is a date in EF16, then the formula should return "Current"

I am not quite clear on all your criteria, as I think may be mixing EF16 and EG16 in your statement above.

Rather than guess, please provide us with a grid of ALL the possible combinations in EF16 and EG16, and what should be returned in each instance.

 

[halefamily104]

I am not quite clear on all your criteria, as I think may be mixing EF16 and EG16 in your statement above.

Rather than guess, please provide us with a grid of ALL the possible combinations in EF16 and EG16, and what should be returned in each instance.

Sure, be happy to! here is what I am currently trying (which isn't working by the way):
=IF(ISBLANK(EF16),"",IF(EF16>TODAY(),"Overdue",IF(EG16>0,"Current","")))

Essentially, look at EF16 first, if the date in that cell is less than today's date, mark it "Overdue", however, if the date in EG is greater than or equal to today's date, return "Current". Essentially, if there is a date in EG, it will make it "Current". If the cell in EG16 is blank, the returned value should be "Not Done".

 

[Joe4]

Not quite what I am looking for. You have left out too many details about possible scenarios.
Here is a grid I have made up showing all the possibilities (maybe - don't know if we need to compare the dates in EF16 and EG16 to each other):

Cell EF16 Value	Call EG16 Value	What to return?
Blank	Blank
Blank	Past Date
Blank	Future Date
Past Date	Blank
Future Date	Blank
Past Date	Past Date
Past Date	Future Date
Future Date	Past Date
Future Date	Future Date

Please indicate what you want to happen in each one (or if a particular combination is not possible).