Workday results for next year result in blue warning

Thinking about it, you may need to add the EDATE() to the YEAR() function, too.

As written, the YEAR() is returning the year from column B (2025), whereas you want the year from that date one month later. Adding EDATE() to that year field will return the whatever year the next month falls in.

If you're running Numbers 14.4, with its support for the LET() function, you could simplify this a little:

In this example, LET creates a new variable called next_month which is calculated by EDATE(A2,1) - that is, one month later than the date in A2.

This variable is then used in a DATE() calculation to determine the new variable due_date. This, in turn, is passed to WORKDAY() to find the next working day (I omitted your non-working days, but figure you can add that back in).

LET() makes it easier to work with complex nested functions.

Thanks, @SGIII.

I think LET() has three main use cases where I find it useful.

First is cases where you might previously have used a dummy/hidden column to calculate an intermediate value. In this case, I could have used a hidden column to calculate the 'next month' value, but using LET and storing it in a variable within the formula is neater.

The second is where the formula is repeating some function - in this case, the formula was calculating the YEAR() and the MONTH() of an offset date. The typical way of doing this would be:

=DATE(YEAR(EODATE(original_date,1)), MONTH(EODATE(original_date,1)),14)

so here the EODATE() function is called twice, once for the year, and once for the month. It's more efficient to call EODATE() once and store the result. Any time a sub-function is repeated within a formula, it's a good candidate for LET().

The third case is nested functions where the (IMHO abysmal) spreadsheet syntax really makes it hard to read - I'm thinking especially of things like AND(OR()) conditions that don't follow a natural-language flow.

In these cases, it's often easier to read when the nested functions are broken out into variables.

Now, instead of:

=IF(AND(OR(B2>$B$1,C2>$C$1,AND(E2<$E$1,F2>$F$1),TRUE,FALSE)

You can say something like:

=LET(option_1,OR(B2>$B$1,C2>$C$1),
     option_2,AND(E2<$E$1,F2>$F$1),
     IF(AND(option_1, option_2),TRUE,FALSE)

Here the various IF(AND()) components are broken out into their own statements, and the actual TRUE/FALSE logic is much easier to read.

The error makes sense when you think about it...

Your formula is trying to create a new date by adding 1 to the current month.

When the Month is December (12), adding 1 results in month 13... hence the error..

The blue triangle is trying to tell you this day may be wrong because of this wrap (in fact, Numbers gets it right in this instance, but it still warns you)

In either case, the solution is simple - instead of simply adding 1 to the month, use EDATE() to calculate the next month.

Replace

MONTH(B11)+1

with:

MONTH(EDATE(B11,1))

This adds '1 month' to the date in B11, which will handle the wrap-around at the end of the year.

Great explanation! Bookmarking this.

SG

Upvote for Camelot! A great example of using the new (to Numbers) LET function in the wild. Beginning to understand better how it can be useful.

SG