Functions - Difference b/w two dates

[Shannakinz]

Here's what i'm trying to do:
I have a spreadsheet that has columns for "referral date", "intake
consultation date", "monitoring date", "group date" and "1:1 date". At the
end of my spreadsheet, i have a section that calculated the difference (in
days) between each of these dates. Each row will have "referral date" and
"intake consultation date". But for each row, there may be a date in either
just one, a combination of two, or all three of "monitoring date", "group
date" and "1:1 date".

The problem is that in situations where there is a "referral date" and
nothing in the "monitoring date" column, the formula is giving me a negative
number. I'm not sure why it would give me a number at all, let alone a
negative one. Does anyone know why it's doing this? Or how to fix it?

Thanks.

 

[T. Valko]

We'd need to see your formula but it sounds like you're subtracting a date
from an empty cell. An empty cell evaluates as 0 so:

A1 = empty cell
B1 = some date like 1/1/2009

=A1-B1 (formated as Genaerl) returns -39814

So, what you need to do is test the cell and make sure it contains a date to
subtract.

=IF(COUNT(A1:B1)=2,A1-B1,"")

That makes sure there are 2 dates in the cells. If there are 2 dates then
process A1-B1. If there aren't 2 dates in the cells return a blank "".

 

[Shannakinz]

That is exactly what i needed, thank you so much!

 

[T. Valko]

You're welcome. Thanks for the feedback!