DATE as part of a formula
 

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/comm...lic.excel.misc

DATE as part of a formula
 

Try this in R11

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

Vaya con Dios,
Chuck, CABGx3


"EMW103" wrote:

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/comm...lic.excel.misc

DATE as part of a formula
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"EMW103" wrote in message
...
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/comm...lic.excel.misc

DATE as part of a formula
 

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,"")
--
Gary''s Student - gsnu200829


"EMW103" wrote:

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/comm...lic.excel.misc

DATE as part of a formula
 

No luck :-(

"Gary''s Student" wrote:

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,"")
--
Gary''s Student - gsnu200829


"EMW103" wrote:

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/comm...lic.excel.misc

DATE as part of a formula
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"EMW103" wrote in message
...
No luck :-(

"Gary''s Student" wrote:

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,"")
--
Gary''s Student - gsnu200829


"EMW103" wrote:

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/comm...lic.excel.misc

DATE as part of a formula
 

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?

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"EMW103" wrote in message
...
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/comm...lic.excel.misc

DATE as part of a formula
 

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.

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"EMW103" wrote in message
...
No luck :-(

"Gary''s Student" wrote:

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,"")
--
Gary''s Student - gsnu200829


"EMW103" wrote:

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/comm...lic.excel.misc

DATE as part of a formula
 

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.

"EMW103" wrote in message
...
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.

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"EMW103" wrote in message
...
No luck :-(

"Gary''s Student" wrote:

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,"")
--
Gary''s Student - gsnu200829


"EMW103" wrote:

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/comm...lic.excel.misc

DATE as part of a formula
 

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))))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"EMW103" wrote in message
...
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.

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"EMW103" wrote in message
...
No luck :-(

"Gary''s Student" wrote:

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,"")
--
Gary''s Student - gsnu200829


"EMW103" wrote:

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/comm...lic.excel.misc

DATE as part of a formula
 

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

=IF(Q110,P11,"")

Vaya con Dios,
Chuck, CABGx3





"EMW103" wrote:

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/comm...lic.excel.misc

DATE as part of a formula
 

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!

"Fred Smith" wrote:

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.

"EMW103" wrote in message
...
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.

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"EMW103" wrote in message
...
No luck :-(

"Gary''s Student" wrote:

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,"")
--
Gary''s Student - gsnu200829


"EMW103" wrote:

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/comm...lic.excel.misc

DATE as part of a formula
 

See my reply to Fred Smith, but thanks.

"CLR" wrote:

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

=IF(Q110,P11,"")

Vaya con Dios,
Chuck, CABGx3





"EMW103" wrote:

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/comm...lic.excel.misc

DATE as part of a formula
 

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

"EMW103" wrote in message
...
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?

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"EMW103" wrote in message
...
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/comm...lic.excel.misc

DATE as part of a formula
 

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.
--
David Biddulph


"EMW103" wrote in message
...
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.

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"EMW103" wrote in message
...
No luck :-(

"Gary''s Student" wrote:

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,"")
--
Gary''s Student - gsnu200829


"EMW103" wrote:

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/comm...lic.excel.misc