How to Stop Excel from Calculating the Days Between Two Dates if Cell is Blank?

[SusanJohnst]

We often calculate the amount of days between two dates for calculating interest (=DAYS(A22,A21) OR =A22-A21), but if there's no date in the cell below (i.e. the end date) it will show as a negative number (see image). I guess we would need the formula to assume if the cell is blank that it's either today's date or just not calculate until a date is entered.

Thanks!!

 

[Joe4]

If End Date was cell A22, to ignore blanks, use:

=IF(A22="","",A22-A21)

To default to today, try:

=IF(A22="",TODAY()-A21,A22-A21)

 

[Fluff]

How about
=IF(A22="","",A22-A21)

 

[SusanJohnst]

If End Date was cell A22, to ignore blanks, use:

=IF(A22="","",A22-A21)

To default to today, try:

=IF(A22="",TODAY()-A21,A22-A21)

Thank you!! This works

 

[Joe4]

You are welcome.
Glad we could help!

 

[bobby786]

If End Date was cell A22, to ignore blanks, use:

=IF(A22="","",A22-A21)

To default to today, try:

=IF(A22="",TODAY()-A21,A22-A21)

@Joe4

i am trying to use your formula posted above to calculate the days between Order date and Today() in Cell K7, but i am getting wrong results , can you please take a look what i am doing wrong.

My Goal:
I want K7 to display the numbers of days passed since the order was placed till today. And if there is no Date in cell J7 it should display 0 or " -" , once i get working forumla i will copy/paste in the entire column.

BOOK_2020 - 2 - Copy.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
4	Α/Α	PRODUCER	Related Invoice	Material Qty	Related PRODUCT	Deposit Status	Material Used		Dates		LOT NUMBER
5							Name	Status	Order Date	Days Since Order
6	A0001	May Hong	1	510	A800	Pending	Colorbox			#NAME?	00001	A0001
7	A0002	Han Cartons	1	51	A800	Done	Cartons	Done	2020-08-10	1900-01-00	00001	A0002
8	A0003	YiQiang	1	510	A800	Pending	Blister	Done	2020-07-10	2020-10-02	00001	A0003
9	A0004										00000	A0004
Materials

Cell Formulas
Range	Formula
L6:L9	L6	=D6
M6:M9	M6	=B6
I7:I8	I7	=IF(J7="","Pending","Done")
K6	K6	=DATEIF($Q$1-J6)
K7:K8	K7	=IF(J7="",TODAY()-J7,K7-J7)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I6	Expression	=IF(J6="","Pending","Done")	text	NO
I8	Expression	=IF(J8="","Pending","Done")	text	NO
I8	Expression	="IF(J1="""",Pending)"	text	NO
I7	Expression	=IF(J7="","Pending","Done")	text	NO
I7	Expression	="IF(J1="""",Pending)"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B6:H9	Any value
J6:N9	Any value

 

[Fluff]

How about

=IF(J6="",0,TODAY()-J6)

and format the cells as general.

 

[bobby786]

@Fluff
Wow , works like charm , Thank you very much

 

[Fluff]

You're welcome & thanks for the feedback.