Return Zero in Date Subtraction

[Celticshadow]

Hi All

I have a range of dates in a row extending to ten columns but sometimes not all cells in the row have a date (the cells with dates in are dependent on other cells - so if the dependent cell does not have a date it the cell returns 00-Jan-00).

If for example the first two cells in the row had two dates as attached how do I get the cell to return zero when subtracting one from the other.

Thanking you in advance.

Kind Regards


https://spreadsheets.google.com/ccc?hl=en&key=tYgdzUn_vPqgbN6E0_dqtmA&authkey=CImkspwI&hl=en#gid=0

 

[Aladin Akyurek]

Hi All

I have a range of dates in a row extending to ten columns but sometimes not all cells in the row have a date (the cells with dates in are dependent on other cells - so if the dependent cell does not have a date it the cell returns 00-Jan-00).

If for example the first two cells in the row had two dates as attached how do I get the cell to return zero when subtracting one from the other.

Thanking you in advance.

Kind Regards


https://spreadsheets.google.com/ccc?hl=en&key=tYgdzUn_vPqgbN6E0_dqtmA&authkey=CImkspwI&hl=en#gid=0

What is the question as =C4-D4 yields 0?

 

[Celticshadow]

What is the question as =C4-D4 yields 0?

Hi Aladin

How do I get B4 - C4 to equal zero and not as is. C4 is dependent on a date being entered in another cell as it is not it returns 40,497 instead of 0.

Kind Regards

 

[Aladin Akyurek]

Hi Aladin

How do I get B4 - C4 to equal zero and not as is. C4 is dependent on a date being entered in another cell as it is not it returns 40,497 instead of 0.

Kind Regards

B4: 14-Nov-10
C4: 30-Dec-99

You say: =B4-C4 should yield 0. How do you mean that?
By the way, I get: 3972.

 

[Celticshadow]

Hi

I have updated the link with an explanation that I hope shows things more clearly.

Kind Regards

https://spreadsheets.google.com/ccc...UpoUXc5QkR0dS0zcTFRWXc&hl=en&authkey=CMqmi6AD

 

[Aladin Akyurek]

Hi

I have updated the link with an explanation that I hope shows things more clearly.

Kind Regards

https://spreadsheets.google.com/ccc...UpoUXc5QkR0dS0zcTFRWXc&hl=en&authkey=CMqmi6AD

In B6 enter and copy across:

=IF(COUNT(A4:B4),A4-B4,0)

 

[Celticshadow]

In B6 enter and copy across:

=IF(COUNT(A4:B4),A4-B4,0)

Hi Aladin

Many thanks for replying.

Unfortunately that formula still returns a figure above zero in C6 when the formula is copied across. Where as I need it to return Zero.

Kind Regards

 

[Aladin Akyurek]

Hi Aladin

Many thanks for replying.

Unfortunately that formula still returns a figure above zero in C6 when the formula is copied across. Where as I need it to return Zero.

Kind Regards

Try this one...

=IF(COUNTIF(A4:B4,">0")=2,A4-B4,0)

 

[Celticshadow]

Try this one...

=IF(COUNTIF(A4:B4,">0")=2,A4-B4,0)

Hi Aladin

That works an absolute treat many thanks for helping me out on this, you are a diamond. Top class as usual.

Kind Regards