Formula for looking up blank and/or date-filled cells- contingent

[Roady]

Hi:

Column A is called 'Receipt Deadline' (mo/dd/yr)
Column B is called 'Actual Receipt Date'. (mo/dd/yr)
Column C would be called 'Outstanding Receipts'?' (yes/no/'N/A')

I want Column C to look in Column A and IF there is a date entered there,
then look up column B and see if there is a date entered there as well. Then
1 of 3 scenarios will occur in Column C:

1. If there is a date in A but none in B, then I want it to return a value
of 'Yes' in C.
2. If there is a date in both A and B, then I want it to return a value of
'No' in C.
3. If there are no dates in either A or B, I want it to return a value of
'N/A'.

Can you help? thank you!
Roady

 

[RagDyeR]

Does this work for you:

=IF(A1*B1>0,"No",IF(A1+B1>0,"Yes","N/A"))
?

Copy down as needed.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi:

Column A is called 'Receipt Deadline' (mo/dd/yr)
Column B is called 'Actual Receipt Date'. (mo/dd/yr)
Column C would be called 'Outstanding Receipts'?' (yes/no/'N/A')

I want Column C to look in Column A and IF there is a date entered there,
then look up column B and see if there is a date entered there as well. Then
1 of 3 scenarios will occur in Column C:

1. If there is a date in A but none in B, then I want it to return a value
of 'Yes' in C.
2. If there is a date in both A and B, then I want it to return a value of
'No' in C.
3. If there are no dates in either A or B, I want it to return a value of
'N/A'.

Can you help? thank you!
Roady

 

[Roady]

Unfortnately, that returns an Value error. I think it doesn't want to
multiply dates- just as a guess...

 

[RagDyeR]

No, it doesn't want to multiply *text*!

That means your dates are *not* XL "legal" dates.

Try this:

=IF(AND(A1>0,B1>0),"No",IF(OR(A1>0,B1>0),"Yes","N/A"))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Unfortnately, that returns an Value error. I think it doesn't want to
multiply dates- just as a guess...

 

[Roady]

Now I am seeing a Circular Reference Error.

Also, how do I make the dates legal as you say below?

 

[RagDyer]

A circular reference means that the cell containing the formula is itself
within the calculating range of the formula, or, a cell in the calculating
range of the formula is referring to the cell containing the formula.

As far as "legal" dates -
In XL, a date is a number (days since the start of 1900) formatted to
display in any one of a number of different masks that you may choose.

This allows for calculations to be performed on them (dates).

Actually, it's easier to enter a "legal" date then not!
XL takes anything that looks like a date, and makes it a date.

These entries will *all* give you a "legal" date of July 2, 2008:

July 2, 2008
Jul 2, 08
Jul 2
7/2/08
7/2/2008
7/2
7-2
7-2-08
7-2-2008
7-2/08
7/2-08
Plus a few others.

However, what mask displays after you hit <Enter> depends on your
formatting.

If the cell(s) for date entry are *PRE*formatted to TEXT, all bets are off!
Anything entered may look like a date, but will *not* be a "legal" date.
This might be your situation.

 

[Roady]

I checked and my dates are in fact "legal" so that was not the issue. And
yet, neither of those formulas work:

=IF(AND(A1>0,B1>0),"No",IF(OR(A1>0,B1>0),"Yes","N/A"))
=IF(A1*B1>0,"No",IF(A1+B1>0,"Yes","N/A"))

Any other suggestions?
I appreciate it!
Roady

 

[Gord Dibben]

How did you do the "legal" check?

Enter =ISNUMBEWR(cellref) in a cell adjacent to one of the dates.

Return TRUE or FALSE?


Gord Dibben MS Excel MVP

 

[RagDyer]

What does "Both Don't Work" mean?

Are you getting #Value! errors from both formulas ... or what?