How do I make a blank cell with a date format blank?
 

I have a new spreadsheet that I need to calculate a due date (column X) based
on the initial appointment date (column Q).(the next appointment would be 90
days after the initial appointment) I want it to ignore blank cells. I have
used the following formula which calculates the info fine, but it adds 90
days to blank cells also and gives me 3/30/1900. Please help
=IF(NOT(ISBLANK($Q2)),F2+90,"")

Seemingly blank cell, if you have a formula returning a blank it is not
empty so you might want to use

=IF($Q2<"",F2+90,"")

if you by any chance are using spaces

=IF(TRIM($Q2)<"",F2+90,"")

--
Regards,

Peo Sjoblom

(No private emails please)


" Pivot Table/Query" wrote
in message ...
I have a new spreadsheet that I need to calculate a due date (column X)
based
on the initial appointment date (column Q).(the next appointment would be
90
days after the initial appointment) I want it to ignore blank cells. I
have
used the following formula which calculates the info fine, but it adds 90
days to blank cells also and gives me 3/30/1900. Please help
=IF(NOT(ISBLANK($Q2)),F2+90,"")

Aren't you trying to reference column Q?

=IF(Q2="","",Q2+90)

In article ,
Pivot Table/Query
wrote:

I have a new spreadsheet that I need to calculate a due date (column X) based
on the initial appointment date (column Q).(the next appointment would be 90
days after the initial appointment) I want it to ignore blank cells. I have
used the following formula which calculates the info fine, but it adds 90
days to blank cells also and gives me 3/30/1900. Please help
=IF(NOT(ISBLANK($Q2)),F2+90,"")

Hi!

Try this:

=IF(AND($Q2<"",F2<""),F2+90,"")

Biff

" Pivot Table/Query" wrote
in message ...
I have a new spreadsheet that I need to calculate a due date (column X)
based
on the initial appointment date (column Q).(the next appointment would be
90
days after the initial appointment) I want it to ignore blank cells. I
have
used the following formula which calculates the info fine, but it adds 90
days to blank cells also and gives me 3/30/1900. Please help
=IF(NOT(ISBLANK($Q2)),F2+90,"")

I thought that formula would work also, but the formula results in true
because it thinks that F2 & Q2 have 1/1/00 in them and that they are not
blank. I am referencing F2 as the initial date and Q2 as the 2nd date. These
meetings will go on every 90 days for 1 year, so there are 5 meetings total.
However, if there is a match located at any of the meetings then the
remaining meetings are not needed. Does that make sense?

"Biff" wrote:

Hi!

Try this:

=IF(AND($Q2<"",F2<""),F2+90,"")

Biff

" Pivot Table/Query" wrote
in message ...
I have a new spreadsheet that I need to calculate a due date (column X)
based
on the initial appointment date (column Q).(the next appointment would be
90
days after the initial appointment) I want it to ignore blank cells. I
have
used the following formula which calculates the info fine, but it adds 90
days to blank cells also and gives me 3/30/1900. Please help
=IF(NOT(ISBLANK($Q2)),F2+90,"")

Try this:

=IF(AND($Q20,F20),F2+90,"")

OR this:

=IF(AND(ISNUMBER($Q2),ISNUMBER(F2)),F2+90,"")

Biff

" Pivot Table/Query" wrote
in message ...
I thought that formula would work also, but the formula results in true
because it thinks that F2 & Q2 have 1/1/00 in them and that they are not
blank. I am referencing F2 as the initial date and Q2 as the 2nd date.
These
meetings will go on every 90 days for 1 year, so there are 5 meetings
total.
However, if there is a match located at any of the meetings then the
remaining meetings are not needed. Does that make sense?

"Biff" wrote:

Hi!

Try this:

=IF(AND($Q2<"",F2<""),F2+90,"")

Biff

" Pivot Table/Query"
wrote
in message ...
I have a new spreadsheet that I need to calculate a due date (column X)
based
on the initial appointment date (column Q).(the next appointment would
be
90
days after the initial appointment) I want it to ignore blank cells. I
have
used the following formula which calculates the info fine, but it adds
90
days to blank cells also and gives me 3/30/1900. Please help
=IF(NOT(ISBLANK($Q2)),F2+90,"")

THANK YOU! That worked (the first one).

"Biff" wrote:

Try this:

=IF(AND($Q20,F20),F2+90,"")

OR this:

=IF(AND(ISNUMBER($Q2),ISNUMBER(F2)),F2+90,"")

Biff

" Pivot Table/Query" wrote
in message ...
I thought that formula would work also, but the formula results in true
because it thinks that F2 & Q2 have 1/1/00 in them and that they are not
blank. I am referencing F2 as the initial date and Q2 as the 2nd date.
These
meetings will go on every 90 days for 1 year, so there are 5 meetings
total.
However, if there is a match located at any of the meetings then the
remaining meetings are not needed. Does that make sense?

"Biff" wrote:

Hi!

Try this:

=IF(AND($Q2<"",F2<""),F2+90,"")

Biff

" Pivot Table/Query"
wrote
in message ...
I have a new spreadsheet that I need to calculate a due date (column X)
based
on the initial appointment date (column Q).(the next appointment would
be
90
days after the initial appointment) I want it to ignore blank cells. I
have
used the following formula which calculates the info fine, but it adds
90
days to blank cells also and gives me 3/30/1900. Please help
=IF(NOT(ISBLANK($Q2)),F2+90,"")