Hi, this is Gary with MacMost.com. Let's look at how to calculate some difficult dates in Numbers.
MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to
MacMost.com/patreon. There you can read more about
the Patreon Campaign. Join us and get exclusive content and course discounts.
I'm often asked about how to do calculations in Numbers to get specific dates. Like for
instance the last Wednesday of the month or the third Thursday of the month. There's no
actual function in Numbers that will let you simply calculate these. But you can
use the existing functions, with some complex formulas, to get these dates.
So let's start off with something simple. Let's calculate the last day of the month.
To do this first we want a sample date. This could be any date of the month. We're just
going to be taking the month and year from the day. You could see here I just entered
September 15th. The actual value is September 15, 2022. The calculation for the end of the
month is actually a function in Numbers. If we enter a formula and then look in the
functions here and search for eom, eomonth, end of month. This takes any date and then
a month offset. So let's give it a try. eomonth and then let's use the sample date
and then we'll do a month offset of zero. So use the current month. This will give us
the last day of September 2022. The offset can be used to do something like find the last day
of the next month. Or a negative value for the last day, say, of the previous month.
To show these formulas I'm going to put them here using the special formula text
function. Give it a cell reference and it will output the formula as text. So now we can see
exactly what it is in the cell without having to click it and look down below.
Now let's calculate the first day of the month. So one way to calculate the first day of the month
is to use that same eomonth function. Take that date but do the month offset as negative 1. In
other words calculate the last day of the previous month. Then add 1. Whenever you add a raw value
like 1 to a date it assumes you mean days. So, we get September 1st. It's actually calculating the
end of the month for the previous month and then adding 1 to it to get the first day of this month.
But there's another way to do it. We can use the Date Function. We look up the Date Function
we see that the date takes three parameters. Year, Month, and Day.
So, we'll use the Year function to extract the year from the sample date. Then we'll use the
Month function to extract the month from the sample date. But we don't care about the day of
the sample date. We just want the first day of the month. So we'll use 1 for the value of the
day. This will calculate the first day of the month. But it will do it using date rather than
end of month. This will come in handy when we do more complex formulas in a minute.
Now to do things like calculating the third Wednesday or the last Friday of a month we're
really going to have to get a grip with what a day of the week means in relation to the date.
There's one function that handles that. That is the Weekday Function. So we'll use weekday
and let's look it up here.
We see weekday takes the date and a value for the first day of the week. We won't
actually even use that. We're just going to always assume we want to do Sunday as
the first day and Saturday as the 7th day. So we'll feed it this date here
and then we get the result of 5. The 15th is actually on a Thursday. So if Sunday is 1,
Monday, Tuesday, Wednesday, Thursday= 5. So we can combine using end of month function and the
weekday function to actually figure out when, say, the last Wednesday of the month comes.
So to do that let's first start off by figuring out what the offset is between the last day of
the month and whatever the day we want to figure out is, like Wednesday. So we can start off with
weekday and instead of just the sample day we actually want to use the last day of the month. So
eomonth and we'll do the sample day of that. We'll do a month offset of zero so the current month
and now we're going to get the weekday for the last day of the month.
That's a 6 which would be a Friday. Indeed, September 30th does fall out on a Friday. Now we
want to subtract from that the day of the week we want to find which is a Wednesday. Sunday, Monday,
Tuesday, Wednesday, the 4th day. So subtract 4. So we get a 2. So if we take the last day
of the month, which is a Friday, and we shift it by 2, we'll get the last Wednesday of the month.
But that is not always going to be the case. Sometimes you end up with the last day of the
month being, say, a Monday and if you try to find Wednesday is going to be after Monday. So what we
want to do there is use the Mod Function. The Mod Function keeps numbers within a range. Basically
gives the remainder when you divide. So if you do Mod 7, for instance, you'll get value zero through
six and then the next value would be zero again through six, and then zero through six. The same
thing happens if you go in the opposite direction. If you get too small, if you go below zero the
negative one will loop back around to six. So we always stay within the range of zero to six
instead of going into negative numbers or numbers above six when we use the 7 as the value for Mod.
So we'll do Mod for the value we get, the offset, of 7. Seven days in a week. That
will give us the same value here of 2 because in fact the last day of the month is a Friday
and we want to find Wednesday. If instead we wanted to find the last Saturday then this
wouldn't be a negative 4 it would be a negative 7 which would then be six minus 7, negative 1,
but because we do Mod 7 we get a 6 instead of a negative 1. So basically here we get a value that
will shift the last day of the month no matter if it comes before or after that day of the week.
Now all we need to do is start this off with eomonth and feed it the sample day. No shift
there. A zero and then subtract this value. So in the case of the last Wednesday of the
month it's going to subtract 2. The last day of the month is Friday. Taking away 2 it is
going to give us that Wednesday. There we go. The last Wednesday of September is the 28th.
To make this a little easier to see I'm actually going to go to Numbers,
Preferences, and turn off Use Header Names as Labels. Now you'll see references like
B6 rather than using the sample date and last Wednesday of the month labels there.
So what if we wanted to adjust this and instead of getting the last Wednesday of the month get
the last Sunday of the month. Well I'm just going to Copy this function in here and all
you need to do is change this value. Instead of a 4 for Wednesday what I need is a 1 for Sunday.
Now I can see the last Sunday of the month is September 25th. So even though the last day of
the month is a Friday it will look back to the previous Sunday, not forward to the next Sunday.
What about the first Wednesday of the month. Well, that actually is a longer formula here.
You're going to want to use the Date Function and then extract Year and Month just like we did
up here to get the first day of the month. Then you're going to want to add to it what we did here
before. Getting the weekday and placing inside of Mod except we want to do the opposite. Instead of
subtracting 4 we want to start with 4 and subtract the weekday because it is the beginning of the
month rather than the end of the month. But we're still doing the Mod using 7 here. So the formula
looks like this now. It's a longer formula because a lot of the space is just taken up by date with
the year and month, 1. You can see that's there twice. Once inside of the weekday to calculate
the day for the first day of the month and one to actually start with the first day of the
month because we're adding to it. Then this will correctly calculate the first Wednesday of the
month is September 7th. To change this to be the first Sunday we want to go to the 4 here, instead
of Wednesday make it 1 for Sunday. Then we'll get the first Sunday of the month is September 4th.
Finally, that takes us to calculating something like the third Wednesday. To do that, really,
we just need to take the first Wednesday. So I'll Copy this formula here. But what we'll do to that
first Wednesday is add 2 weeks to it. So plus 14 or 2x7 if you want. So adding two weeks to
the first Wednesday gets you the third Wednesday which is September 21st. If you wanted to do the
second Wednesday you would just add 7. If you wanted to do the fourth Wednesday you would add
21. That gives you a nice set of formulas that you can use to easily calculate things like the
first and last day of the month and the last specific day of the week for the month, or the
first day of the week for that month or the 2nd, 3rd, or 4th day of a week for the month. All using
the end of month function, the date function, the weekday function, and a few other support
functions like Mod, Year and Month thrown in. Hope you found this useful. Thanks for watching.