# Subtracting non-blank cells only

1. ## Subtracting non-blank cells only

How do I say in C1 that I want =A1-B1 only where A1 and B1 both contain a date?

I am tracking how long it takes for a task to get done w the simple formula of =A1-B1 in my C1 cell. A1 and B1 contain dates or are blank until the task on that line is done. I use the C column to create an average.

I just discovered if A1 and B1 are both empty, I am getting a 0 in C1 which is throwing off my average calculation. I cannot just exclude 0 from the formula that averages the C column bc the result in C is often 0 since A1 and B1 are supposed to happen on the same date.

I know it is something simple, but I hit a brain block. Help!

2. ## Re: Subtracting non-blank cells only

=if(and(isblank(a1),isblank(b1)),"",a1-b1)

3. ## Re: Subtracting non-blank cells only

Spectacular!!! Thanks.

4. ## Re: Subtracting non-blank cells only

It does not work where one cell has a date and one cell does not. In that case, I am getting -43510 instead of the blank.

5. ## Re: Subtracting non-blank cells only

my bad, im exhausted right now. Not the best solution but it gets it done.

=IF(A1="","",IF(B1="","",A1-B1))

6. ## Re: Subtracting non-blank cells only

I found one online and it gives me #NAME? instead of a blank, but it cleans up my averages. Now I just need to figure out how to get an NA instead of the error message...

=IF(AND(ISNUMBER(U28),ISNUMBER(R28)),U28-R28,”NA“)

7. ## Re: Subtracting non-blank cells only

see my post #5

8. ## Re: Subtracting non-blank cells only

It works!!! Thank you. Hope you feel better.

9. ## Re: Subtracting non-blank cells only

A slight variation...
=IF(or(A1="",B1=""),"",A1-B1)

10. ## Re: Subtracting non-blank cells only

The reason you got a #NAME? error when trying to use the formula in post #6 is that the double quotes around NA look like they are so called curly or smart quotes. Replace them with standard (straight) double quotes . Now you have 3 equivalent formulas to choose from

There are currently 1 users browsing this thread. (0 members and 1 guests)