How do I stop Excel from treating blank cells as zero?
 

I'm calculating the difference between two dates, such as

A1 - 1st September
B1 - 10th September

So B1-A1 returns 9, which is great.

But I only enter the dates in column B when a certain visit has been made,
and while the cells in B are blank, Excel treats them as zero (or 1st Januray
1900). So B1-A1 returns -39000 odd.

Is there a way to essentially stop it from calculating the formula until
both cells have a value entered?

Hi Michael
Try
=IF(OR(A1="",B1=""),"",B1-A1)

Regards

Roger Govier


Michael Gillie wrote:
I'm calculating the difference between two dates, such as

A1 - 1st September
B1 - 10th September

So B1-A1 returns 9, which is great.

But I only enter the dates in column B when a certain visit has been made,
and while the cells in B are blank, Excel treats them as zero (or 1st Januray
1900). So B1-A1 returns -39000 odd.

Is there a way to essentially stop it from calculating the formula until
both cells have a value entered?

Hi Michael,

Pls try this

=IF(a7=" ",,a7-a7)



"Michael Gillie" wrote:

I'm calculating the difference between two dates, such as

A1 - 1st September
B1 - 10th September

So B1-A1 returns 9, which is great.

But I only enter the dates in column B when a certain visit has been made,
and while the cells in B are blank, Excel treats them as zero (or 1st Januray
1900). So B1-A1 returns -39000 odd.

Is there a way to essentially stop it from calculating the formula until
both cells have a value entered?