All right welcome to the end of section 7.
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 going to do is kind of step by step walk through this project that incorporates a bunch
of the different date and time functions in tandem with some of the functions that we learned earlier
in our training.
So let's do it.
First thing we need is the current date in Seat 2 and contrary to what you might be thinking.
I actually don't want to use the Today function here.
It's a little bit misleading but I don't want to return what data is today.
I want to return the latest day in my dataset.
So if you recall the sample data has expensed data by day going down to May 16.
So if I don't have expense data after that and I type today which is in August this tool isn't going
to be very useful.
So instead I'm going to use a max function and just return the max date from column A of my sample data
it's had now returned 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 going to jive with the sample data and it won't really make
much sense.
So for now just use the max 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.
So start date is going to be the first day of the current month and again current meaning latest data
in our dataset.
So I'm going to use the ego Month function here.
Start date is going to be C2.
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 1 plus one day.
That gives me 5 1 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 frac function you know year frac is really useful because you can just
say tell me the percentage through the year that I am there isn't an equivalent for month frac.
And the challenge is 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.
So I can use that to calculate My percent of Month field equals my current date minus my start date
and then surround that piece in parentheses.
So that's the number of days through the month.
I currently am and I'm going to divide that by the total number of days in the month to get that I need
to use a V lookup function and the lookup values going to be the current month which is the month of
my current date so month of C-2 we'll just turn 5 and 5 is the look of value that I'm looking for in
this table array.
So it's going to look for five and then I would return the value in the second column over.
So that's where my number of days live.
And then it's going to be an exact match so finish it with zero close parenthesis and hit enter now
or return forty eight point four percent.
Telling me that on the 16th of May I was forty eight point four percent of the way through the month
.
And again just to recap what this formulas 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 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 twenty five hundred dollars.
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 going to equal a month of the date to apply down and that will show me.
OK.
Month number one for all the January dates two for February 3 for March 4 for April and 5 through May
.
And now that I have this additional dimension I can use a Smyth's 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 function section we're just going to start with some
ifs open parenthesis and then the sum range is basically where the data lives that I'm trying to aggregate
is the expenses column.
Column C.
My sample data 10 and then the criteria range is basically the month number because that's the criteria
then it needs to meet to aggregate these expense totals.
So the criteria range is Column A and my criteria is the month of the current date.
So remember what I'm going to need is a function that will return the number 5 here and so it's just
going to be the month of monthly budget pacing tool.
Selsey to.
And then close those two parentheses and there you go.
So when I hit enter it returns 16 or 3 which is telling me that all of the expense data in the month
of May is totaling to sixteen hundred three dollars and I can check that by scrolling down to the main
data and just looking at the some 16 or 3 so that's working properly.
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 5 and then in my sum ifs rather than nesting the
Month function there I can just basically refer to that cell where I've already put the Month 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 and how percent of budget is just going
to be what I've spent divided by what my budget is.
So sixty four point one percent.
Now here you'll see that I've added the framework for a chart.
So click on that and we're into 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.
I will be putting out a 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 going to add a series series name is going to be called percent of month in a series values.
Is this going to be Selsey for just telling me the percent of the month.
It OK can I add one more series.
The name of this series is going to be percent of budget and the series values is just going to be sell
for it OK.
And then we hit okay again.
As you can see it's created chart for me that's showing that percent of budget shown in orange and the
percent of month shown in blue.
Now one more piece then I'm going to do here in this cell I have emerged cell and to what I'm going
to do is basically just calculate the difference between the two percentages.
So it's just going to be equal to 4 minus C 4.
So that's telling me that I've spent fifteen point seven percentage 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 highlights
cell rules and say okay if this number is greater than zero it means percent of budget is greater than
percent a month meaning we're over pacing which is a bad thing.
I'll format that with a light read filled with dark red text which is one of the default options here
.
OK.
And then I'll also add a rule where if it's less than zero meaning we're under pacing will do green
fill with dark green text and hit OK.
So now if my monthly budget was $5000.
Now all of a sudden under pasing by sixteen point three percentage 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 to add some more flavor to it I could add a logical operator
here that says so.
Equals if H2 is greater than zero then I want this to say over pacing.
Otherwise under pacing close the princess.
So that will just add a little label there under my conditionally formatted cell showing the item under
pacing now.
But if my budget again was twenty five hundred.
Now I'm over pacing.
So there you go.
It's 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.
And as you can see the fill series defaulted to adding days for each row.
I can just drag my month formula down and then say OK I spent $90 on the 17th 114 on the 18th and 75
on the 19th.
I want to jump back to my tool.
As you can see it's updated so now the current date is the 19th since to max function.
I'm fifty eight point one percent and I'm seventy five point three percent there 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 date and time functions.
I hope you found it useful.
Stay tuned for the next section which is formula based formatting rules