Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
I'm dealing with only 4 specific columuns in a worksheet. If the cell in one
column is YES (O4) and the two other columns have dates greater than 5 Jul 08, then I want to show "GO" in the fourth column otherwise it would reflect "NO GO". I should know this but haven't worked with Excel for about a year and forgotten too much. Appreciate any assistance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
A formula solution where your date to compare is in cell o1
=IF(AND(O2="yes",P2$O$1,Q2$O$1),"go","no go") -- Don Guillett Microsoft MVP Excel SalesAid Software "Bernie" wrote in message ... I'm dealing with only 4 specific columuns in a worksheet. If the cell in one column is YES (O4) and the two other columns have dates greater than 5 Jul 08, then I want to show "GO" in the fourth column otherwise it would reflect "NO GO". I should know this but haven't worked with Excel for about a year and forgotten too much. Appreciate any assistance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Bernie wrote...
I'm dealing with only 4 specific columuns in a worksheet. If the cell in one column is YES (O4) and the two other columns have dates greater than 5 Jul 08, then I want to show "GO" in the fourth column otherwise it would reflect "NO GO". *I should know this but haven't worked with Excel for about a year and forgotten too much. If the Yes/No(/Other?) were in A3, the two dates in B3 and C3, and the result in D3, then use the following formula in cell D3. =IF(AND(A3="Yes",MIN(B3,D3)DATE(2008,7,5)),"GO"," NO GO") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Try this:
=IF(AND(O4="Yes",P47/5/2008,Q47/5/2008),"GO","NO GO") -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Bernie" wrote: I'm dealing with only 4 specific columuns in a worksheet. If the cell in one column is YES (O4) and the two other columns have dates greater than 5 Jul 08, then I want to show "GO" in the fourth column otherwise it would reflect "NO GO". I should know this but haven't worked with Excel for about a year and forgotten too much. Appreciate any assistance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Michael wrote...
Try this: =IF(AND(O4="Yes",P47/5/2008,Q47/5/2008),"GO","NO GO") .... Doesn't do what the OP asked. Excel will *ALWAYS* evaluate 7/5/2008 as 7 divided by 5 divided by 2008, or 0.000697211, which is less than all valid dates after 31 Dec 1899 (1900 date) or 1 Jan 1904 (1904 date system). |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Michael,
Here's what I typed in replacing p and q with the actual cell and it will not give me a "GO" with dates after 5 Jul 08 in both cells. =IF(AND(O4="Yes",ES77/5/2008,EU77/5/2008),"GO","NO GO") "Michael" wrote: Try this: =IF(AND(O4="Yes",P47/5/2008,Q47/5/2008),"GO","NO GO") -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Bernie" wrote: I'm dealing with only 4 specific columuns in a worksheet. If the cell in one column is YES (O4) and the two other columns have dates greater than 5 Jul 08, then I want to show "GO" in the fourth column otherwise it would reflect "NO GO". I should know this but haven't worked with Excel for about a year and forgotten too much. Appreciate any assistance. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
showed "NO GO" for valid dates greater than 5 jul 08 in those two cells.
"Harlan Grove" wrote: Bernie wrote... I'm dealing with only 4 specific columuns in a worksheet. If the cell in one column is YES (O4) and the two other columns have dates greater than 5 Jul 08, then I want to show "GO" in the fourth column otherwise it would reflect "NO GO". I should know this but haven't worked with Excel for about a year and forgotten too much. If the Yes/No(/Other?) were in A3, the two dates in B3 and C3, and the result in D3, then use the following formula in cell D3. =IF(AND(A3="Yes",MIN(B3,D3)DATE(2008,7,5)),"GO"," NO GO") |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Guys,
Thanks for your help. All your suggestions worked. "Bernie" wrote: I'm dealing with only 4 specific columuns in a worksheet. If the cell in one column is YES (O4) and the two other columns have dates greater than 5 Jul 08, then I want to show "GO" in the fourth column otherwise it would reflect "NO GO". I should know this but haven't worked with Excel for about a year and forgotten too much. Appreciate any assistance. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
You might wanna add a little error trapping to this formula:
=IF(AND(A3="yes",(B3<"")*(D3<"")*MIN(B3,D3)DATE (2008,7,5)),"GO","NO GO") Currently will fail if one of the date cells has a valid date and the other cell is blank. -- ** John C ** "Bernie" wrote: showed "NO GO" for valid dates greater than 5 jul 08 in those two cells. "Harlan Grove" wrote: Bernie wrote... I'm dealing with only 4 specific columuns in a worksheet. If the cell in one column is YES (O4) and the two other columns have dates greater than 5 Jul 08, then I want to show "GO" in the fourth column otherwise it would reflect "NO GO". I should know this but haven't worked with Excel for about a year and forgotten too much. If the Yes/No(/Other?) were in A3, the two dates in B3 and C3, and the result in D3, then use the following formula in cell D3. =IF(AND(A3="Yes",MIN(B3,D3)DATE(2008,7,5)),"GO"," NO GO") |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Thanks John, that's a good idea for another area of the spreadsheet. I do
have a question for you. All the data in the worksheet has to be greater than 5 Jul 08 to be valid and I have several sections with various columns. If I had a section with 4 columns (1 showing % complete and the other 3 are data columns) and only two have valid dates, how would I type that formula in the percentage column? This is how much of the spreadsheet is structured. "John C" wrote: You might wanna add a little error trapping to this formula: =IF(AND(A3="yes",(B3<"")*(D3<"")*MIN(B3,D3)DATE (2008,7,5)),"GO","NO GO") Currently will fail if one of the date cells has a valid date and the other cell is blank. -- ** John C ** "Bernie" wrote: showed "NO GO" for valid dates greater than 5 jul 08 in those two cells. "Harlan Grove" wrote: Bernie wrote... I'm dealing with only 4 specific columuns in a worksheet. If the cell in one column is YES (O4) and the two other columns have dates greater than 5 Jul 08, then I want to show "GO" in the fourth column otherwise it would reflect "NO GO". I should know this but haven't worked with Excel for about a year and forgotten too much. If the Yes/No(/Other?) were in A3, the two dates in B3 and C3, and the result in D3, then use the following formula in cell D3. =IF(AND(A3="Yes",MIN(B3,D3)DATE(2008,7,5)),"GO"," NO GO") |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Harlan Grove wrote:
.... If the Yes/No(/Other?) were in A3, the two dates in B3 and C3, and the result in D3, then use the following formula in cell D3. =IF(AND(A3="Yes",MIN(B3,D3)DATE(2008,7,5)),"GO", "NO GO") Sorry, typo, should be =IF(AND(A3="Yes",MIN(B3,C3)DATE(2008,7,5)),"GO"," NO GO") If you also want to trap blank cells, change this to =IF(AND(A3="Yes",MIN(N(B3),N(C3))DATE(2008,7,5)), "GO","NO GO") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I enter a formula, Excel shows the formula not the results | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) |