DATE as part of a formula

[EMW103]

Why doesn't this formula work? =IF(Q11=DATE, P11). Can you not use a date
as part of a calculation or question, and if not, what alternative would be
recommended?

The idea is: The formula I want is to copy cell P11 into cell R11 if there's
text/data(which in this case is a date) in cell Q11. Is there something
other than DATE that I could use?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...5-f3f3248ff1dd&dg=microsoft.public.excel.misc

 

[CLR]

Try this in R11

=IF(LEN(Q11)>0,P11,"")

Vaya con Dios,
Chuck, CABGx3

 

[Don Guillett]

Look in the help index for DATE. Then look for TODAY

 

[Gary''s Student]

First install this UDF:

Function isdatee(r As Range) As Boolean
isdatee = IsDate(r.Value)
End Function

and then the formula becomes:

=IF(isdatee(Q11), P11,"")

 

[EMW103]

No luck :-(

First install this UDF:

Function isdatee(r As Range) As Boolean
isdatee = IsDate(r.Value)
End Function

and then the formula becomes:

=IF(isdatee(Q11), P11,"")

 

[Don Guillett]

Did you look at my post
=if(a1=today(),1,2)

 

[EMW103]

Don, I had spent several hours going through the help menu. This suggests
that I'm looking to insert the date, which I'm not: I've got dates inserted
manually, I'm trying go come up with a formula that recognizes those dates as
part of an equation. I tried this: =IF(Q10=DATEVALUE, P10), but also no
luck. Any thoughts?

 

[EMW103]

I don't get it: this still inserts a date. I want to copy the data in row P
(which is a monetary value) in to row R only if row Q11 contains any data
(which is a date that I've entered manually, it's not a flexible or formulaic
date). Your formula inserts a date in to cell R11.

This =IF(Q10=DATEVALUE,P10) isn't working either, but surely there's
something in place of DATEVALUE that I can use? I feel like this should be
really simple and I just haven't cracked the right terminology.

 

[Fred Smith]

You have two problems. You need to discover whether there's a date in Q10,
and you need to tell Excel what to do if there *isn't* a date.

The general syntax of an If statement is:
=if(condition,true value,false value)

You haven't specified what to do if your statement is false.

Your next problem is determining whether there's a date. Unfortunately
there's no ISDATE function in Excel. Fortunately, a date is just a number,
so try it this way:

=if(isnumber(q10),p10,"what you want if there's no date")

Regards,
Fred.

 

[Don Guillett]

Here are a couple to try if you have a full date entered such as 2/1/2009

=AND(ISNUMBER(I1),LEFT(CELL("format",I1),1)="D")
=NOT(ISERROR(DATE(YEAR(I1),MONTH(I1),DAY(I1))))

 

[CLR]

Didn't like my first one?.........then try this

=IF(Q11>0,P11,"")

Vaya con Dios,
Chuck, CABGx3

 

[EMW103]

THAT''S IT!

Is there a course somewhere online about this level of formulas? I think
I've gone as far as the Microsoft site goes for formula training, and the
help windows can make for pretty brain-numbing reading.

But thanks a million!

 

[EMW103]

See my reply to Fred Smith, but thanks.

Didn't like my first one?.........then try this

=IF(Q11>0,P11,"")

Vaya con Dios,
Chuck, CABGx3

 

[David Biddulph]

Again, please don't try to use a function like DATEVALUE without reading in
Excel help what it does.

Help gives a crystal clear description of what the function does, what the
syntax is, and gives examples. Do please read it, and if there is something
specific in there which you don't understand, please come back to us with a
specific question. The help also has a "See also link" to other date and
time functions within Excel.

 

[David Biddulph]

You need to understand that an Excel date is actually stored as a number
(counted as a number of days from the beginning of 1900) but merely
displayed as a date, so Excel can't really tell the difference between a
date and any other number in Excel. An additional complication is that if
you type in the date in a format which Excel doesn't understand (such as
"29th January, 2009"), Excel will treat it as a text string.

If you merely want to test whether Q10 is empty or not, you can use
=IF(Q10="","",P10) or alternatively =IF(Q10<>"",P10,"")
Note that you need to give the IF statement a result to return if the
condition isn't satisfied, as otherwise it will return the Boolean value
FALSE. I have assumed that if you haven't gor a date in column Q you want
the formula to return the empty string "".

If you want to distinguish text from numbers in column Q, you could use the
ISNUMBER or ISTEXT functions.

If there are any Excel functions that you don't understand, look them up in
Excel help.