SOLVED

NEED HELP with tricky formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2376266%22%20slang%3D%22en-US%22%3ENEED%20HELP%20with%20tricky%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376266%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20the%20latest%20version%20of%20Office%20365%20(Excel)%20on%20a%20Windows%20Machine%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20help%20with%20a%20couple%20of%20formulas.%26nbsp%3B%20This%20example%20is%20tracking%20compliance%20with%20a%20Medical%20form%20that%20is%20good%20for%20three%20years.%26nbsp%3B%20I%20need%20to%20track%20if%20an%20app%20has%20been%20filled%20out%20or%20not%2C%20and%20its%20Status.%20i.e.%2C%20No%20Start%20Date%2C%20Active%2C%20time%20to%20Renew%2C%20or%20Expired.%3C%2FP%3E%3CP%3EHere%20is%20an%20explanation%20of%20the%20Columns%20and%20the%20formula%20I%20do%20have.%26nbsp%3B%20I%20cannot%20figure%20out%20how%20to%20get%20Column%20E%20%26amp%3B%20F%20to%20work%20properly.%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSTRONG%3EColumn%20C%3C%2FSTRONG%3E%20is%20the%20App%20Date%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EColumn%20D%3C%2FSTRONG%3E%20is%20the%20App%20Exp%20Date.%20Formula%20in%20that%20cell%20is%3A%20%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3E%3DIF(C132%3D%22%22%2C%22No%20Start%20Date%22%2CDATE(YEAR(C132)%2B3%2CMONTH(C132)%2CDAY(C132)))%3C%2FEM%3E%3C%2FFONT%3E%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EColumn%20E%3C%2FSTRONG%3E%26nbsp%3BNeed%20a%20formula%20that%20will%20return%20%22No%20Start%20Date%22%20if%20Column%20D%20reads%20%22No%20Start%20Date%2C%20or%20calculate%20the%20Days%20left%20in%20the%203yr%20period%20before%20the%20application%20expires.%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EColumn%20F%3C%2FSTRONG%3E%26nbsp%3BNeed%20a%20formula%20that%20returns%20text%20as%20follows%3A%3C%2FLI%3E%3C%2FUL%3E%3CP%3Ea)%26nbsp%3B%20if%20the%20Column%20E%20reads%20%22No%20Start%20Date%22%2C%20then%20Column%20F%20should%20read%20%22No%20Start%20Date%22%20%3CFONT%20color%3D%22%230000FF%22%3EOR%3C%2FFONT%3E%3C%2FP%3E%3CP%3Eb)%26nbsp%3B%20if%20Column%20E%20is%20more%20than%20%220%22%20then%20Column%20F%2C%20should%20read%20%22Active%22%3B%20%3CFONT%20color%3D%22%230000FF%22%3EOR%3C%2FFONT%3E%3C%2FP%3E%3CP%3Ec)%26nbsp%3B%20if%20the%20Column%20E%20is%20%26lt%3B%3D60%20but%20%26gt%3B%3D1%2C%20Column%20F%20should%20read%20%22RENEW%20NOW%22%3B%20%3CFONT%20color%3D%22%230000FF%22%3EOR%3C%2FFONT%3E%3C%2FP%3E%3CP%3Ed)%26nbsp%3B%20if%20it%20reads%20%220%22%20or%20a%20negative%20number%2C%20Column%20F%20should%20read%20%22EXPIRED%22'%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BColumn%20C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BColumn%20D%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BColumn%20E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Column%20F%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BApp%20Date%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Exp%20Date%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BDays%20Left%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20STATUS%3C%2FP%3E%3CTABLE%20width%3D%22458px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22104.4px%22%20height%3D%2229px%22%3E6%2F29%2F2018%3C%2FTD%3E%3CTD%20width%3D%22110px%22%20height%3D%2229px%22%3E6%2F29%2F2021%3C%2FTD%3E%3CTD%20width%3D%22114px%22%20height%3D%2229px%22%3E39%3C%2FTD%3E%3CTD%20width%3D%22128.8px%22%20height%3D%2229px%22%3ERENEW%20NOW%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22104.4px%22%20height%3D%2229px%22%3E3%2F27%2F2019%3C%2FTD%3E%3CTD%20width%3D%22110px%22%20height%3D%2229px%22%3E3%2F27%2F2022%3C%2FTD%3E%3CTD%20width%3D%22114px%22%20height%3D%2229px%22%3E310%3C%2FTD%3E%3CTD%20width%3D%22128.8px%22%20height%3D%2229px%22%3EACTIVE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22104.4px%22%20height%3D%2229px%22%3E1%2F28%2F2020%3C%2FTD%3E%3CTD%20width%3D%22110px%22%20height%3D%2229px%22%3E1%2F28%2F2023%3C%2FTD%3E%3CTD%20width%3D%22114px%22%20height%3D%2229px%22%3E617%3C%2FTD%3E%3CTD%20width%3D%22128.8px%22%20height%3D%2229px%22%3EACTIVE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22104.4px%22%20height%3D%2229px%22%3E10%2F23%2F2016%3C%2FTD%3E%3CTD%20width%3D%22110px%22%20height%3D%2229px%22%3E10%2F23%2F2019%3C%2FTD%3E%3CTD%20width%3D%22114px%22%20height%3D%2229px%22%3E-576%3C%2FTD%3E%3CTD%20width%3D%22128.8px%22%20height%3D%2229px%22%3EEXPIRED%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22104.4px%22%20height%3D%2229px%22%3E9%2F28%2F2017%3C%2FTD%3E%3CTD%20width%3D%22110px%22%20height%3D%2229px%22%3E9%2F28%2F2020%3C%2FTD%3E%3CTD%20width%3D%22114px%22%20height%3D%2229px%22%3E-235%3C%2FTD%3E%3CTD%20width%3D%22128.8px%22%20height%3D%2229px%22%3EEXPIRED%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22104.4px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22110px%22%20height%3D%2229px%22%3ENo%20Start%20Date%3C%2FTD%3E%3CTD%20width%3D%22114px%22%20height%3D%2229px%22%3ENo%20Start%20Date%3C%2FTD%3E%3CTD%20width%3D%22128.8px%22%20height%3D%2229px%22%3ENo%20Start%20Date%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EThanks%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2376266%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376294%22%20slang%3D%22en-US%22%3ERe%3A%20NEED%20HELP%20with%20tricky%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376294%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1060123%22%20target%3D%22_blank%22%3E%40Starrman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20E132%3A%3C%2FP%3E%0A%3CP%3E%3DIF(C132%3D%22%22%2C%22No%20Start%20Date%22%2CD132-TODAY())%3C%2FP%3E%0A%3CP%3EFormat%20E132%20as%20General.%3C%2FP%3E%0A%3CP%3EIn%20F132%3A%3C%2FP%3E%0A%3CP%3E%3DIFS(C132%3D%22%22%2C%22No%20Start%20Date%22%2CE132%26gt%3B60%2C%22Active%22%2CE132%26gt%3B0%2C%22Renew%20Now%22%2CTRUE%2C%22Expired%22)%3C%2FP%3E%0A%3CP%3ESelect%20E132%20and%20F132%2C%20then%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376506%22%20slang%3D%22en-US%22%3ERe%3A%20NEED%20HELP%20with%20tricky%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376506%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20for%20your%20help.%20This%20works%20great.%3CBR%20%2F%3EDavid%3C%2FLINGO-BODY%3E
New Contributor

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/20186/29/202139RENEW NOW
3/27/20193/27/2022310ACTIVE
1/28/20201/28/2023617ACTIVE
10/23/201610/23/2019-576EXPIRED
9/28/20179/28/2020-235EXPIRED
 No Start DateNo Start DateNo Start Date

Thanks for your help.

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Starrman 

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.

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