Due Date Pop-up Alert in Excel | Date Due Tracker - Formula & Due Date Color Change Notifications

hi it's chester blue began computer training and in this video i want to show you how to create alerts or notifications here's my scenario i've got a list of invoices here's the date due for each invoice so i'm alerted or notified that the invoices are overdue one by this column here yes automatically appears in these cells if these date dues are in the past second you can see that the sales are given a different color background if the date due is in the past and also i get a notification automatically appears in a little pop-up window that lists all the invoices that are overdue so in this video i'm going to show you how to create these three separate solutions okay let's get started so we'll start off with the column that tells us that the date due is in the past and start doing this we need to use the today function and today is useful because it always returns the current date so the current date today the time of recording is the 14th of april 2021 and now i want to be alerted about an invoice not on the day it's due but two days before now this is where this value comes into play here bring forward reminder days and i've said it's two days there so i'm going to add this value in h2 to the today function i need to fix that because i'll be copying the formula down i'm using the f4 key to do that if f4 doesn't work for you just put the dollars in front of the h and the two press enter and you can see now i'm two days on from today's day today's date being the 14th of april 2021 now what i want to say is that if this date here is greater than or equal to the due date then i want a reminder so let's just write that little test in i'm going to say is the result of this formula greater than or equal to the date due if i press enter i get a true here and if i copy this down i get three truths for invoice one three and four the others are further in the future so i don't need to be sent to a reminder about these invoices so instead of true and forces i want yes it's for the truths and blank cells for the forces so this is where i can use my if and i can say if this is true then i want to yes otherwise i want a blank cell so an empty text string close the bracket and copy this down and i get my notifications in the send reminder column the only problem with this is if i deleted this date i would get a yes in this column and i don't want that to be the case so i'm going to add another condition to my if statement i want to say only return yes if this is true and if this cell isn't blank now to have those two conditions i use the and function and i'm going to say is the date due not blank comma and the second test is the existing test within the if statement so i just close the bracket for the end at the end of that press enter copy that down and now if i delete the date due column i still get a blank cell in the send reminder column just undo that now the other thing that could go wrong if someone could type in a date in the wrong format and then where i should be sent a reminder i'm not being sent a reminder now the way around that is to set some data validation on the sales in the date due column so if i select all those cells go to the data tab on my ribbon go over to data validation and in the allow menu choose custom and then write this formula equals is number open bracket d3 now the reason i'm using his number in this context is because dates are numeric values in excel so if the value in d3 is a number the formula will return true which means that the entry will be accepted if it's not a number this formula will return false which means the values will not be accepted the reason i'm referring to d3 is because that's the active cell and as this data validation is applied to the other cells that have been selected this cell reference will automatically change to d4 d5 d67 now i need an error alert a message which i've already put in here title is dateju and then a little error message please enter the date you're in the correct format d-d-m-n-y-y-y with slashes thanks click on okay so now if i type in a date in the incorrect format i get this little message and i have to retry it and enter the date in the correct format so the next method will alert us if the invoice is overdue by automatically coloring the background of the cells and to do this we use conditional formatting now the logical test that we've used within our if formula is the same test that we need to use within our conditional formatting so i'm just going to click on this logical test argument in a little screen tip it selects the logical test and then i'm going to copy it ctrl c and press enter then i'm going to select the cells that i want to apply the conditional formatting to i'll go to the home tab on my ribbon go to conditional formatting new rule use a formula to determine which cells to format type in equals in this little box here and then ctrl v to paste my formula in go to the format button and i'll choose a background color orange background gold background click on ok and there we are it applies the conditional formatting to the invoices that i need to be notified of okay let's move on to the final method which uses that little pop-up dialog box that tells us which invoices we need to chase to create that little dialog box automatically appears when we open the workbook you are going to need to use a little bit of vba code which i've included in the description of this video you'll need to paste the code into the visual basic editor there is a shortcut key for opening the visual basic editor and that's alt f11 now you will need to see the project explorer to do what we are about to do and if it isn't visible just go to view project explorer and then within the project explorer you need to select this workbook if it isn't already selected double click on it and then you'll get this code window on the right of your screen and paste in the code that i've supplied in the description of this video now i will explain a little bit about how this code works i've set a variable here called date due column and that refers to the date due column in my excel worksheet i've defined down here which sales make up the date you column that's d3 and i've said down to d100 just in case i add some more invoices to my data and then i've used a for each next loop to loop through each of the dates due within the date due column and i've used an if statement within this for each next loop and the tests within the if statement are the same as the tests that we used in our if formula earlier on the first test is is the date due not empty and then the second test asks whether the current date which is returned by the date function is greater than equal to date due minus range h2 and if you remember range h2 i just go back to my workbook was the cell that contained my bring forward reminder days value so if both those things are true we want to add that invoice number to the list of invoice numbers that are overdue and that's what this line of code does i've declared a variable called notification message and that is equal to whatever was already in the notification message plus the invoice number that we're currently looping through and i've picked up that invoice number using the offset method so i said take the date due and offset by no rows and two columns to the left and if we look at our data you can see that the invoice number is two columns to the left then once we've looped through all of the date dues in the date due column i then run this if statement if the notification message is empty then i return the message you do not need to chase any invoices today else i return the message the following invoices need chasing today concatenated with all of the invoice numbers that are now contained within the variable notification message now because we've saved this sub procedure within the workbook object it will automatically run when we open the workbook you can see here that the event that is going to trigger this sub procedure is workbook open so i'll close down the visual basic editor i'm going to close and save this workbook and then when we reopen it we should see that notification i'll reopen the workbook and you can see the notification appears click on ok ok that's all i wanted to cover in this particular video hopefully you found it useful if you have please subscribe and i'll see you next video you

Channel: Chester Tugwell

Views: 10,081

Rating: 4.8888888 out of 5

Keywords: excel formula for due date color change, get pop up window alert in excel when a date is reached, excel due date reminder template download, date tracking in excel, highlight overdue dates in excel, excel pop up alert when date is due, how to create due date alert in excel, excel notification popup, how to set reminder in excel for expiry date, due date notification on excel, excel create pop-up message

Id: eLBGhAvgCkc

Length: 10min 49sec (649 seconds)

Published: Thu Apr 22 2021