Formula to leave the C1 blank if either A1 or B1 (both containing dates) are blank

[celestersue]

I am attempting to calculate a total value for a column in which I have a formula giving the number of days between two dates. Example: A1 start date, B1 end date C1= number of days between. I need this formula to leave the C1 blank if either A1 or B1 (both containing dates) are blank. Does anyone have a solution for this?

I need C1 to be blank so that when I take an average of column C it gives an average of only fields with a number value. Sometimes this number value will be a zero so C1 needs to be completely blank otherwise an average of column C would cause a broken formula.

Is this possible? Is there another way to accomplish this?

 

[Scott Huish]

=if(count(a1:b1)=2,b1-a1,"")

 

[celestersue]

I need the formula to work if the date cells (A1 and B1 in the example) are not always adjacent as well as calculating the number of days between those two dates. Here's a better example of what I mean:

A1= 4/1/2013
B1= 4/3/2013
then
C1= 2

or if a cell is blank:

A1= 4/1/2013
B1=
then
C1=

 

[Scott Huish]

That's what my formula does, but if your 2 date cells are not next to each other use a comma to separate the cells in the COUNT function.

=if(count(a1,d1)=2,d1-a1,"")

Some examples:

Excel Workbook
	A	B	C	D	E
1	4/1/2013	4/3/2013			2
2	4/1/2013
3		4/3/2013
4	4/1/2013			4/12/2013	11
Sheet1

I put the formulas in Column E for my examples but it could just as easily go in Column C.

 

[celestersue]

This is perfect! It's exactly what I needed. Thanks!

That's what my formula does, but if your 2 date cells are not next to each other use a comma to separate the cells in the COUNT function.

=if(count(a1,d1)=2,d1-a1,"")

Some examples:

Sheet1

*	A	B	C	D	E
*	*
*	*	*	*
*	*	*	*
*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:68px;"><col style="width:64px;"><col style="width:81px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]4/1/2013[/TD]
[TD="align: right"]4/3/2013[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]4/1/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]4/3/2013[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]4/1/2013[/TD]

[TD="align: right"]4/12/2013[/TD]
[TD="align: right"]11[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E1 =IF(COUNT(A1,B1)=2,B1-A1,"") E2 =IF(COUNT(A2,B2)=2,B2-A2,"") E3 =IF(COUNT(A3,B3)=2,B3-A3,"") E4 =IF(COUNT(A4,D4)=2,D4-A4,"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



I put the formulas in Column E for my examples but it could just as easily go in Column C.