combining # of days and max formulas
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)
Using 365
= LET( days, DAYS(TODAY(), due), IF(days>0, days, 0) )or legacy systems
= IF( DAYS(TODAY(),due)>0, DAYS(TODAY(),due), 0)
