Date compared to today
 

I have a worksheet with two columns (Appraisal Status and Appraisal Due Date.
Both columns are filled with data from a separate worksheet using the
VLOOKUP command. I have a thirdcolumn where I wish to put a YES if the
Appraisal status is NOT equal to "Completed" AND the date in the Appraisal
Due Date is less than todays date. ALl I can get is NO responses. for every
situation. Can you help?


A1 B1 C1
Completed need NO response
Registered 11/27/2008 need YES response
Registered 05/30/2009 need NO response

=IF(AND(A1<"Completed",B1<TODAY()),"YES","NO")

Date compared to today
 

Hi,

The formula seems correct. IT looks like the entries in column B are non
dates. Please use the isnumber() formula on column B to check if the
entries there are dates or not.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"BethD" wrote in message
...
I have a worksheet with two columns (Appraisal Status and Appraisal Due
Date.
Both columns are filled with data from a separate worksheet using the
VLOOKUP command. I have a thirdcolumn where I wish to put a YES if the
Appraisal status is NOT equal to "Completed" AND the date in the Appraisal
Due Date is less than todays date. ALl I can get is NO responses. for
every
situation. Can you help?


A1 B1 C1
Completed need NO response
Registered 11/27/2008 need YES response
Registered 05/30/2009 need NO response

=IF(AND(A1<"Completed",B1<TODAY()),"YES","NO")

Date compared to today
 

If the entries in column B are text values, then you could amend your
formula like this:

=IF(AND(A1<"Completed",--B1<TODAY(),B1<""),"YES","NO")

Hope this helps.

Pete

On Mar 20, 1:11*am, "Ashish Mathur" wrote:
Hi,

The formula seems correct. *IT looks like the entries in column B are non
dates. *Please use the isnumber() formula on column B to check if the
entries there are dates or not.

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

"BethD" wrote in message

...



I have a worksheet with two columns (Appraisal Status and Appraisal Due
Date.
Both columns are filled with data from a separate worksheet using the
VLOOKUP command. *I have a thirdcolumn where I wish to put a YES if the
Appraisal status is NOT equal to "Completed" AND the date in the Appraisal
Due Date is less than todays date. *ALl I can get is NO responses. for
every
situation. *Can you help?

A1 * * * * * * * * * * * *B1 * * * * * * * * * * * * *C1
Completed * * * * * * * * * * * * * * * * *need NO response
Registered * * * * * *11/27/2008 * * *need YES response
Registered * * * * * *05/30/2009 * * *need NO response

=IF(AND(A1<"Completed",B1<TODAY()),"YES","NO")- Hide quoted text -

- Show quoted text -

Date compared to today
 

This worked perfectly. Thank you. I'm not sure who everyone is that uses
help others in these discussion groups, but each time my "professor's afar"
have helped me out. Thank you again. Beth

"Pete_UK" wrote:

If the entries in column B are text values, then you could amend your
formula like this:

=IF(AND(A1<"Completed",--B1<TODAY(),B1<""),"YES","NO")

Hope this helps.

Pete

On Mar 20, 1:11 am, "Ashish Mathur" wrote:
Hi,

The formula seems correct. IT looks like the entries in column B are non
dates. Please use the isnumber() formula on column B to check if the
entries there are dates or not.

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

"BethD" wrote in message

...



I have a worksheet with two columns (Appraisal Status and Appraisal Due
Date.
Both columns are filled with data from a separate worksheet using the
VLOOKUP command. I have a thirdcolumn where I wish to put a YES if the
Appraisal status is NOT equal to "Completed" AND the date in the Appraisal
Due Date is less than todays date. ALl I can get is NO responses. for
every
situation. Can you help?

A1 B1 C1
Completed need NO response
Registered 11/27/2008 need YES response
Registered 05/30/2009 need NO response

=IF(AND(A1<"Completed",B1<TODAY()),"YES","NO")- Hide quoted text -

- Show quoted text -

Date compared to today
 

You're welcome, Beth - thanks for feeding back.

Pete

On Mar 23, 4:16*pm, BethD wrote:
This worked perfectly. *Thank you. *I'm not sure who everyone is that uses
help others in these discussion groups, but each time my "professor's afar"
have helped me out. *Thank you again. *Beth



"Pete_UK" wrote:
If the entries in column B are text values, then you could amend your
formula like this:

=IF(AND(A1<"Completed",--B1<TODAY(),B1<""),"YES","NO")

Hope this helps.

Pete

On Mar 20, 1:11 am, "Ashish Mathur" wrote:
Hi,

The formula seems correct. *IT looks like the entries in column B are non
dates. *Please use the isnumber() formula on column B to check if the
entries there are dates or not.

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

"BethD" wrote in message

...

I have a worksheet with two columns (Appraisal Status and Appraisal Due
Date.
Both columns are filled with data from a separate worksheet using the
VLOOKUP command. *I have a thirdcolumn where I wish to put a YES if the
Appraisal status is NOT equal to "Completed" AND the date in the Appraisal
Due Date is less than todays date. *ALl I can get is NO responses.. for
every
situation. *Can you help?

A1 * * * * * * * * * * * *B1 * * * * * * * * * * * * *C1
Completed * * * * * * * * * * * * * * * * *need NO response
Registered * * * * * *11/27/2008 * * *need YES response
Registered * * * * * *05/30/2009 * * *need NO response

=IF(AND(A1<"Completed",B1<TODAY()),"YES","NO")- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -