Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(AND with date comparison
I have an =IF(AND( .... formula. One of the conditions in the AND portion is
to compare a date in one cell(call it sht-1 A1) with the dates in 2 other cells (call them sht-2, A1 and A2, one being a start date and the other a finish date). I am trying to return a "FALSE " if the sht-1 date falls outside (either before or after) of the dates in sht-2 cells. The formula as written is: =IF(AND($B90,$D90,$E9="yes",N$8='Proposal Data'!$C$7,'Proposal Data'!$C$11=N$8)............................... The portion that isn't working is the 4th argument where N8 is compared to the other dates in C7 and C11. It is returning a "TRUE" when no dates are present in C7 & C11 and allowing the remaining calculations to ocurr. There is more to the formula but it is just additional IF statements. Can someone tell me what i am doing wrong? By the way, when dates are present in C7 & C11 the formula works perfectly. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(AND with date comparison
The below returns true if the date in N8 is between the dates in c7 and c11..
=IF(COUNT('Proposal Data'!$C$7,'Proposal Data'!$C$11)=2,MEDIAN('Proposal Data'!$C$7,'Proposal Data'!$C$11,N$8)=N8) If this post helps click Yes --------------- Jacob Skaria "PSU35" wrote: I have an =IF(AND( .... formula. One of the conditions in the AND portion is to compare a date in one cell(call it sht-1 A1) with the dates in 2 other cells (call them sht-2, A1 and A2, one being a start date and the other a finish date). I am trying to return a "FALSE " if the sht-1 date falls outside (either before or after) of the dates in sht-2 cells. The formula as written is: =IF(AND($B90,$D90,$E9="yes",N$8='Proposal Data'!$C$7,'Proposal Data'!$C$11=N$8)............................... The portion that isn't working is the 4th argument where N8 is compared to the other dates in C7 and C11. It is returning a "TRUE" when no dates are present in C7 & C11 and allowing the remaining calculations to ocurr. There is more to the formula but it is just additional IF statements. Can someone tell me what i am doing wrong? By the way, when dates are present in C7 & C11 the formula works perfectly. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(AND with date comparison
AND($B90,$D90,$E9="yes",N$8=$C$7,$C$11=N$8,AND (C70,C110))
On Oct 26, 10:32*pm, PSU35 wrote: I have an =IF(AND( .... formula. *One of the conditions in the AND portion is to compare a date in one cell(call it sht-1 A1) with the dates in 2 other cells (call them sht-2, A1 and A2, one being a start date and the other a finish date). *I am trying to return a "FALSE " if the sht-1 date falls outside (either before or after) of the dates in sht-2 cells. * The formula as written is: =IF(AND($B90,$D90,$E9="yes",N$8='Proposal *Data'!$C$7,'Proposal * Data'!$C$11=N$8)............................... The portion that isn't working is the 4th argument where N8 is compared to the other dates in C7 and C11. *It is returning a "TRUE" when no dates are present in C7 & C11 and allowing the remaining calculations to ocurr. *There is more to the formula but it is just additional IF statements. *Can someone tell me what i am doing wrong? *By the way, when dates are present in C7 & C11 the formula works perfectly. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(AND with date comparison
N$8='Proposal Data'!$C$7,'Proposal Data'!$C$11=N$8
The only way that could be TRUE is if there are certain combinations of numbers and/or TEXT in the 3 cells. Maybe try adding a condition that tests all 3 cells are numeric: AND(COUNT(N$8,'Proposal Data'!$C$7,'Proposal Data'!$C$11)=3,N$8='Proposal Data'!$C$7,'Proposal Data'!$C$11=N$8) -- Biff Microsoft Excel MVP "PSU35" wrote in message ... I have an =IF(AND( .... formula. One of the conditions in the AND portion is to compare a date in one cell(call it sht-1 A1) with the dates in 2 other cells (call them sht-2, A1 and A2, one being a start date and the other a finish date). I am trying to return a "FALSE " if the sht-1 date falls outside (either before or after) of the dates in sht-2 cells. The formula as written is: =IF(AND($B90,$D90,$E9="yes",N$8='Proposal Data'!$C$7,'Proposal Data'!$C$11=N$8)............................... The portion that isn't working is the 4th argument where N8 is compared to the other dates in C7 and C11. It is returning a "TRUE" when no dates are present in C7 & C11 and allowing the remaining calculations to ocurr. There is more to the formula but it is just additional IF statements. Can someone tell me what i am doing wrong? By the way, when dates are present in C7 & C11 the formula works perfectly. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF(AND with date comparison
Thank you...you simple solution worked wonderfully.
Many thanks. Rick "muddan madhu" wrote: AND($B90,$D90,$E9="yes",N$8=$C$7,$C$11=N$8,AND (C70,C110)) On Oct 26, 10:32 pm, PSU35 wrote: I have an =IF(AND( .... formula. One of the conditions in the AND portion is to compare a date in one cell(call it sht-1 A1) with the dates in 2 other cells (call them sht-2, A1 and A2, one being a start date and the other a finish date). I am trying to return a "FALSE " if the sht-1 date falls outside (either before or after) of the dates in sht-2 cells. The formula as written is: =IF(AND($B90,$D90,$E9="yes",N$8='Proposal Data'!$C$7,'Proposal Data'!$C$11=N$8)............................... The portion that isn't working is the 4th argument where N8 is compared to the other dates in C7 and C11. It is returning a "TRUE" when no dates are present in C7 & C11 and allowing the remaining calculations to ocurr. There is more to the formula but it is just additional IF statements. Can someone tell me what i am doing wrong? By the way, when dates are present in C7 & C11 the formula works perfectly. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date comparison | Excel Discussion (Misc queries) | |||
Date comparison | Excel Worksheet Functions | |||
date comparison help | Excel Discussion (Misc queries) | |||
Date comparison | Excel Worksheet Functions | |||
Date comparison | Excel Discussion (Misc queries) |