Finding the last weekday of the current month can be a powerful tool in managing schedules, projects, and deadlines. Here, I’ll walk you through an Airtable formula that outputs this date dynamically using the NOW() function. This formula is straightforward and efficient, ensuring you have up-to-date information at your fingertips.

Step-by-Step Guide to the Formula

The goal of this formula is to calculate the last weekday of the current month, considering only Monday to Friday as weekdays. Let’s break down the formula and its components:

1. Get the Last Day of the Month:

DATETIME_FORMAT(DATEADD(DATEADD(TODAY(), 1, 'month'), -DAY(DATEADD(TODAY(), 1, 'month')), 'days'), 'YYYY-MM-DD')

This segment calculates the last day of the current month. It adds one month to the current date, then subtracts the number of days from the new date, giving us the last day of the month.

2. Determine the Weekday:

WEEKDAY(DATEADD(DATEADD(TODAY(), 1, 'month'), -DAY(DATEADD(TODAY(), 1, 'month')), 'days'))

This part finds out what day of the week the last day of the month falls on.

3. Adjust for Weekends:

IF(
  WEEKDAY(DATEADD(DATEADD(TODAY(), 1, 'month'), -DAY(DATEADD(TODAY(), 1, 'month')), 'days')) = 0,
  DATEADD(DATEADD(TODAY(), 1, 'month'), -DAY(DATEADD(TODAY(), 1, 'month')) - 2, 'days'),
  IF(
    WEEKDAY(DATEADD(DATEADD(TODAY(), 1, 'month'), -DAY(DATEADD(TODAY(), 1, 'month')), 'days')) = 6,
    DATEADD(DATEADD(TODAY(), 1, 'month'), -DAY(DATEADD(TODAY(), 1, 'month')) - 1, 'days'),
    DATEADD(DATEADD(TODAY(), 1, 'month'), -DAY(DATEADD(TODAY(), 1, 'month')), 'days')
  )
)

Here, we adjust the date if the last day of the month falls on a weekend. If it’s a Saturday (6) or Sunday (0), the formula subtracts the necessary days to get the last weekday.

Complete Formula

Now, let’s combine everything into one comprehensive formula:

IF(
  WEEKDAY(DATEADD(DATEADD(TODAY(), 1, 'month'), -DAY(DATEADD(TODAY(), 1, 'month')), 'days')) = 0,
  DATEADD(DATEADD(TODAY(), 1, 'month'), -DAY(DATEADD(TODAY(), 1, 'month')) - 2, 'days'),
  IF(
    WEEKDAY(DATEADD(DATEADD(TODAY(), 1, 'month'), -DAY(DATEADD(TODAY(), 1, 'month')), 'days')) = 6,
    DATEADD(DATEADD(TODAY(), 1, 'month'), -DAY(DATEADD(TODAY(), 1, 'month')) - 1, 'days'),
    DATEADD(DATEADD(TODAY(), 1, 'month'), -DAY(DATEADD(TODAY(), 1, 'month')), 'days')
  )
)

How It Works

• Calculate the Last Day: The formula calculates the last day of the current month by adding one month to the current date and then subtracting the days of the resulting date.
• Weekday Check: It checks the weekday of this last day.
• Adjustment for Weekends: If the last day is a Sunday (0), it subtracts two days to get the previous Friday. If it’s a Saturday (6), it subtracts one day to get the previous Friday.

By using this formula, you ensure that your Airtable base dynamically updates to always show the last weekday of the current month, streamlining your planning and scheduling processes.

// original 
IF(
    WEEKDAY(DATEADD(DATETIME_PARSE(DATETIME_FORMAT(DATEADD(NOW(), 1, 'month'), 'YYYY-MM') & '-01'), -1, 'days')) = 0,
    DATEADD(DATETIME_PARSE(DATETIME_FORMAT(DATEADD(NOW(), 1, 'month'), 'YYYY-MM') & '-01'), -3, 'days'),
    IF(
        WEEKDAY(DATEADD(DATETIME_PARSE(DATETIME_FORMAT(DATEADD(NOW(), 1, 'month'), 'YYYY-MM') & '-01'), -1, 'days')) = 6,
        DATEADD(DATETIME_PARSE(DATETIME_FORMAT(DATEADD(NOW(), 1, 'month'), 'YYYY-MM') & '-01'), -2, 'days'),
        DATEADD(DATETIME_PARSE(DATETIME_FORMAT(DATEADD(NOW(), 1, 'month'), 'YYYY-MM') & '-01'), -1, 'days')
    )
)