May 21 2021 09:41 AM
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.
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 |
Thanks for your help.
May 21 2021 09:55 AM
SolutionIn 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.
May 21 2021 11:07 AM
May 21 2021 09:55 AM
SolutionIn 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.