Forum Discussion

Ryan Holdinga's avatar
Ryan Holdinga
Copper Contributor
Jun 12, 2018

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

  • Steven Correy's avatar
    Steven Correy
    Copper Contributor
    Hi 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(),"")

Resources