SOLVED

combining # of days and max formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2487993%22%20slang%3D%22en-US%22%3Ecombining%20%23%20of%20days%20and%20max%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2487993%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%2C%3C%2FP%3E%3CP%3EThe%20spreadsheet%20tracks%20user%20tasks%20and%20the%20due%20date.%20I%20calculate%20the%20%23%20of%20days%20using%20the%20DAYS%20function.%26nbsp%3B%20The%20users%20in%20a%20separate%20column%20enter%20their%20completion%20date.%20For%20the%20due%20date%20column%2C%20I%20am%20getting%20large%20negative%20numbers%20because%20the%20DAYS%20formulas%20is%20calculating%20the%20negative%20%23%20of%20days.%26nbsp%3B%20What%20I%20am%20wanting%20to%20do%20is%20calculate%20and%20DAYS%20and%20if%20negative%20show%20zero.%26nbsp%3B%20I%20tried%20to%20combine%20the%20MAX%20and%20DAYS%20but%20not%20getting%20results.%26nbsp%3B%20When%20I%20use%202%20columns%2C%20I%20can%20use%20the%20two%20formulas%20one%20in%20each%20column%20to%20get%20the%20%22expires%20in%22%20as%20either%20zero%20or%20positive.%26nbsp%3B%20Curious%20if%20anyone%20has%20combined%20these%20so%20that%20I%20do%20not%20have%20to%20have%20two%20columns.%26nbsp%3B%20I've%20tried%20but%20have%20not%20been%20successful.%20Please%20provide%20suggestions%20to%20calculate%20the%20number%20of%20days%20till%20task%20expires%20changing%20negatives%20to%20zero.%20If%20another%20formula%20or%20combination%20would%20work%20better%20I%20am%20open%20to%20any%20suggestions.%20Goal%20is%20to%20do%20it%20in%20one%20column%20not%20two.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EIn%202%20columns%20what%20works%20is%3A%3CBR%20%2F%3EDAYS%20formula%20is%20%3DTODAY-J1%3C%2FP%3E%3CP%3EMAX%20formula%20is%20%3CSPAN%3E%3DMAX(%3C%2FSPAN%3E%3CSTRONG%3E0%3C%2FSTRONG%3E%3CSPAN%3E%2CJ1)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2487993%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2488050%22%20slang%3D%22en-US%22%3ERe%3A%20combining%20%23%20of%20days%20and%20max%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2488050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1088739%22%20target%3D%22_blank%22%3E%40Moeaudit%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20365%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20days%2C%20DAYS(TODAY()%2C%20due)%2C%0A%20%20IF(days%26gt%3B0%2C%20days%2C%200)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eor%20legacy%20systems%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20IF(%20DAYS(TODAY()%2Cdue)%26gt%3B0%2C%20DAYS(TODAY()%2Cdue)%2C%200)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Good morning,

The spreadsheet tracks user tasks and the due date. I calculate the # of days using the DAYS function.  The users in a separate column enter their completion date. For the due date column, I am getting large negative numbers because the DAYS formulas is calculating the negative # of days.  What I am wanting to do is calculate and DAYS and if negative show zero.  I tried to combine the MAX and DAYS but not getting results.  When I use 2 columns, I can use the two formulas one in each column to get the "expires in" as either zero or positive.  Curious if anyone has combined these so that I do not have to have two columns.  I've tried but have not been successful. Please provide suggestions to calculate the number of days till task expires changing negatives to zero. If another formula or combination would work better I am open to any suggestions. Goal is to do it in one column not two.


In 2 columns what works is:
DAYS formula is =TODAY-J1

MAX formula is =MAX(0,J1)

2 Replies
best response confirmed by Moeaudit (New Contributor)
Solution

@Moeaudit 

Using 365

= LET(
  days, DAYS(TODAY(), due),
  IF(days>0, days, 0) )

or legacy systems

= IF( DAYS(TODAY(),due)>0, DAYS(TODAY(),due), 0)

 

@Peter Bartholomew Thanks! The LET formula worked.  I appreciate the help.