Ageing Monthly Bracket Calculator

[msiyab]

Hi Guys,

We have monthly ageing reports which has the following columns.

Current Period
1 to 30
31 to 60
61 to 90
91 to 180
181 to 360
361 Above


Now, I would like to have a formula to display the months, when I just type in the current month under "Current Period" in the attached file excel file.

[Excel Fox]

So what is your lookup value? Do you want to lookup the current period based on the date you select? Or do you want to select the current period, and show the month? What is your look up value, and what is your look up array.

[msiyab]

What i want is, when i key in the month in B1, the values in B2 to B7 should be filled up automatically as shown in the file.

1 to 30 - One month before Current Period
31 to 60 - Two months before Current Period
61 to 90 - Three months before Current Period
91 to 180 - Four to Six months before Current Period
181 to 360 - Seven to Twelve months before Current Period
361 Above - Thirteen months & Before

[msiyab]

Any help guys?

[LalitPandey87]

First insert two columns in between A & B colums as data in your attached file.

Now put this formuls with CSE in cell B2


=SMALL(IFERROR(INT(MID($A2,SMALL(IF(MID(" " & $A2,ROW($A$1:$A$100),1)=" ",ROW($A$1:$A$100),""),ROW($A$1:$A$100)), SMALL(IF(MID($A2&" ",ROW($A$1:$A$100),1)=" ",ROW($A$1:$A$100),""),ROW($A$1:$A$100))-SMALL(IF(MID(" " & $A2,ROW($A$1:$A$100),1)=" ",ROW($A$1:$A$100),""),ROW($A$1:$A$100)))),""),COLUMN(A1))


Now drag this formula to C2 and then select B2:C2 and fill down till the data ends.

Now copy below formula and paste it cell D2 adn fill down till the data end


=IFERROR(IF(((((C2+1)-B2)/30)-1)=0,TEXT(DATE(YEAR($D$1),MONTH($D$1)-(((B2-1)/30)+1),DAY($D$1)),"MMMM YYYY"), TEXT(DATE(YEAR(DATE(YEAR($D$1),MONTH($D$1)-(((B2-1)/30)+1),DAY($D$1))),MONTH(DATE(YEAR($D$1),MONTH($D$ 1)-(((B2-1)/30)+1),DAY($D$1)))-((((C2+1)-B2)/30)-1),DAY(DATE(YEAR($D$1),MONTH($D$1)-(((B2-1)/30)+1),DAY($D$1)))),"MMMM YYYY") & " to " & TEXT(DATE(YEAR($D$1),MONTH($D$1)-(((B2-1)/30)+1),DAY($D$1)),"MMMM YYYY")),TEXT(DATE(YEAR($D$1),MONTH($D$1)-(((B2-1)/30)+1),DAY($D$1)),"MMMM YYYY")&" & Before")


:cheers:

[msiyab]

Thanks for the reply Lalit Pandey.

However, I am not able to get the results. Instead, I am receiving the #VALUE error.

Also, "put this formuls with CSE in cell B2" - Could you explain what is CSE?

Or, could you please add the formula yourself and attach it here. That would be really nice.

[LalitPandey87]

CSE Means apply formula with Ctrl + Shift + Enter. Try to apply first formula with CSE and check if it is working or not

[msiyab]

Wow!! it works!!

Thanks a lot Lalit...

[msiyab]

I am attaching the excel sheet with the automated calculations here for other members.

[Admin]

Thanks for sharing :)