Ageing report - YouTube

hi guys my name is Vikal Jain and today I am going to show you how to create an aging report  

for your debtors, creditors Loans and Fixed deposits and so on. so here is the product category, colors, unit price 

order date, Number of rows is, control down arrow now 60,403 huge list  

I have now my boss has ordered me to create the ageing. what type of ageing you want ! He want ageing 

like this, 0 to 30 days outstanding 31 to 60, 61 to 90.91 to 120 only so this type of report is very easy  

to create and this type of the report I am going to show you share it with you. so, first we need  

to calculate the number of days outstanding. So, how will calculate the number of days outstanding for  

this. The trick is simple Today Date - Order Date. Today is the 26 March so 26 March - Order Date. so  

I'm going to show do that today Oops. Today --> close parenthesis. Today is going to calculate 

today's date, Minus this. now Excel has a feature whenever you are calculating the difference  

between two days then it is going to show you the date, but believe me this is not a date  

it is number you have to change this into generalor press CTRL + SHIFT + ~ from your keyboard  

now see it is general and drag and drop it has calculated that now first we understand this report.  

Here, we have two criteria, What ? greater than zero, below than 30, greater than 31 below  

60 greater than 61 below 90 but here  at the end here we have only one criteria and  

What is that criteria? greater then 181 only one criteria, so let's try to building this report. so for this what  

we need to do just extract the upper limit, sorry just extract the upper limit so  

I am going to extract this by manual typing 30 60 90 120 150 and 180 so here we don't need  

to do this. Now the 0 I am going to type here, Now check, 0 to 30, 30 to 60, 60 to  

90, 91 and 120 and 120 to 180. Now I am going to  

use two functions here first is IF and second is AND. So, let's try it now I am going to use AND what  

is the use of AND here. AND is going to increase  my logical limit. IF can show you only one but AND can  

help you where more logical so my first logical is number of outstanding is greater than this and

less then

and equal to this now you have to be careful about $ sign why? E6 I'm talking about this  

whenever we are talking we are going to shift left to right our column we're not going to  

change but our rows will change so we need to put the dollar sign in front of E here in front 

4 because row is not going to change but our column is going to change so the same thing here

now here now my logical has completed now Value if true, what do you want I want the unit price  

okay now let's move it to, we need to put a dollar sign in front of C dollar now common  

otherwise what you want if the criteria is not going to match then I want 0 

now close parenthesis now control enter zero is the answer let's drag and drop to the right side a

everything is 0 why because 613 is not  falling here. not falling here, not following  

you you know what about this in this we have only one criteria we have to do this again  

if this is greater than this now comma  what do you want I want Unit Price, then 0  

close parenthesis. Hey don't Press the enter now put the dollar sign first now dollar dollar

dollar you can press F4 key to put a dollar  but I'm putting the manually because I don't want  

to put f4 three times, so Enter Oops I  have done some mistake oh I need to press  

I need to type 0 here and link this here  now dollar now see my answer is here. So what  

is the next step, just drag and drop  or copy paste. Select all cells and drag and  

drop this is going to take some time because my  data is huge so who now see it is calculating  

this is and we have to wait for a while so you  can go for a coffee period till then. be  

patient, Our value is here so let's check  our data and is it corrected something else  

let's check so I am going to check for it  already I have done some mistakes because  

all is zero, zero everything is zero so what is the mistake you let's check E6 this is

if this is greater than this and smaller  than this now I have put in a Opposite SIGN

now enter, now drag and drop again  now it has been calculated let's  

check again. Here is some values  there's no error now see okay

perfect my report is ready I can remove my Filter and Now i will submit my report to Boss so here

now keep enjoying and keep learning.