How to find first Monday from given date?

Question

I'm trying to find the first Monday from a given date, and so far I've come up with the following formula, but it returns the first Monday of the month for the given date. Could you help me fix it, please?

DATE(YEAR({!var_StartDate}), MONTH({!var_StartDate}),
  CASE(MOD(DATE(YEAR({!var_StartDate}),MONTH({!var_StartDate}),1) - DATE(1900, 1, 7), 7),
    0, 2,
    1, 1,
    2, 7,
    3, 6,
    4, 5,
    5, 4,
    6, 3,
  0)
)

Answer 1

The primary reason for that is that your formula is currently, literally being told to start from the first of the month

/*                                                    v This right here */
DATE(YEAR({!var_StartDate}), MONTH({!var_StartDate}), 1) ...

In general, formulas of this type are saying the following:

• Give me a date with a given year and a given month
• To determine the day to use...
  • Start with the first of the given month and year, and find the difference between that and a date that we know is a Sunday
    • If the remainder of that number divided by 7 is 0, then we know the first Monday is the 2nd of the month
    • If the remainder is 1, then it's the 1st of the month
    • If the remainder is 2, then it's the 7th of the month
    • ...and so on

That's useful in some situations, but I have two main issues with it:

1. The values of the CASE() (2, 1, 7, 6, 5, 4, 3) seem a bit like magic numbers. It's not immediately evident to me why those numbers are used in that order. It's not that hard to discern why, but it does take time and does impose a cognitive load.
2. There appears to be unnecessary work being done

Shifting the approach

Instead, I think it's more helpful and more intuitive to approach this from the angle of how many days do I need to add to get to a Monday?.

If the remainder between your date and a known Tuesday is 0, then your date is a Tuesday and we need to add 6 days
If the remainder between your date and a known Tuesday is 1, then your date is a Wednesday and we need to add 5 days
and so on

In the "first Monday of the month" example, that changes the formula to

/* First day of month */
DATE(YEAR({!var_StartDate}), MONTH({!var_StartDate}), 1)
+
CASE(
    /* Remainder in # of days between our chosen date and a known Tuesday */
    MOD(
        DATE(YEAR({!var_StartDate}), MONTH({!var_StartDate}), 1) - DATE(1900, 1, 9), 
        7
    ),
    /* Tuesday, so add 6 days */
    0, 6,
    /* Wednesday, so add 5 days */
    1, 5,
    /* Thursday, so add 4 days */
    2, 4,
    3, 3, 
    4, 2,
    5, 1,
    /* The only other target left is 6, which we can just ignore and */
    /*   use as our default (adding 0 days) since we need to specify */
    /*   a default anyway                                            */
    0
)

Another notable benefit of taking the "how many days do I need to add?" approach is that you don't need to worry about month and year boundaries. Leap days are similarly a non-issue.

Since you're not looking to do this from the start of a month, you can actually save some typing here. You can just use {!var_StartDate} instead of explicitly making a new date with the DATE() function.

But this is still doing more work than you need to do

The help page on Formula Functions and Operators is always a good thing to reference. By looking through it for something that might be useful, you should find the WEEKDAY() function.

That can completely replace the MOD(<date1> - <date2>, 7) part of the formula. The only real gotchas are that WEEKDAY() goes from 1 to 7 (instead of 0 to 6) and a result of 1 means you're on a Sunday

DATE(YEAR({!var_StartDate}), MONTH({!var_StartDate}), 1)
+
CASE(
    WEEKDAY(DATE(YEAR({!var_StartDate}), MONTH({!var_StartDate}), 1)),
    /* Sunday, so add 1 day */
    1, 1,
    /* Monday, so add 0 days */
    2, 0,
    /* Tuesday, so add 6 days */
    3, 6,
    4, 5,
    5, 4,
    6, 3,
    7, 2,
    0
)

You could add 4 and bring back the MOD(x, 7) to shift things so that 0 = Tuesday again so the CASE() values are always decreasing. That would be mostly just a quality-of-life thing. At any rate, having a helpful comment in your formula should help decrease cognitive load for the next person who looks at it (which could be you in 6 months, after you've forgotten why you did it this way)

DATE(YEAR({!var_StartDate}), MONTH({!var_StartDate}), 1)
+
CASE(
    /* WEEKDAY gives us 1-7, with 1 = Sunday and 3 = Tuesday */
    /* Add 4 to that and MOD 7 so we get 0-6 with 0 = Tuesday */
    /* That allows us to always add fewer days (to get to a Monday) */
    MOD(
        WEEKDAY(DATE(YEAR({!var_StartDate}), MONTH({!var_StartDate}), 1)) + 4,
        7
    ),
    /* Tuesday, so add 6 days to get to Monday */
    0, 6,
    1, 5,
    2, 4
    3, 3,
    4, 2,
    5, 1,
    0
)

I'll leave the final part (swapping out the DATE() bit) as an exercise to you.