Create an Excel Invoice for R&D Effortlessly

Excel invoice for R&D

hello welcome back to tips and times Savers I'm Danny rocks today's lesson is in response to a viewer who is asking for my help to build an accounts receivable aging worksheet let's see the letter dear Danny first I'd like to say that your tutorials are great I've watched them all I believe and I have learned quite a bit about Excel thank you I need your help in building an accounts receivable aging report I want to be able to see the amounts that are passing doe for 30 60 90 and over 120 days I would like to see the total amount for each of these categories I've tried several formulas and looked for help in several locations with no success can you help me with this project absolutely here I have a simple invoice register the key cells to point out is over here in B3 we're using the today function to always return the current date the date that is in our system clock and we use the IF function over here to evaluate a formula with an IF function we evaluate a logical test in this case what we're looking for in our logical test is if the due date for the invoice is less than in other words earlier than the cell that refers to the current date then the value of true as a result of the logical test I put in there return this text string pass du the value of FAL put in there this text string current notice that we include text strings inside double quotation marks all right now what we want to be able to do for our viewer is go beyond just P du but it is 1 to 30 days P du 31 to 60 all right now let's come over here and show you how to do that what we've done now is we've turned the if statement The Logical value in this case I've said if the result of this calcul today's date minus the due date for the invoice so if that is greater than 30 the value if true is I want you to do that calculation and I want you to put the number of days in there if the value is false in other words it's not pass du then I want to have a blank in the cell I indicate a blank just as I would with any other text double quotation mark space double quotation mark all right now let's take that information and turn it into an AR agent report so I copied over the last worksheet with the number of days P due we're going to be doing a formula that is going to use the if and the an functions we're going to be looking through and saying if a condition is true we're going to be using and the condition will be if in The Logical test the and condition both of these logicals must be met for it to return true E6 is if the number of days that is passed do meets both of these conditions it's greater than or equal to one and it is less than or equal to 30 then what I want you to put in here is the amount in that column so down here we were able to get this amount into the 31 to 60 day P du column by using IF and and both conditions were met E8 over here Falls within this range it is greater than or equal to 31 and it is less than or equal to 60 all right let's put this into practice over here we use the if and the and function so what we will do is say equals well actually let's make it easier for here we can copy over the formula that we have here and make a couple of quick changes so here what we want to do is edit this to be if the amount the number of days that are passed due if it is greater than or equal to 61 and it is less than or equal to 90 then what I want you to return is the amount controll enter and now let's copy this down so there we have this amount that got picked up over here now for the 90 days we don't need the and Condition it's a simple if condition equals if formula autocomplete function autocomplete I like to use controll a so our logical test will be if over here if the number of days is greater than 90 value if true then I want you to return the amount over here which is D6 and the value if false put nothing in there which is double quotation mark space double quotation mark click okay and there you go now I can copy this down and now we have our totals for our aging report the total amount pass due 31 to 60 days 61 to 90 and over 90 finally here's a little nicity I'm going to introduce you very quickly to the weekday function the weekday function when we put a weekday function equals weekday what we have is going to be looking for a serial number every date is a serial number so this is a serial number that is just formatted as a date then return the type well there's several ways that we can say the week begins on Sunday that's the default so if it is a Sunday the result will be one Monday Tuesday and so on and so forth for Saturday so over here what we can do is we can use the weekday function and say that if the due date for the invoice is on a weekend in other words the result is 7 which is Saturday then we want to give an additional two days for the due date if the return of the weekday function falls into a Sunday then we want to add one additional day all right so the functions that we used in this well lesson the IF function which requires a logical test and then if the value is true from The Logical result return this value value of false if it doesn't pass the weekday function fun which I just introduced you to it's going to be looking for a cell that contains a date a date is a serial number and then optionally what dat you want it to begin so it's going to return a value from 1 to 7 the and function requires that all of our logical values must uh return true to get the result so there you've learned how to create an aging receivable report in Excel I'll see you in the next lesson