Formula for looking up blank and/or date-filled cells- conting
What does "Both Don't Work" mean?
Are you getting #Value! errors from both formulas ... or what?
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roady" wrote in message
...
I checked and my dates are in fact "legal" so that was not the issue. And
yet, neither of those formulas work:
=IF(AND(A10,B10),"No",IF(OR(A10,B10),"Yes","N/A"))
=IF(A1*B10,"No",IF(A1+B10,"Yes","N/A"))
Any other suggestions?
I appreciate it!
Roady
"RagDyer" wrote:
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.
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roady" wrote in message
...
Now I am seeing a Circular Reference Error.
Also, how do I make the dates legal as you say below?
"RagDyeR" wrote:
No, it doesn't want to multiply *text*!
That means your dates are *not* XL "legal" dates.
Try this:
=IF(AND(A10,B10),"No",IF(OR(A10,B10),"Yes","N/A"))
--
HTH,
RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===
"Roady" wrote in message
...
Unfortnately, that returns an Value error. I think it doesn't want to
multiply dates- just as a guess...
"RagDyeR" wrote:
Does this work for you:
=IF(A1*B10,"No",IF(A1+B10,"Yes","N/A"))
?
Copy down as needed.
--
HTH,
RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===
"Roady" wrote in message
...
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
|