Excel Formulas & Functions Part 7: Date & Time Functions

1. Introduction to Date & Time Functions: All right. Welcome back, everyone. This is Section seven. Date and time functions. One of my favorite sections hands down one of the most important sections in this entire training course, simply due to the fact that many people run into a number of issues and headaches trying to work with dates and times in excel. I know I spent about a year kind of fumbling through these not quite understanding what was going on. Um, until I really sat down to understand how Excel is thinking about dates and how excels processing dates. And once I got that understanding, it's actually quite simple, and it made everything easier when it came to working with dates and times. So we're going to start with a date Time 101 section or intro. We'll talk about the date value, which is kind of the key piece here on why it's going to be your new best friend when it comes to working with dates and times and excel talk date formatting and then the fill Siri's option, which is a nice little shortcut for creating sequential dates we're going to today and now , which are volatile functions that return the current date and the exact current time we'll talk year, month, day, hour, minute and second, which agree ways to kind of categorize your date data. We'll talk end of month and your frack going to weekday workday network days. Those air cool tips to kind of show how many workdays fall between two dates? What date follows a certain number of work days from today, so on and so forth? And then we'll finish with date diff, which is another tool to calculate differences between two dates and then wrap up with the , uh, a very useful hands on demo, where I'm gonna show you how to build your own budget pacing tool, which you can customize and transform into anything you want. Two files for download got our Excel for analysts date time functions. PdF and her section seven date Time functions Excel File, which will be walking through as we move through the lectures. So go ahead and download those two files. You could print them out if you choose and, uh, let's dive right in 2. DATEVALUE: Your New BFF: Alright. So, first and foremost, I want to talk about something called the date value. And if there's one lecture gonna listen to or one slide that you're going to read in this entire section, this is the one to pay attention to. And it's because the date value is the critical piece that allows excel to process dates and times so every date. In fact, every moment in time in Excel has an associate ID underlying numerical date value. That's how excels, able to do things like calculate the passage of time or apply statistical functions to dates without those date values dates would kind of just be treated like text fields. You wouldn't really be able to do much. Ah, in terms of manipulation with those dates. So midnight on January 1st 1900 is treated as, I guess, the beginning of time in excels universe. So January 1st 1900 has an underlying date value of one and then every subsequent day beyond January 1st 1900 has a date value one number higher, so january 2nd 1900 has a date value of two and so on and so forth. So Excel is actually really, really smart about dates on, and then it recognizes most type dates and automatically applies a common date format. In most cases, it will be month slash day slash year, with four digits along with its associated date value. Now the way to check if your entry is recognized as a date is to right click going to sell , format and switch to general, and it will show you if there's an underlying date value. So I'll show you an example of that right now. If I jump into just a blank workbook here, I'm gonna type in a date by hand. I'm gonna do January 1st 2015 percenter and have a right click a one going to format cells . Um, as you can see, it defaults the date field. This is the default version Dave Month year. But if I switch to the general category, you can see that it has an underlying value of 42,000 and five because it's the day that's fallen 42,005 days after January 1st 1900 s. So that tells me that okay, Excel has recognized my entry as a date that can treat it as such. It's also pretty good about understanding, kind of strangely formatted one. So I could say like, Oh one dash Owen, Dash 15 and you can see that it switched it over to the default version because it knew I was trying to type a date. You can actually get like, a little bit weird with it. You know, I like to kind of test to see how strange I can get. See even that version where I typed it. As you know, dash and combination of dashes and slashes, it still recognized it as a date, and it still applied that date value to it. So Excel is actually pretty smart when it comes to those things on. And that's kind of the the core reason why are able to do such interesting manipulations and calculations against states eso if you happen to type of date. That, except doesn't recognize one common example that I've seen is if you type a series of numbers like date month day. So for January 1st 2015 again if you typed 2015 0101 with no dashes or slashes. Excel would not recognize that as a date, and so it wouldn't apply in under underlying date value, and it would treat it as a text string or, in this case, value. So if that's the case like if I download some data and the date field is in a very strange format like that, I'll show you how to use tools like date valuer, time value to convert those un formatted dates in two formats that Excel understands. So looking at some examples down here again, January 1st 1900 is date one, so date value equals one 10 days later. On the 11th in 1900 would have a date value of 11. February 6th is 42,041 days after January 1st 1900 and these date values don't have to be whole numbers. In fact, 42 0 41.5 equates to noon on February 6th, whereas 42 0 41.75 equates to 6 p.m. which is 75% of the way through the day. So you can get very granular even down to the second on. That's how you can start working with very particular times in Excel, not just hold dates, so hopefully that helps Once you understand this concept of date value, suddenly everything becomes a bit more clear. We start talking about actually manipulating and applying functions to these dates. 3. Date Formatting & Fill Series: so I just want to cover two more tips and concepts before we dive into the functions themselves. The first is date formatting, so when you format dates and excel, you have a few options. You can either select a preset option from the date category in the format Cells Dialog box , which you can access just by right clicking a cell choosing south formatting. Or you have the option to create your own custom format. So the preset formats again live in the date line or the date category of the format cells dialogue box. And there are a number of options. Apologize. It's a little bit tricky to read, but you could scroll down and you could display your date with a number of different variations. You could show just the day and the month, not the year. You can show it as a two digit year versus four. You could write out the day and leave the year as a number, like there are dozens and dozens of ways to show it and probably has what you need. The other option is to jump down to the custom category and write your own formats here, So here the codes that you can use. This is just a sample of them. The full list is available at support dot office dot com, but this just allows you to display a date in other additional ways. So, for instance, if I have a date in a cell like January 1st 2015 ah, and instead of showing the whole date, I just want to show what day of the week that is. I could format that cell with a custom format de de de de, and it will display the value as Saturday or Friday or Wednesday. I don't know if the top my head what day of the week that was. But, um, important to note that it won't change the underlying value itself. It will only change how you're displaying it. Second thing I want to show you is Phil Siri's, which is a great shortcut that Excel uses to basically help you populate a series of dates without having toe manually type in sequential days or months or years. So let's say I wrote 11 2015 and sell a one here. Excel has recognized it as a date. If I grab the lower right corner of that cell and drag it down to a five by default. Excel is gonna assume that I want to show it as stay over day trending. So basically adding a day for each row. If I say no, I don't want to do that. I just want a copy. 11 2015 and every cell I can click on this little button that pops up after you've dragged the values and I can check copy sells. You'll see that it's defaulted to fill serious, which assumes I want day over, day over, day on and then I have some other options. Here is Well, I can fill only the formatting from Sally one. I could fill it without the formatting or if I wanted to show the sequence at a level other than day. I have those options as well, so I can say Okay, rather than going in increments of days, let's show it in increments of months, in which case this would be 1121314151 or years, in which case it would be 11 2015 2016 17 18 19 or even week days so I can say increase the date by one day. Purcell, but exclude weekends. So this is a really cool wayto create rolling date windows or automate a lot of the process when you're working with date focused data sets that are constantly changing. So keep these two tricks in mind, we're gonna be using them quite a bit in some of the upcoming exercises. 4. TODAY()/NOW(): all right, the first to date and time functions that I want to share with you, our today and now which are designed to simply return the current date or the current exact time. And the good news is that these air perhaps two of the simplest functions in excel and they're so simple because they actually don't even refer to other cells. You just open and close your parentheses. Don't even put anything inside. Call it a day and you're good to go. Um, important to note that these are volatile functions, meaning that they will update any time you make any worksheet change in any open workbook. So there will be constantly changing. Let's jump into excel and, uh, go ahead and open up section seven Date time functions. I will do a quick walk through, as you can see in the first tab daytime functions. I've got a placeholder for the current date and current time, which we're gonna populate in just a minute using today and now we also have a hypothetical project due date, which we'll get to later on. And then this is basically where we're gonna practice all of the functions that we learned in the upcoming lectures. The second tab is a blank budget pacing tool. This is the demo that we're gonna walk through at the end of the section where we're gonna work together and use thes date and time functions to build out a custom budget pacing tool . And then last but not least on the third tab called sample data. We just have a Siris of dates and expenses and these air gonna feed into the budget pacing tool that we build. So jumping back to date time functions on Tom one. Let's just populate these today and now functions in cell C two and C three. So for current date, just gonna type equals today. Open, close. Enter ESO returns the current date, which is August 18th 2015 Current time. Same process equals Now open close press enter. And as you can see, they're returning the same thing except current time or the now function is just a bit more granular that will introduce the hour, minute and second components that the current date or today function will not. So there you go extremely simple, but this is gonna be our starting point to practice the functions in the upcoming lectures 5. YEAR/MONTH/DAY & HOUR/MINUTE/SECOND: so Excel will always calculate dates and times based on their precise underlying cereal values or date values. But what if you need to work with less specific values? So what if you don't care about the specific day? Just care about the month or the year, or even the hour or the minute. Um, if you need to roll up your data in a less granular way and view things at a higher level, there are number of serialization formulas that allow you to do that, so the most common ones include year, month and day and hour, minute and second. Basically, all they do is extract those individual components out of a given date. So syntax wise, they're about as easy as it gets that written exactly as you'd expect. So year is year, month, this month, Days, day and so on, and the only actual component of the formula is the reference to a cell containing a date or time. So let's jump into Excel and write some of these. What I'm gonna do is populate cells C five through C 10 to extract year, month, day, hour, minute and second out of our current date and time fields. So current year it's just gonna be equals year and the serial number or reference that could be either C two or C three in this case. So I start with C to close it off. You'll see that it extracts 2015 because that's the date of the current day. I could also change the reference. I'm gonna show you a little shortcut here rather than going in and changing this and typing c three. Um, obviously, that wouldn't be very difficult. But what I'm gonna do instead is press F two to dive in and then just grab my reference and drag it down to see three press enter. That will accomplish the exact same thing moving on to month. Same story here, do month of C three day of C three. So that's telling me we're in the eighth month and the 18th day of the month Now, for our minute and second, I have to refer to the now function because the today function doesn't give me that level of detail. So if I tried to do our of C two, it's just gonna give me zero because there's no our specified, um coming out of that formula. So what we'll do again is just change the reference. Drag it down and now it's selling ma'am in the 12th hour of the day for minute again, refer to see 3/30 minute and finally, second of Seat three is 16 and again. Because these are volatile functions, you can watch them change any time you make any workbook update. So if I type some values in here, see that second value changing or if I went to the formulas tab, compressed this calculate now button and watched the seconds tick away. So there you go. It's a very simple but very valuable way to extract specific components of a date. 6. The EOMONTH Function: next up, we have the end of month or e o month. Function, which calculates the last day of a given month, can also be used to calculate, start or end dates of previous or future months as well. So the syntax wise, just two components of this formula. It starts with your start date. That could be a current date. It could be a random date. It could be any cell reference to a cell containing a date and then the months component, which is the number of months before or after your starter current date. So if I'm interested in the last day of the current month, I would put a zero for months here. If I'm interested in the last date of next month, I would put a positive one. If I was looking for the last date of the previous month, I'd put a negative one and so on so forth. So let's say we've got a start date or current date of 83 2015 in self see two. Here, we can use the EOE Month function in a number of ways. If we want to calculate the end of the current month, we reduce Eo Month C two comma zero. Um, to calculate this start of the month, you have to get a little bit clever, but you can use the EOE Month function to do that. So, for instance, the start of the current month could be calculated using Eo Month see to negative one, which gives me the last date in the previous month and then just add one to get the first date of the current month. So remember that dates really are just values because of that underlying date value. So adding one to it essentially just tax on one day. So similar case here to get this start date of the next month weaken, say, eo month, See to common zero to get the last day of this month and then add one to get the start date of the following month. So it's jump over to excel and actually practice this, um, for last day of the month and sell see 12. All I need to do is right equals e o month. And then my start, date or sell reference to a date could be C two or C three. Just gonna choose C to here and what I'm gonna do is press F four to fix that reference, which will give him the option to copy and paste this formula elsewhere while continuing to refer to seat to. So I don't want that reference to shift on me comma over to the next piece, which is months. And in this case, I'd like the last day of the current month. So I'm just gonna put a zero there, close the parenthesis and press enter. So the result is 4 to 247 And if you paid attention to the first few lectures, you'll probably recognize that this is a date value that it's showing me on. That means that this cell is formatted with the general formatting type. So I could do is right click going to format cells, change it to date, and then select any option that I choose here. So I'm gonna go with the default, uh, month, day year option for us. Okay. And there you go. 8 31 2015 Keep in mind that depending on when you're actually doing these exercises, you're going to see different values and dates here than I am. Don't worry about that. the concepts and fundamentals should still apply. In any case, I'm so to get the first day of the month, I'm actually gonna copy this formula, paste it down, and now remember the little trick instead of zero, which applies to the current month. I want the last date in the previous month. So I'm gonna put negative one, and then we jump outside of the parenthesis and add one more day to it, which will get me to the first day of the current month, which is 81 2015. So now, last but not least, first day of year, this one's a little bit trickier. There a couple ways to do it. The first way is to just copy and paste that same formula that I just wrote. But instead of looking one month in the past, I'm gonna look eight months in the past because the current month is August or eight. So this will get me the last day of December of last year, and then I'm gonna add one, which should return the first day of his current year. My press enter. That seemed to work. So I got 11 2015. Now when you're doing it at home, you may not subtract eight months, depending on what month you're looking at. So we can modify this formula to make it work no matter what your current date and current time is. And to do that, I'm gonna just replace the minus eight here. So what I need to do is subtract the current month number, which requires me to know what the month number is and then multiply it by negative one to get the right month component for this formula. So in print disease, I'm going to do negative one times months of current date and then close off all of those parentheses. So to recap what I just did now what I'm saying is, I want the end of the month. We're Mike current date or starting point of c two. But the number of months that I want to look back in time is a function of what my current month is. So this is saying if I'm currently in month eight of the year, I want to go back in time eight months and get me the last day from eight months ago. If I'm in the 10th month of the year. I want to jump back and get the last eight from 10 months ago and so on and so forth. And then again, this last piece is just attack on that one extra day to get the first of the following month. So if we press enter, there you go. We get the same response 11 2015 and that will now return the first day of the year, regardless of what your current date and time is. So as you can see, E O month is a really useful function. You can use it to do a number of very creative things, and it's a pretty critical tool for Excel users working with dates and times because there's really no simpler way to do it. Given the fact that the number of days per month varies, this kind of automates a lot of that tedious manual work that you'd have to do otherwise. So there you go E O month function 7. The YEARFRAC Function: the next function that I want talk about. It's called year frack, which calculates the fraction of the year represented by the difference between two dates. So, typically, this is used to compare the first date of a given year versus the current date to show what percentage of the year you are through. But you can do it for any two dates and look it. Basically, anyone know you choose so syntax wise, three components to this formula you got your start date, your end date, and your basis basis just allows you to specify the type of day count to use or, in other words, how you're defining the number of days in a year. Um, zero is the default. I'd actually recommend putting a one here, which looks at the actual number of days in the actual year that you're in based on your start and end dates. So that will account for things like, uh, leap years, which might have one additional day 366 versus 365 for the year before or the year after. So here's an example. If you've got a start date in Selby 211 2015 and an end date in cell B three 2 28 2015 Here to example, outputs in the first case we're using the one basis, which is actuals. And that's telling us that on February 28th 2015 were 15.9% of the way through the year on . And that's through the actual year. 2015 if we change the basis to to that percentage changes slightly to 16.1, because now it's based on a 360 at year basis. So pro tip here year frack is a really great tool for things like pacing and projection calculations. Let's take a look at Excel and basically we're just gonna use the data that we've already input here to calculate the percent through the year of our current date. So I'm going to do equals you're frack, open the parenthesis, our start date going to use the first day of the year, which we calculated in the last lecture and then the end date and see to, and then my basis is going to be one. When I close that out, I get 63%. So that's telling me that today on the 18th of August. I'm 63% of the way through the year. So there you go. You're frack. 8. WEEKDAY, WORKDAY & NETWORKDAYS: all right, so let's talk about three more date functions or in talk weekday, workday and network days. So starting with weekday really valuable one. It shows you which day of the week a given date falls on, and there are two ways you can do this. The first is just to use a custom cell format like we talked about in lecture to, So you can use either three DS to get abbreviated day of week or four D's to get the full day of week and again note that this isn't actually changing the value or creating any kind of new value. It's on Lee changing how the original date value is being displayed to you. The second, which is the approach that I'd recommend, is using the week day function, which actually returns a serial value corresponding to a particular Dave that week, either one through seven or zero through six. So this syntax is pretty simple. You've got your serial number first, which is sell that contains a date or a time and then your return type, which is optional where zero is your default, which assigns a number one to a Sunday, up to number seven, which is Saturday. Um, if you prefer to call Monday the start of the week and assigned one to Monday, you could put a one here instead of a zero or instead of leaving it blank eso a really useful function, especially when it comes to doing things like Day of Week analyses where we want to see how performance or data differs, UH, Tuesday versus a Friday versus a Wednesday and so on, Um, work day and network days. So work day, to be totally honest, is a little bit confusing. Basically returns a date that is a specified number of days before or after a given start date, excluding weekends and holidays, if you choose to add those. So the most common application that I can think of is to say what date falls 50 work days from today. So once I get through 50 workdays, which doesn't include weekends, what date I'm gonna land on And so sin tax wise, it starts with your start date, then goes to the number of days. You can either put a positive value here if you want to look forward in the future or negative value. If you want to look back in the past. Those air all the required components. There's an optional holidays component that lets you reference a list of holidays, and it will essentially exclude those from the number of working days that it's calculating . So that's Work Day. Network Day, which actually find much more useful and more practical, shows you the number of work days between two dates. So you and put a start date on end date and an optional reference to a list of holidays, and it will tell you how many workdays or working days fell between those dates. So two examples here I've got the same to date references that I mentioned in the last lecture, which are January 1st 2015 as our start, February 28th 2015 as our end. So if we say workday, be too common 20 that saying from the start date of January 1st 2015. What date falls 20 work days after that, and it will return the date January 29th 2015 if we use the network days function and set B two is our start date, and B three is there ended. It will tell us that between January 1st and February 28th 2015. There were 42 working days, which is the total number of days minus all of the weekends. So let's jump into excel and see kind of how this works in practice. So in F three, I've got a project due date cell, and I'm just gonna make up some date. Far in the future, that means nothing. So let's say may 9th 2017. If this is a big project, it's gonna take a lot of time. Days until do this is really simple. Since date values air really just values that I can perform any normal operation on just like I would any other numbers, I'm just gonna say the due date minus the current date now returns 6 30 So have 630 days between August 18th 2015 and May 9 2017. So that's the total days until do now. If I want the number of work days until do obviously I'm not going to spend my weekends working on this project. I can use network days, so net workdays start date is current date. 18th end date is the due date. So if I leave the optional holidays field blank. Close that parentheses. Impress center. That's telling me that well, I have 630 total days until it's do I only have 451 working days until it's do so for the holidays piece. What I can't do is just type in a number of holidays like five and say OK, exclude those five holidays. You'll just return the same value of 4 51 What it needs here is an actual reference to a list of dates that define the holidays that I want to exclude eso what I can do just for the sake of an example. Let's say 12 25 2015. I want take off Christmas and I want to take off January 1st 2016. And those are the two holidays between today's date and may 9th 2017 that I plan to take off and not work. So what, I can do it say OK, instead of putting a five there, I'm gonna refer to age 536 which is my list of holiday dates and press enter, and now you can see it updated to 4 49 so it excluded these holiday dates as well. Note that if you have a holiday that also falls on a weekend, it would have already been excluded here. But in this case, both of these dates are weekdays, so they were being counted in the 4 51 total. And so when I add that reference in, it excludes those two and leaves. Move 449 working days until the project is due. Last but not least for day of week again, I could just right click and going to format cells on the actual date field and say, Let's just do a custom delete this type line and put Dee dee dee dee. Okay, it's a Tuesday, but I want to continue to show the date in its current format. And just add the day of week separately here. So I'm gonna use the week day function equals week, day of the serial number, which is F three. I'm gonna leave the return type default, so I get a three. And when I format the three that can change that to custom de de de de and there you go gives me Tuesday. So may 9th 2017 is a Tuesday. So there you go. There's weekday workday and network days 9. The DATEDIF Function: So the last function that I want to talk about in this section is called date def. And it calculates the number of days, months or years between two given dates. So three components of the formula syntax starts with the start date, then you and put an end date. The third piece is the unit. So this is determining how you want to calculate the difference between the two days. You can set a d here to calculate, as the number of days between the dates M would return the number of months between dates. Why would return the number of years between dates? There also options like M d y d or why I m. Which will calculate these differences regardless of months or regardless of years. So two examples here again, we're looking at the same start and end dates that we used in the previous two examples. January 1st 2015 Zehr start February 28th 2015 is our end. So if we set the date diff between her start date and be to on our end date and B three and said the unit two days, it will return 58. Since they're 58 days between these states and note that this is functioning kind of like a network days function except the network days excluding weekends. This is returning all of the dates and also note that, uh, this is doing the exact same thing as just writing the formula B three minus B two. So pro tip. If you only need to calculate the number of days between dates, just use simple subtraction. You don't need to use a date if function for that. But where the date did function becomes much more valuable and where it can't be replicated . Using simple operations is if you want to calculate a difference in terms of months or years, or someone and so forth. So if we want to say date diff between January 1st and February 28th with the M D unit, which is the number of days between dates ignoring months and years, it's going to say they're 27 days between the 28th and the first. Regardless, if I'm looking at a different month or a different year, let's take a look at Excel and practice this. So to revisit this project due date that we had written in the last lecture. What I can do here is, if I want to calculate the months remaining until my project is due, I can type equals date deaf. And then I don't know why this is the case, but Excel doesn't help you through this function. It just kind of leaves you hanging. Not sure if it's a bug or what, but if we rely on our memory, we know that the first component of the date, def, is the start date, which is my current date. And then the end date is gonna be the due date. And then the last piece is the unit, Remember? So in this case, I need the months remaining, So I'm gonna put an M in quotes. And this won't work unless I put the M in quotes here. So just keep that in mind. And then when I hit enter, that tells me that I have 20 months remaining from the current date through the date that my project is due and then similar case here, what I'll do instead of writing this from scratch, I'm just gonna fix the references in my original functions of fixed the C two and fix the F three. Copy that formula, paste it down and change the M to a Why, since I want the years remaining. So it's telling me that I've one year remaining until my project is due. I could also jumped back to sell F five, where had calculated my days until do as just f three minus C two and let's see if we can get the same answer. 630 using a date did function. So I'm gonna do date deaf C two comma F three comma de in quotes and there you go 6 30 so again two ways to do the same thing and I don't know about you, but I would prefer to just say F three minus C two and call it a day. But date if can be a great option, especially if you want to aggregate the difference between two dates at the month or year level 10. PROJECT SHOWCASE: Designing a Sample Budget Pacing Tool: All right. Welcome to the end of section seven. Congrats on making it through. We talked about a ton of different, useful date and time functions, and now it's time to put some of them into practice. So what we're gonna do is kind of step by step walk through this project that incorporates a bunch of the different dating time functions in tandem with some of the functions that we learned earlier in our training. So let's do it. Um, first thing we need is the current date and see to and contrary to what you might be thinking, I actually don't want to use the today function here. Um, it's a little bit misleading, but I don't want to return what day it is today. I want to return the latest day in my data set. So if you recall this sample data has expense stated by day going down toe may 16th. So if I don't have expense data after that and I type today, which is in August, this tool isn't gonna be very useful. So instead I'm going to use a max function. I just want to return the max date from column A of my sample data, Ted, Now we'll return 5 16 The other benefit of doing this is that when you're working through this exercise on your own at home and you use the today function, it's not gonna jive with sample data and it won't really make much sense. So for now, just use the Mac's function. And if you do end up building one of these or customizing one of these for your own use, you can change it as you see fit. I'm so start Date is gonna be the first day of the current month on again current meaning latest data in our data set. So I'm going to use the EOE Month function here. Start date. It's gonna be C two and remember that to get the first date of the current month, I need to take the last date of the previous month so minus one plus one day, that gives me 51 2015 which is exactly what I want now for percent of month. This one seems like it should be really easy, but it's actually a little trickier than you would think because there's really no month frack function. Uh, you know, year. Frack is really useful because you could just say Tell me the percentage through the year that I am There isn't an equivalent for month frack and the challenges that each month has a different number of days. So I know that I'm 15 days of the way through May. But do I divide that by 30 days or 31 or 29? So what we need is a useful little table, which I've conveniently added in here in cells B 24 through C 35 which tells me the number of days in each month based on the month number s so I can use that to calculate my percent of month field equals my current date minus my start date. And they surround that peace in parentheses. That's the number of days through the month that currently am. And I'm gonna divide that by the total number of days in the month. To get that, I need to use a V look up function and the look up value is gonna be the current month, which is the month of my current date. So month of C two will just return five and five is the look up value that I'm looking for in this table. All right, so it's gonna look for five, and then I would return the value in the second column over. That's where my number of days live. And then it's gonna be an exact match. So finish it with a zero close. The parenthesis and hit enter now returned 48.4% telling me that on the 16th of May, I was 48.4% of the way through the month. Um, and again, just to recap what this formula is doing, it's calculating. The number of days have gone through May divided by the number of days in May. And it finds that by searching for the month of my current date down in this table array and returning the value in the second column over. So that's that piece. The next piece is all about my monthly budget and my monthly expenses. So the monthly budget is a user entered figure, and that's whatever you want. So let's say your monthly budget is $2500. That's just kind of set in stone now to calculate monthly expenses. There's one step we need to take because our expense data is at the daily level and we're looking to roll it up at the monthly level. So we need to create a new column column, a called month, that rolls this data up at the month level. So it's just gonna equal month of the date beat, too, and then apply down, and that will show me. OK, month number one for all the January dates to for February 3 for March for for April and five through May. And now that I have this additional dimension, um, I can use a some ifs formula to roll up my expense totals at the month level, not just at the day level. So if you recall the some ifs from the statistical Functions section, we're just going to start with some ifs, open parenthesis, and then the some range is basically where the data lives. That I'm trying to aggregate is the Expenses Column column. See my sample data 10 and then the criteria range is basically the month number because that's the criteria that it needs to meet toe aggregate these expense totals so the criteria range is calling A and my criteria is the month of the current date. So remember what I'm gonna need is a function that will return the number five here. And so it's just gonna be months of monthly budget pacing tool cell C two and then close those two parentheses. And there you go. So when I hit enter it returns. 16 03 Which is telling me that all of the expense data in the month of May is totaling 2 $1603. I can check that by scrolling down to the May data and just looking at the sun 16 03 So that's working properly. Um, if I wanted to make these formulas a little bit easier, what I could also do is just in another cell. I could just write that month function to return the five and then in my some ifs, rather than nesting the month function there, I can just basically refer to that cell where have already put them on function and get the same response. So a few different ways to do it. It kind of depends on specifically what you're looking to do on now. Percent of budget is just gonna be what I've spent divided by what my budget is So 64.1%. Um, now, here you'll see that I've added the framework for chart. I'm so click on that, and we're going to right click to select the data. And I know this training isn't on charts and graphs, So apologies if this is tough to follow, um, I will be putting out, of course, specifically covering charts and graphs in the near future. So stay tuned for that. But in the meantime, just try to follow along step by step as we build this out. So I'm gonna add a Siri's. Siri's name is gonna be called percent of month in a Siri's values is just gonna be cell c for just telling me the percent of the month hit. Okay, add one more. Siri's. The name of this Siri's is gonna be percent of budget, and the Siri's values is just gonna be sell f four it okay, and then we hit. Okay, again. As you can see, it's created chart for me. That's showing the percent of budget shown in orange and the percent of month shown in blue . Um, now one more piece that I'm gonna do here in this cell, I have emerged cell in each to what I'm gonna do. It's basically just calculate the difference between the two percentages. So it's just gonna be equal to F for minus C four. So that's telling me that I have spent 15.7% points above what I'm pacing for the month. So what I can do now just to add some bells and whistles is going to conditional formatting highlight cell rules and say, Okay, if this number is greater than zero, it means percent of budget is greater than percent of month, meaning we're over pacing, which is a bad thing. I'll format that with a light red filled with dark red text, which is one of the default options and hit OK and then also at a rule where, if it's less than zero, meaning were under pacing, we'll do green fill with dark green text and hit OK, so now if my monthly budget was $5000 now let's send them under pacing by 16.3% points so you can kind of play with it, you know, to see how the chart updates and then last, but not least, if I wanted Teoh, you know, add some more flair to it. I could add a logical operator here that says, uh so equals. If H two is greater than zero, then I want this to say over pacing. Otherwise, under pacing, Close the Francis So that will just add a little label there under my conditionally formatted. So show me all right, I'm under pacing now, but if my budget again was 2500 now, I'm over pacing. So there you go. It's Thea, a great example of how to combine a number of different functions from a number of different formula categories to create something that's actually useful. Last thing to speak to is that this tool is great because it will update kind of on its own as you add more data. So let's say like another week goes by. You know you can drag this down as you can see the fill Siri's defaulted toe adding days for each row. Um, I could just drag my month formula down and then say OK spent $90 on the 17th 114 on the 18th and 75 on the 19th And when I jump back to my tool, as you can see, it's updated. So now the current date is the 19th since two max function. I'm 58.1% and have 75.3%. They're my budget factoring in those additional days that I just added. So we spent some time building out the front end, but now it's pretty much automated to use very easily moving forward. So that wraps up dating time functions. I hope you found it useful. Stay tuned for the next section, which is formula based formatting rules. 11. HOMEWORK: Date & Time Functions: All right. Congrats on wrapping up Section seven. Date and time functions. If you want to get some extra practice in. Got some good demos for you in the Excel homework exercise file. So go ahead and open it up. Head to the date and time functions tab. And here we've got seven steps. Essentially, you're gonna practice using all of the functions we covered in this chapter today. Now weekday workday, Network day, end of month on Phil Siri's. So go ahead and give that a shot answer file is available in the course. Resource is or just shoot me a message if you need support. Good luck.