Ageing Report using excel

[Sarfraz Khan]

Dear Sir,

please help me to resolve this.

Column A5 has mentioned 30 days ,Column A6 has mentioned 45 days.
Column B5 has mentioned 1000 amount ,Column B5 has mentioned 1500 amount.
I want the to come amount at column C5 from B5 basis if A5 is 30 days and D5 if A5 45 days.
I have attached file for your ref.

 

[alacrity59]

It is a bit painful the first time you try this.

 

[Khalid NGO]

Hi Sarfraz,

I think you simply want a lookup:
enter this in C5
=LOOKUP(C$4,$A$5:$A$11,$B$5:$B$11)
Copy to right.

Regards,

 

[Sarfraz Khan]

Dear Sir,

If formula works fine but instead of mentioning no of days in A column can I take the difference of current date and Invoice date in same formula and caculate the same. eg =IF(AND(A15>1,A15<30),B15,0) A15 is the diffrence between current and Invoice date, if directly take the diffrence in same formula like =today.

 

[Somendra Misra]

Dear Sir,

If formula works fine but instead of mentioning no of days in A column can I take the difference of current date and Invoice date in same formula and caculate the same. eg =IF(AND(A15>1,A15<30),B15,0) A15 is the diffrence between current and Invoice date, if directly take the diffrence in same formula like =today.

Please upload a sample file to illustrate your point.

Regards,

 

[alacrity59]

Yes Sarfraz you can do exactly that in the formula using today() minus the invoice date. I typically have a cell at the top of my aging called report date which I use as an absolute reference.

I've attached an example where you can have the system date in B1 or you can type in any date. Additionally in my example you can change the aging buckets . . . note though that the last bucket is a little different in that there is no ending date.