IF LEN formula
 

Hello,

I would like to know how to set a formula to check two cells for dates and
then calculate a start date by taking one of those cells and adding lead days
from another cell. The hard part is making the formula 1) check the
confirmed cell for a date and if no date there then 2) use tentative date.

My spreadsheet is like follows:

G2 = tentative project start date
I2 = confirmed project start date
D2 = lead time (usually 45, 60 or 90 days)
E2 = PreWork Start Date

So I need the formula to:
1) check I2 for a date, if this cell has a date then in cell E2 I need the
answer to 'I2-D2'
2) if I2 has no date then use date in G2. So in cell E2 I need answer to
'G2-D2'

I've tried this:

=IF(OR(LEN(I22)=0),AND(G22-D22),AND(I22-D22))

but the formula gives me True, I need a date. please help.


--
Thank You!

IF LEN formula
 

What is the purpose of using "OR", "LEN", & "AND"?

Try this:
=IF(I22="",I22-D22,G22-D22)

HTH,
Paul

"maijiuli" wrote in message
...
Hello,

I would like to know how to set a formula to check two cells for dates and
then calculate a start date by taking one of those cells and adding lead
days
from another cell. The hard part is making the formula 1) check the
confirmed cell for a date and if no date there then 2) use tentative date.

My spreadsheet is like follows:

G2 = tentative project start date
I2 = confirmed project start date
D2 = lead time (usually 45, 60 or 90 days)
E2 = PreWork Start Date

So I need the formula to:
1) check I2 for a date, if this cell has a date then in cell E2 I need the
answer to 'I2-D2'
2) if I2 has no date then use date in G2. So in cell E2 I need answer to
'G2-D2'

I've tried this:

=IF(OR(LEN(I22)=0),AND(G22-D22),AND(I22-D22))

but the formula gives me True, I need a date. please help.


--
Thank You!

IF LEN formula
 

Thanks for looking PCLIVE,

When I paste this formula in cell E22 I get the (negative NUM) in D22 which
in this case is 60. So I got -60. What I was looking for was a date. Say
if

I22 = 10/16/2007
D22 = 60
Then I need in cell E2 = 8/17/2007

If I22 is blank then use date in cell G22 which in this case is 10/14/2007.

Let me know if this is possible. Thanks.
--
Thank You!


"PCLIVE" wrote:

What is the purpose of using "OR", "LEN", & "AND"?

Try this:
=IF(I22="",I22-D22,G22-D22)

HTH,
Paul

"maijiuli" wrote in message
...
Hello,

I would like to know how to set a formula to check two cells for dates and
then calculate a start date by taking one of those cells and adding lead
days
from another cell. The hard part is making the formula 1) check the
confirmed cell for a date and if no date there then 2) use tentative date.

My spreadsheet is like follows:

G2 = tentative project start date
I2 = confirmed project start date
D2 = lead time (usually 45, 60 or 90 days)
E2 = PreWork Start Date

So I need the formula to:
1) check I2 for a date, if this cell has a date then in cell E2 I need the
answer to 'I2-D2'
2) if I2 has no date then use date in G2. So in cell E2 I need answer to
'G2-D2'

I've tried this:

=IF(OR(LEN(I22)=0),AND(G22-D22),AND(I22-D22))

but the formula gives me True, I need a date. please help.


--
Thank You!

IF LEN formula
 

My bad PCLIVE,

It was a date formatting issue. You're formula works perfectly. Thanks
again.
--
Thank You!


"PCLIVE" wrote:

What is the purpose of using "OR", "LEN", & "AND"?

Try this:
=IF(I22="",I22-D22,G22-D22)

HTH,
Paul

"maijiuli" wrote in message
...
Hello,

I would like to know how to set a formula to check two cells for dates and
then calculate a start date by taking one of those cells and adding lead
days
from another cell. The hard part is making the formula 1) check the
confirmed cell for a date and if no date there then 2) use tentative date.

My spreadsheet is like follows:

G2 = tentative project start date
I2 = confirmed project start date
D2 = lead time (usually 45, 60 or 90 days)
E2 = PreWork Start Date

So I need the formula to:
1) check I2 for a date, if this cell has a date then in cell E2 I need the
answer to 'I2-D2'
2) if I2 has no date then use date in G2. So in cell E2 I need answer to
'G2-D2'

I've tried this:

=IF(OR(LEN(I22)=0),AND(G22-D22),AND(I22-D22))

but the formula gives me True, I need a date. please help.


--
Thank You!

IF LEN formula
 

I was going to respond with this, but if it's working, then great!

=IF(I22="",G14,TEXT(I22-D22,"m/dd/yyyy"))


"maijiuli" wrote in message
...
My bad PCLIVE,

It was a date formatting issue. You're formula works perfectly. Thanks
again.
--
Thank You!


"PCLIVE" wrote:

What is the purpose of using "OR", "LEN", & "AND"?

Try this:
=IF(I22="",I22-D22,G22-D22)

HTH,
Paul

"maijiuli" wrote in message
...
Hello,

I would like to know how to set a formula to check two cells for dates
and
then calculate a start date by taking one of those cells and adding
lead
days
from another cell. The hard part is making the formula 1) check the
confirmed cell for a date and if no date there then 2) use tentative
date.

My spreadsheet is like follows:

G2 = tentative project start date
I2 = confirmed project start date
D2 = lead time (usually 45, 60 or 90 days)
E2 = PreWork Start Date

So I need the formula to:
1) check I2 for a date, if this cell has a date then in cell E2 I need
the
answer to 'I2-D2'
2) if I2 has no date then use date in G2. So in cell E2 I need answer
to
'G2-D2'

I've tried this:

=IF(OR(LEN(I22)=0),AND(G22-D22),AND(I22-D22))

but the formula gives me True, I need a date. please help.


--
Thank You!