Specific Solutions
First Tuesday of the Previous Month
Inspiration
In this , a user wants a formula that outputs the first Tuesday of the previous month. Let’s tackle this in two stages. First get the first day of the previous month, then find the first Tuesday of that month.
The First Day of the Previous Month
Here are two different ways to get the first day of the previous month. Pick the method that makes the most sense to you.
Subtract Days Method
The original poster put together a formula that gave the first date of the previous month. However, that formula unnecessarily produced a text string that looked like a date, not an actual date object.
Here is a formula with the same basic logic, but formatted across multiple lines and without turning the date object into a text string.
This formula subtracts days from the date to get to the first of the current month, then subtracts an additional month.
DATETIME_PARSE() Method
This formula creates a text string representing the first of the month, using the MONTH() and YEAR() of the {Date} and hard-coding 1 as the day. Then the text string is parsed into a date object.
Finally, at the outermost level of the formula, adding negative one month to the parsed date results in the first of the previous month.
The First Tuesday
Here are two different ways to get the first day of the previous month. Pick the method that makes the most sense to you.
SWITCH() method
To get the first Tuesday of the month, use WEEKDAY() to find the day of the week for the first of the month.
If the month starts on a Sunday, add 2 days. If the month starts on a Monday, add 1 day. If the month starts on a Tuesday, add zero days. If the month starts on a Wednesday, add 6 days, and so on.
I prefer this method to the next method because I find it easier to read. I also add 0 days versus not adding any days because it keeps the parallel structure of the formula, and makes the formula easier to adjust if you want a different day of the week.
Because the formula for the {First of Previous Month} is used so many times in this formula, I prefer to keep it in its own formula field instead of trying to combine both formula fields into one.
More Math Method
This formula has fewer lines because the number of days to add is embedded in the IF() function. If the first of the month is Tuesday or earlier, subtract the current numeric day of the week from 2. Otherwise, subtract the current numeric day of the week from 9.
I personally find this formula harder to read.
Thank Kuovonne for creating this content!
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.