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:



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

    Adam Love
    1 year 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?

    1 year 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.

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

    D. Sands
    3 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

    3 months ago

    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.