IF formula for several date ranges.

[pmc001]

Hi,

I'm trying to write what I think will be an IF formula. I need to check against a large number of receipt dates, and then categorise each of them using the aged categories and date range below

PO Accrual > 18 Months 31/12/2018
PO Accrual > 15 Months < 18 Months 01/01/2019 31/03/2019
PO Accrual > 12 Months < 15 Months 01/04/2019 30/06/2019
PO Accrual < 12 Months 01/07/2019


I need them categorised so I can run a SUMIF and total each aged category.

I've attached a really brief worksheet showing the format in case my description is confusing. Any help would be really appreciated.

[Limor_OP]

Hi,
Do you count the months relative for today?

Ifso- maybe this formula can asist :

In E1:
=today()

In D2:
=IF(($E$1-C2)/365*12>18,"31/12/2018",IF(AND(($E$1-C2)/365*12>15,($E$1-C2)/365*12<18),"01/01/2019-31/03/2019",IF(AND(($E$1-C2)/365*12>12,($E$1-C2)/365*12<15),"01/04/2019-30/06/2019",IF(($E$1-C2)/365*12<12,"01/07/2019"))))

[pmc001]

Hi,

Thanks for your reply! The date I will be using for this one will be 30th June 2020, which is the end of the period. What I'm trying to get in Column D is the relevant category from column F depending on how the date in column C relates to the date ranges in columns G and H.
So for example the date in C2 would return 'PO Accrual > 18 Months' in Column D2, and the date in C7 would return 'PO Accrual < 12 Months' in column D7. I need to put the dates into the specific categories in F1 to F4.

[Limor_OP]

OK, you should have said these things in your initial post (no one can guess), notice that you didnt receive any replies because of that, your instructions were missing essential data.

Any way

change the date in E1 to 30/6/2019

and apply this formula in D2 and down:
=IF(C2="","",IF(($E$1-C2)/365*12>18,$F$1,IF(AND(($E$1-C2)/365*12>15,($E$1-C2)/365*12<18),$F$2,IF(AND(($E$1-C2)/365*12>12,($E$1-C2)/365*12<15),$F$3,IF(($E$1-C2)/365*12<12,$F$4)))))

Good luck.

[oeldere]

With vlookup to get the data. (see the yellow cells)

With a pivot table (see the green cells).

[pmc001]

Thanks again for your help.

[pmc001]

Hi,

Thanks for your help!

[oeldere]

Since you got answers of several forummembers it is good to add to whom you replying.