Create a Past Due Invoice Template for Personnel Effortlessly

Past due invoice template for Personnel

here's our accounts receivable records and here's the due dates for the invoices and we want to be able to type two days late and instantly the records from this data set are extracted showing us the invoices that are two days late when we type a 30 bam there's all invoices that are 30 days late now you are not going to believe how easy it is with microsoft 365 in the year 2022 because guess what back in 2010 december i posted this video right here and back then not everyone had even microsoft 2007 so i had to show them how to count with two conditions using two countif functions and i had to show them how to do it with sum product and of course countifs which was a new function back then but the formula for extracting records that is just flat out complicated and if you were lucky enough back in december 2010 to have the latest excel this formula was a little bit easier but still crazy now this is the history what i'm going to show you right now is the reason that everyone in the world should get microsoft 365. now i always get comments below that oh why are you showing videos none of us have it well guess what even where i work at highline college we're not using microsoft 365 so the truth is is lots of entities around the world have not caught up yet but what you're going to see here is such a common task and is so useful that eventually this is the reason we should all get it but while most of us including me still sit around waiting for our entities to get microsoft 365 here's how easy it is if we have it now the function we want to get today's date this has been around forever today and today is dynamic it always gives you the current date so that means the records that we extract will always be 30 days back now i want to make a text formula up here that indicates how many days backwards we're going so i'm going to say today minus and that's in double quotes and i'm going to join it to this 2. that way this label will always tell us that we're going today minus whatever number of days we have there now the formula we're going to need for the lower end hurdle to extract these records is whatever today is minus the number of days and enter now if we change this to 30 that all updates this will be the upper limit but for invoicing this day will not be included if the invoice date is really 3-3 it's not late so everything from this upper limit but not including it to this lower limit now i'm going to control z for the time being we'll leave it two and here's how easy it is we have the filter function now and array we simply put the entire table i'm going to click in the top cells just the first record then use the keyboard control shift down arrow that highlights all the way down to the bottom now i want to jump back to the active cell so i use control backspace all right comma now include this is where we need a pattern of trues and falses that has the same number of rows as our table now we have two conditions upper limit lower limit and they both have to be simultaneously true that means we have to do an and logical test so in include we're going to run two logical tests and they have to be in parentheses so open parentheses now i highlight just the date column control shift down arrow control backspace and i can pick either limit to start with the and logical test we're going to run doesn't care which one comes first i'm going to start with the lower limit so any dates over there you have to be greater than or equal to whatever date that is now i close parentheses if i highlight this and hit f9 i get trues and falses but i need a second condition so control z and for an and logical test you multiply that way when you get a true times a true filter will know to extract that record now open parentheses get the date column control shift down arrow control backspace and now we have to say less than the upper limit not putting an equal sign because i don't want that date close parentheses and close parentheses that's it when i hit enter that is absolutely amazing when i type a 1 i'm getting just the invoices that are only one day late if i type a 30 bam there it is and i pre-formatted this including date format here but there's all the records so we have built with a formula a totally dynamic solution and we have the luxury of using the simple filter function as long as we know for an and logical test we multiply whatever logical tests there are if you happen to ever run an or logical test it's not multiplication it's addition all right if you like that video be sure to click that thumbs up leave a comment and subscribe because there's always lots more videos to come from excel is fun all right we'll see you next video