| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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") |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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") |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumif....date in table is greater than compared to date | Excel Discussion (Misc queries) | |||
| IF TODAY equals date in cell A10, or if TODAY is beyond that date | Excel Worksheet Functions | |||
| MAX figure within a date range as a function of today()'s date | Excel Worksheet Functions | |||
| SUMIF within date range as a function of today()'s date | Excel Worksheet Functions | |||
| Date : =today() | Excel Worksheet Functions | |||
Hybrid Mode
