Determining the Last Business Day of the Month Using Excel Formulas

Understanding specific dates within a month, such as the last working day, is essential for many applications and tools. Whether you’re planning tasks, managing a calendar, or building sophisticated scheduling apps, knowing how to pinpoint these dates can be indispensable. In this article, we delve into the Excel formulas to help you ascertain the last weekday of any month.

Essence of Determining the Last Weekday

Recognizing the last workday of a month can be the cornerstone for many scheduling and planning applications. Consider creating recurring events for specific days in a month, such as the final Friday or the last Monday. This capability can be incredibly valuable.

Universal Formula: Retrieving the Last Thursday of a Month

Let’s take August 2021 as a reference. The formula to extract the last Thursday looks like:

    With({SubsequentMonth:Date(2021,9,1)},
         DateAdd(
                 SubsequentMonth,
                 -1 * Weekday(DateAdd(SubsequentMonth, - 5))
         )
    )
    

Here, the digit 5 signifies Thursday, considering Sunday as the starting weekday (1).

An accompanying screenshot would validate the result, pinpointing the last Thursday of August 2021 as 26th August.

Unraveling the Methodology

We commence with the inaugural day of the succeeding month and estimate the necessary days to backtrack. For instance, for the last Thursday of August 2021, we backpedal six days from the 1st of September. This backtracking is computed by deducting the desired weekday from the subsequent month’s first day and subsequently invoking the Weekday function.

Formulas for Final Weekdays of the Present Month

Below is a consolidated list of formulas to fetch the date of the last Monday through Sunday for the ongoing month:

• Last Monday:

        With({SubsequentMonth:
                 DateAdd(
                         Date(Year(Now()),Month(Now()),1),1,Months
                 )
              },
              DateAdd(
                     SubsequentMonth, -1 * Weekday(DateAdd(SubsequentMonth, - 2))
              )
        )
        

• Last Tuesday:

        With({SubsequentMonth:
                 DateAdd(
                         Date(Year(Now()),Month(Now()),1),1,Months
                 )
              },
              DateAdd(
                     SubsequentMonth, -1 * Weekday(DateAdd(SubsequentMonth, - 3))
              )
        )
        

Computing Last Weekdays for an Entire Year

We can augment this approach to generate a table that enumerates all the concluding weekdays for every month in a specific year. The following formula demonstrates this for the year 2021:

    
    ForAll(
        Sequence(12),
        With(
                {
                    SubsequentMonth: DateAdd(
                        Date(2021,Value,1),1,Months)
                },
               {
                   MonthNumber: Value,
                   ConcludingMonday: 
                          DateAdd(SubsequentMonth,
                                  -1 * Weekday(DateAdd(SubsequentMonth,- 2))
                          )
                   
               }
        )
    )
    

If you’re employing this formula for a data table control, an accompanying visualization would manifest the outcomes distinctly.

Conclusion

Determining the last business day of a month using Excel is a prevalent need in various fields. The formulas shared in this guide make the task straightforward. If you have queries or need further clarification or technical assistance, don’t hesitate to contact us. We’re here to help and offer our expertise.