Urgent help required with Month and Year functions

[willmarks1984]

Hi All,
I have entered the formula:


=IF(B2>$L$1,"After Period End",(IF(MONTH($L$1)&YEAR($L$1)=MONTH(B2)&YEAR(B2),"0-30 days",(IF(MONTH($L$1)&YEAR($L$1)=MONTH(B2)+1&YEAR(B2),"31-60 days",(IF(MONTH($L$1)&YEAR($L$1)=MONTH(B2)+1&YEAR(B2)+1,"31-60 days",(IF(MONTH($L$1)&YEAR($L$1)=MONTH(B2)+2&YEAR(B2),"61-90 days", (IF(MONTH($L$1)&YEAR($L$1)=MONTH(B2)+2&YEAR(B2)+1,"61-90 days",(IF(MONTH($L$1)&YEAR($L$1)=MONTH(B2)+3&YEAR(B2),"91-120 days",(IF(MONTH($L$1)&YEAR($L$1)=MONTH(B2)+3&YEAR(B2)+1,"91-120 days","120+ days")))))))))))))))


In cell B2 I have the date (British format) 05/12/2016
In cell L1 I have the date (British format) 24/02/2017

I am expecting my formula to produce the result 61-90 days but instead it produces 120+ days . What am I doing wrong? Can anyone help me?

[Phuocam]

Try this ...

=IF(B2>$L$1,"After Period End",LOOKUP($L$1-B2,{0,31,61,91,121},{"0-30 days","31-60 days","61-90 days","91-120 days","120+ days"}))

[willmarks1984]

Thanks Phuocam. You're kind of a genius but it doesn't meet the requirement I've been set, which was (Assuming the date in L1 is 24.02.2017), all dates that are XX/02/2017 should be labelled 0-30 days. All dates that are XX/01/2017 i.e. previous month should be 31-60 days. All dates that are XX/12/2017 should be 61-90 days etc.

I know that seems counter intuitive, but it is for the purposes of monitoring movements of stock month on month so it is actually the most practical thing to do, given that the period end date (L1) is a different date each month (4-4-5 week periods).

Anyway B2 = 24/01/2017 for example should produce 0-30 days, rather than 31-60 days, which your formula produces. Hence my brutal use of the month and year functions. Can you help me? I'm such a rookie.

[Phuocam]

=IF(B2>$L$1,"After Period End",LOOKUP(DATEDIF(EOMONTH(B2,0)+1,EOMONTH($L$1,0)+1,"m"),
{0,1,2,3,4},{"0-30 days","31-60 days","61-90 days","91-120 days","120+ days"}))

[willmarks1984]

Added to your reputation Phuocam. I'm slightly in love with your excel skills. Thank you.