Calculating Difficult Dates In Mac Numbers

Learn how to calculate dates like the first or last day of a month, the first or last day-of-week of a month, or the nth day-of-week of a month.

Video Transcript: 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.

Here are the formulas used in this tutorial:

=EOMONTH(B2,0)
=EOMONTH(B3,−1)+1
=DATE(YEAR(B4),MONTH(B4),1)
=WEEKDAY(B5)
=EOMONTH(B6,0)−MOD(WEEKDAY(EOMONTH(B6,0))−4,7)
=EOMONTH(B7,0)−MOD(WEEKDAY(EOMONTH(B7,0))−1,7)
=DATE(YEAR(B8),MONTH(B8),1)+MOD(4−WEEKDAY(DATE(YEAR(B8),MONTH(B8),1)),7)
=DATE(YEAR(B9),MONTH(B9),1)+MOD(1−WEEKDAY(DATE(YEAR(B9),MONTH(B9),1)),7)
=DATE(YEAR(B10),MONTH(B10),1)+MOD(4−WEEKDAY(DATE(YEAR(B10),MONTH(B10),1)),7)+14

Download: Dates.numbers.zip.

Comments: 5 Responses to “Calculating Difficult Dates In Mac Numbers”

Adam Love

2 years ago

Hello Gary. Thanks for covering topics beyond the usual scope of official documentation, especially the use of the MOD function. I was just about to start work on a project involving complex dates and these examples cover exactly the kinds of things I will be doing. Using Numbers on an iPad, how do I set the cell references to Reference-tab letters and numbers instead of column names by default?

Gary Rosenzweig

Adam: I don't think that option exists on iPadOS. Remember that the reference is the same, it is just how it looks to you. And you can still type the column letter+row number if you like.

Thanks Gary! Definitely useful. I'm wanting to advance more in using Numbers, and this helps me a lot. I need to study a bit more on the MOD function. ;)

D. Sands

7 months ago

Can you suggest a Numbers formula that takes a start date, adds a number of days, and returns the new date, then checks if the new date falls on a Saturday or Sunday, and if so, returns the next workday / Monday

D.: A little tricky. Just adding a number to a date is easy. Just add a number. So if B4 is the date, then B4+3 will get you three days later. But you'll need to embed that in two nested IF functions, the first checking the WEEKDAY function for a Sunday and adding 1, the second checking for Saturday and adding 2. So if you wanted to add 3 days, then something like IF(WEEKDAY(B4+3)=1,B4+3+1,IF(WEEKDAY(B4+3)=7,B4+3+2,B4+3))

Comments Closed.

Calculating Difficult Dates In Mac Numbers

Comments: 5 Responses to “Calculating Difficult Dates In Mac Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information