IF/AND statement to count date ranges?

[xibkwt]

I'm admittedly an Excel amateur and am looking for some help to classify date ranges in the attached book.

Columns E:G are meant to indicate whether or not the names in column A have volunteered over 1 months (30 days), 3 months (90 days), or 6 months (180 days).

For example:

James Brown's first/last dates are 4/1/17 & 5/31/17. 'yes' in 1 month (because over 30 days) but 'ignore' in 3months, 3 months columns because those dates are in the future and haven't occurred yet. This is important that it isn't classified as "no" because they are still actively volunteering in the future

Jane Black's first/last is 5/1 and 5/10. She did not even meet 1 month, so all values for her are "no"

Is there a way to automate the cells in E:G based on the time periods in the B, C, and D columns?

[leelnich]

Hi xibkwt- welcome to Excel Forum. Questions:
1) James Brown last = 5/31. Why future date?
2) Jane Black first = 5/1, less than 30 days, why NO instead of IGNORE?
3) from 2) how is Active status determined?


Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[xibkwt]

Hi xibkwt- welcome to Excel Forum. Questions:
1) James Brown last = 5/31. Why future date?
2) Jane Black first = 5/1, less than 30 days, why NO instead of IGNORE?
3) from 2) how is Active status determined?


Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Thanks for your reply, Lee.

I realized I wrote the example book over a month ago so the dates are no longer relevant. I've updated it with newer examples (attached) along with better explanations for each.

[sandy666]

try:
E2: =IF(OR(D2="",E1=""),"",IF(IFERROR(AND(DATEDIF(TODAY(),$C2,"d")>7,DATEDIF($B2,$C2,"d")>E$1,E$1>=$G$1),0),"Ignore",IF($D2<E$1,"No",IF($D2>E$1,"Yes",""))))
tested with your example only

[xibkwt]

try:
E2: =IF(E2="","",IF(IFERROR(AND(DATEDIF(TODAY(),$C2,"d")>7,DATEDIF($B2,$C2,"d")>E$1,E$1>=$G$1),0),"Ignore",IF($D2<E$1,"No",IF($D2>E$1,"Yes",""))))
tested with your example only

Hi sandy666,

I converted the table to a range to try the formula but I'm getting a circular reference error

[sandy666]

post edited, read again
I forgot to say: three headers was changed to 30, 90, 180

Note: Formula doesn't work with Excel Table

with Ex.Tab
you can try this one:
=IF(OR(D2="",E1=""),"",IF(IFERROR(AND(DATEDIF(TODAY(),$C2,"d")>7,DATEDIF($B2,$C2,"d")>--E$1,--E$1>=--$G$1),0),"Ignore",IF($D2<--E$1,"No",IF($D2>--E$1,"Yes",""))))
but headers should stay: 30, 90, 180

[xibkwt]

post edited, read again
I forgot to say: three headers was changed to 30, 90, 180

Note: Formula doesn't work with Excel Table

with Ex.Tab
you can try this one:
=IF(OR(D2="",E1=""),"",IF(IFERROR(AND(DATEDIF(TODAY(),$C2,"d")>7,DATEDIF($B2,$C2,"d")>--E$1,--E$1>=--$G$1),0),"Ignore",IF($D2<--E$1,"No",IF($D2>--E$1,"Yes",""))))
but headers should stay: 30, 90, 180

Thank you. I got it to work with the ranges perfectly

I hate to be a bug...but is there any way to make it work with a Table? ie: replace the cell references with values (30/90/180) instead?

[sandy666]

read post #6 again
or check att.

[sandy666]

you can try to play with REPLACE() for all three headers but it will go to be a monster

[sandy666]

Also you can try with MID() but headers should be like: 1month (30), 3months (90), 6months (180) because my formula need number from headers.
or with original headers try: =--CHOOSE(MATCH(E$1,{"1month","3months","6months"},0),30,90,180) but G1 should be REPLACEd to 180 and locked $G$1

here is example for Ex.Tab. with your headers:

Please Login or Register  to view this content.

Thanks for feedback

If problem is resolved mark thread SOLVED or maybe someone else will give you another solution