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.



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: 3 Responses to “Calculating Difficult Dates In Mac Numbers”

    Adam Love
    8 months 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?

    8 months ago

    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.

    JL
    8 months ago

    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. ;)

Leave a New Comment Related to "Calculating Difficult Dates In Mac Numbers"

:
:
:
0/500 (500 character limit -- please state your comment succinctly and do not try to get around this limit by posting two comments)