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

#### celestersue

##### New Member
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?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Scott Huish

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

#### celestersue

##### New Member
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

##### MrExcel MVP
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
ABCDE
14/1/20134/3/20132
24/1/2013
34/3/2013
44/1/20134/12/201311
Sheet1

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

Last edited:

#### celestersue

##### New Member
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 1 4/1/2013 4/3/2013 * * 2 2 4/1/2013 * * * * 3 * 4/3/2013 * * * 4 4/1/2013 * * 4/12/2013 11

<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>
</tbody>

 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.

Replies
10
Views
199
Replies
1
Views
143
Replies
2
Views
334
Replies
2
Views
210
Replies
14
Views
693

1,195,600
Messages
6,010,651
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back