Forum Discussion
Ryan Holdinga
Jun 12, 2018Copper Contributor
Excel adding days to a date
I am creating a spreadsheet to track the date something was issued and the deadline date of 7 calendar days after with an additional cell showing how many days till due/ days past due. I have added conditional formatting to make past due turn red and that works fine. I have added this formula to the whole sheet until row 5000 (there is no way I will need 5000 rows).
I will use row 2 as an example .
My Cells are J2: Date of Issue, L2: Deadline; using formula =(J2+7), and M2: Days Until Due; using formula =(L2-TODAY())
The issue that I am having is that some dates of issue have yet to be determined and Column L is returning a date of 07-JAN-00 and causing Column M return a value of -43255 Past due.
Is there anyway to avoid the values in Column L from returning this date if the cells in Column J have no value inputted?And to prevent the value of -43255 from returning in Column M?
I would share the sheet, however it contains confidential information.
1 Reply
Sort By
- Steven CorreyCopper ContributorHi Ryan,
Just use an IF statement like this:
=IF(ISBLANK(J2),"",J2+7)
then for the last formula add an iferror like this:
=IFERROR(L2-TODAY(),"")