Functions - Difference b/w two dates
 

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.

Functions - Difference b/w two dates
 

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 "".

--
Biff
Microsoft Excel MVP


"Shannakinz" wrote in message
...
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.

Functions - Difference b/w two dates
 

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

"T. Valko" wrote:

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 "".

--
Biff
Microsoft Excel MVP


"Shannakinz" wrote in message
...
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.

Functions - Difference b/w two dates
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Shannakinz" wrote in message
...
That is exactly what i needed, thank you so much!

"T. Valko" wrote:

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 "".

--
Biff
Microsoft Excel MVP


"Shannakinz" wrote in message
...
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.