SOLVED

New Contributor

# NEED HELP with tricky formula

I am using the latest version of Office 365 (Excel) on a Windows Machine

I am looking for help with a couple of formulas.  This example is tracking compliance with a Medical form that is good for three years.  I need to track if an app has been filled out or not, and its Status. i.e., No Start Date, Active, time to Renew, or Expired.

Here is an explanation of the Columns and the formula I do have.  I cannot figure out how to get Column E & F to work properly.

• Column C is the App Date
• Column D is the App Exp Date. Formula in that cell is: =IF(C132="","No Start Date",DATE(YEAR(C132)+3,MONTH(C132),DAY(C132)))
• Column E Need a formula that will return "No Start Date" if Column D reads "No Start Date, or calculate the Days left in the 3yr period before the application expires.
• Column F Need a formula that returns text as follows:

a)  if the Column E reads "No Start Date", then Column F should read "No Start Date" OR

b)  if Column E is more than "0" then Column F, should read "Active"; OR

c)  if the Column E is <=60 but >=1, Column F should read "RENEW NOW"; OR

d)  if it reads "0" or a negative number, Column F should read "EXPIRED"';

Column C         Column D           Column E            Column F

App Date          Exp Date             Days Left              STATUS

 6/29/2018 6/29/2021 39 RENEW NOW 3/27/2019 3/27/2022 310 ACTIVE 1/28/2020 1/28/2023 617 ACTIVE 10/23/2016 10/23/2019 -576 EXPIRED 9/28/2017 9/28/2020 -235 EXPIRED No Start Date No Start Date No Start Date

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

# Re: NEED HELP with tricky formula

In E132:

=IF(C132="","No Start Date",D132-TODAY())

Format E132 as General.

In F132:

=IFS(C132="","No Start Date",E132>60,"Active",E132>0,"Renew Now",TRUE,"Expired")

Select E132 and F132, then fill down.

# Re: NEED HELP with tricky formula

Thank you so much for your help. This works great.
David