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.
Check out Calculating Difficult Dates In Mac Numbers at YouTube for closed captioning and more options.
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.
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?
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. ;)