0

Example data

So basically, "INVOICE DUE DATE" is the SUM of D11+7 ("Date invoice sent out" + 7 days as this is when the invoice is due). However, when there is no date inputted in "DATE INVOICE SENT OUT" the "INVOICE DUE DATE" cell shows 07/01/1900. I want it to show blank until I input a date into D11.

Also - the "DAYS REMAINING" column (K11) is made up of E11-B2 (invoice due date - =TODAY(). I again want this to show as blank until the SUM is complete.

Finally - 2day, 4day and 6day chase are made up of D11+2,D11+4 and D11+6. I want these to show as blank until D11 date entered.

  • 2
    I think something like =IF(ISBLANK(D11),"",D11+7) will do what you want – cybernetic.nomad Mar 29 '19 at 22:22
  • @cybernetic.nomad - why do you post answer as a comment? – ZygD Mar 30 '19 at 0:09
0

Your Formula should constructed like shown below:

Formula in E11:

=IF(ISBLANK(D11)," ",DATE(YEAR(D11),MONTH(D11),DAY(D1)+7))

Formula in K11:

=IF(ISBLANK(D11)," ",(E11-B2))

Formula in L11:

=IF(ISBLANK(D11)," ",(DATE(YEAR(D11),MONTH(D11),DAY(D11)+2)))

Formula in M11:

=IF(ISBLANK(D11)," ",(DATE(YEAR(D11),MONTH(D11),DAY(D11)+4)))

Formula in N11:

=IF(ISBLANK(D11)," ",(DATE(YEAR(D11),MONTH(D11),DAY(D11)+6)))

N.B.

  • Format for cells D11, E11, L11, M11, N11 is DD/MM/YYYY.
  • Cell K11 has General Format.
  • this is perfect thank you! - i understand the majority of the SUM - could you please break it down for me just so next time if i need to do it i understand the SUM i would need to do. Thank you very much – TEE Mar 30 '19 at 11:33
  • If D11 is already a date (and stored as a day count), why do you need to extract its year, month, and day and then turn that back into a date? Why not just use D11 directly? – fixer1234 Mar 30 '19 at 23:41
  • @fixer1234,, thanks for valuable observation I do believe, since it's date value so get the accurate result I prefer to use such method. It's true that D11 can be used directly also. – Rajesh S Mar 31 '19 at 4:47
-1

A simple IF should do it.

=IF(D11<DATE(1999,1,1),"",D11+7)

Not the answer you're looking for? Browse other questions tagged or ask your own question.