Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula with 2 conditions met needed
I would appreciate any help you could give. I'm going to try to explain this.
In D2, I need to know if these two conditions - B2 is 5 years or more from D1, and C2 is 62 years or more from D1 - are met. If so, I need to know what date both of those conditions were met, otherwise leave blank. Thanks Amanda |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula with 2 conditions met needed
If I understand what you're asking, try this in D2:
=IF(AND(DATE(YEAR(B2)+5,MONTH(B2),DAY(B2))<=D1,DAT E(YEAR(C2)+62,MONTH(C2),DAY(C2))<=D1),"B2="&TEXT(B 2,"m-d-y")&" & C2="&TEXT(C2,"m-d-y"),"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Amanda" wrote in message ... I would appreciate any help you could give. I'm going to try to explain this. In D2, I need to know if these two conditions - B2 is 5 years or more from D1, and C2 is 62 years or more from D1 - are met. If so, I need to know what date both of those conditions were met, otherwise leave blank. Thanks Amanda |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula with 2 conditions met needed
=AND(DATEDIF(D1,B2,"y")=5,DATEDIF(D1,C2,"y")=62) will tell you whether
both your conditions are met, but I don't understand what you mean by "... I need to know what date both of those conditions were met ...". -- David Biddulph Amanda wrote: I would appreciate any help you could give. I'm going to try to explain this. In D2, I need to know if these two conditions - B2 is 5 years or more from D1, and C2 is 62 years or more from D1 - are met. If so, I need to know what date both of those conditions were met, otherwise leave blank. Thanks Amanda |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula with 2 conditions met needed
It does make the qualifying conditions, however when they are met it is only
giving me the two dates from b2 and c2, rather than the earliest date that both conditions are met. Is that even possible? For instance if b2 was 7 apr 1973 and c2 was 9 nov 1935, the first date that both conditions were met is 9 nov 1997. "RagDyer" wrote: If I understand what you're asking, try this in D2: =IF(AND(DATE(YEAR(B2)+5,MONTH(B2),DAY(B2))<=D1,DAT E(YEAR(C2)+62,MONTH(C2),DAY(C2))<=D1),"B2="&TEXT(B 2,"m-d-y")&" & C2="&TEXT(C2,"m-d-y"),"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Amanda" wrote in message ... I would appreciate any help you could give. I'm going to try to explain this. In D2, I need to know if these two conditions - B2 is 5 years or more from D1, and C2 is 62 years or more from D1 - are met. If so, I need to know what date both of those conditions were met, otherwise leave blank. Thanks Amanda |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula with 2 conditions met needed
It now sounds as if you are wanting a date *after* c2 and b2, whereas from
your original description it sounded as if c2 and b2 were after D1, but your 9 nov 1997 you can get from =MAX(DATE(YEAR(B2)+5,MONTH(B2),DAY(B2)),DATE(YEAR( C2)+62,MONTH(C2),DAY(C2)))You can compare that date with D1, if that is what you intended to ask.--David BiddulphAmanda wrote: It does make the qualifying conditions, however when they are met it is only giving me the two dates from b2 and c2, rather than the earliest date that both conditions are met. Is that even possible? For instance if b2 was 7 apr 1973 and c2 was 9 nov 1935, the first date that both conditions were met is 9 nov 1997. "RagDyer" wrote: If I understand what you're asking, try this in D2:=IF(AND(DATE(YEAR(B2)+5,MONTH(B2),DAY(B2))< =D1,DATE(YEAR(C2)+62,MONTH(C2),DAY(C2))<=D1),"B2=" &TEXT(B2,"m-d-y")&" & C2="&TEXT(C2,"m-d-y"),"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Amanda" wrote in message ... I would appreciate any help you could give. I'm going to try to explain this. In D2, I need to know if these two conditions - B2 is 5 years or more from D1, and C2 is 62 years or more from D1 - are met. If so, I need to know what date both of those conditions were met, otherwise leave blank. Thanks Amanda |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula with 2 conditions met needed
Try:
=IF(OR(DATE(YEAR(B2)+5,MONTH(B2),DAY(B2))=D1,DATE (YEAR(C2)+62,MONTH(C2),DAY(C2))=D1),MAX(DATE(YEAR (B2)+5,MONTH(B2),DAY(B2)),DATE(YEAR(C2)+62,MONTH(C 2),DAY(C2))),"") "Amanda" wrote: It does make the qualifying conditions, however when they are met it is only giving me the two dates from b2 and c2, rather than the earliest date that both conditions are met. Is that even possible? For instance if b2 was 7 apr 1973 and c2 was 9 nov 1935, the first date that both conditions were met is 9 nov 1997. "RagDyer" wrote: If I understand what you're asking, try this in D2: =IF(AND(DATE(YEAR(B2)+5,MONTH(B2),DAY(B2))<=D1,DAT E(YEAR(C2)+62,MONTH(C2),DAY(C2))<=D1),"B2="&TEXT(B 2,"m-d-y")&" & C2="&TEXT(C2,"m-d-y"),"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Amanda" wrote in message ... I would appreciate any help you could give. I'm going to try to explain this. In D2, I need to know if these two conditions - B2 is 5 years or more from D1, and C2 is 62 years or more from D1 - are met. If so, I need to know what date both of those conditions were met, otherwise leave blank. Thanks Amanda |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula with 2 conditions met needed
Thank you all for your help! I was able to take the info below and the
beginning response to get what I needed. As always, everyone here is wonderful! "David Biddulph" wrote: It now sounds as if you are wanting a date *after* c2 and b2, whereas from your original description it sounded as if c2 and b2 were after D1, but your 9 nov 1997 you can get from =MAX(DATE(YEAR(B2)+5,MONTH(B2),DAY(B2)),DATE(YEAR( C2)+62,MONTH(C2),DAY(C2)))You can compare that date with D1, if that is what you intended to ask.--David BiddulphAmanda wrote: It does make the qualifying conditions, however when they are met it is only giving me the two dates from b2 and c2, rather than the earliest date that both conditions are met. Is that even possible? For instance if b2 was 7 apr 1973 and c2 was 9 nov 1935, the first date that both conditions were met is 9 nov 1997. "RagDyer" wrote: If I understand what you're asking, try this in D2:=IF(AND(DATE(YEAR(B2)+5,MONTH(B2),DAY(B2))< =D1,DATE(YEAR(C2)+62,MONTH(C2),DAY(C2))<=D1),"B2=" &TEXT(B2,"m-d-y")&" & C2="&TEXT(C2,"m-d-y"),"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Amanda" wrote in message ... I would appreciate any help you could give. I'm going to try to explain this. In D2, I need to know if these two conditions - B2 is 5 years or more from D1, and C2 is 62 years or more from D1 - are met. If so, I need to know what date both of those conditions were met, otherwise leave blank. Thanks Amanda |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula with 2 conditions met needed
Glad it worked for you.
Sorry that the formatting of the quoted material went awry. I had sought advice in an OE newsgroup on this occasional garbling of the line-feeds from OE, and told that OE-QuoteFix should solve the problem, but apparently it hasn't done so. :-( -- David Biddulph "Amanda" wrote in message ... Thank you all for your help! I was able to take the info below and the beginning response to get what I needed. As always, everyone here is wonderful! "David Biddulph" wrote: It now sounds as if you are wanting a date *after* c2 and b2, whereas from your original description it sounded as if c2 and b2 were after D1, but your 9 nov 1997 you can get from =MAX(DATE(YEAR(B2)+5,MONTH(B2),DAY(B2)),DATE(YEAR( C2)+62,MONTH(C2),DAY(C2)))You can compare that date with D1, if that is what you intended to ask.--David BiddulphAmanda wrote: It does make the qualifying conditions, however when they are met it is only giving me the two dates from b2 and c2, rather than the earliest date that both conditions are met. Is that even possible? For instance if b2 was 7 apr 1973 and c2 was 9 nov 1935, the first date that both conditions were met is 9 nov 1997. "RagDyer" wrote: If I understand what you're asking, try this in D2:=IF(AND(DATE(YEAR(B2)+5,MONTH(B2),DAY(B2))< =D1,DATE(YEAR(C2)+62,MONTH(C2),DAY(C2))<=D1),"B2=" &TEXT(B2,"m-d-y")&" & C2="&TEXT(C2,"m-d-y"),"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Amanda" wrote in message ... I would appreciate any help you could give. I'm going to try to explain this. In D2, I need to know if these two conditions - B2 is 5 years or more from D1, and C2 is 62 years or more from D1 - are met. If so, I need to know what date both of those conditions were met, otherwise leave blank. Thanks Amanda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Conversion Formula Needed | Excel Worksheet Functions | |||
Date formula needed | Excel Worksheet Functions | |||
Simple (I Think) Date Formula Needed | Excel Discussion (Misc queries) | |||
Date formula needed | Excel Discussion (Misc queries) | |||
Custom Date formula needed | Excel Worksheet Functions |