# Easy Excel Formula to Get Monday After a Date

Did you know that finding the Monday following any given date in Excel can be accomplished with a simple formula? Whether you’re planning a project, scheduling tasks, or tracking deadlines, knowing how to calculate the **next Monday** is a useful skill. In this article, we’ll explore different methods and Excel formulas to help you efficiently find the Monday after a date, saving you time and ensuring accurate planning. So let’s dive in and uncover the power of Excel formulas for finding the Monday after a date!

## Method 1: Finding the Monday After a Date

One method to determine the Monday after a given date in Excel involves utilizing a **temporary value** to identify the **day of the week**. Let’s assume the date is stored in cell A1. To calculate the **temporary value**, you can use the following formula: `=WEEKDAY(A1,3)`

.

This formula returns the **day of the week**, where Monday is represented as 0 and Sunday is represented as 6. Once you have the **temporary value** in cell B1, you can then calculate the payment date using this formula: `=A1 - B1 + IF(B1 < 4, 4, 7)`

.

The calculation subtracts the **weekday** value from the original date and adds either 4 or 7 days based on the value. If the **weekday** value is less than 4, it adds 4 days to reach the **next Monday**. Otherwise, it adds 7 days, ensuring the result is the following Monday.

Here’s an example:

Date | Temporary Value | Monday After Date |
---|---|---|

May 15, 2022 | 0 (Monday) | May 15, 2022 |

May 18, 2022 | 3 (Thursday) | May 16, 2022 |

May 21, 2022 | 6 (Sunday) | May 22, 2022 |

Using this method, you can easily find the Monday after any given date in Excel.

## Method 2: Finding the Monday After a Date

Another approach to finding the Monday after a specific date in Excel involves utilizing the TODAY function alongside the MOD function. This method provides an **alternative formula** for determining the **next Monday** after a given date.

To implement this formula, begin by using the TODAY function to retrieve the current date. This can be done by entering the formula `=TODAY()`

in a designated cell.

After obtaining the current date, you can use the MOD function in conjunction with the **WEEKDAY function** to calculate the number of days required to reach the subsequent Monday. The formula is structured as follows:

`=A1 + MOD(8 - WEEKDAY(A1, 2), 7)`

Let’s break down the formula:

`A1`

represents the cell reference containing the original date that you want to find the Monday after.- The
`WEEKDAY(A1, 2)`

segment calculates the**weekday**value (where Monday is represented as 1 and Sunday as 7) for the given date in cell A1. - The
`8 - WEEKDAY(A1, 2)`

portion determines the difference between 8 and the weekday value, indicating the number of days to reach the next Monday. - Finally, the
`MOD(8 - WEEKDAY(A1, 2), 7)`

expression utilizes the MOD function to calculate the remainder when dividing the aforementioned difference by 7. This ensures that the result is between 0 and 6, representing the appropriate number of days required to reach the subsequent Monday.

By adding the result of the MOD function to the original date, you will obtain the next Monday after the given date.

Implementing this method allows you to retrieve the next Monday after any specified date with ease and precision.

### Example:

Suppose we want to find the Monday following the date May 17, 2021. To do this, we can use the TODAY function to retrieve the current date and input it into the formula. Applying the formula `=TODAY()`

provides us with the current date, which we can then substitute into the formula to determine the next Monday.

Using the formula `=A1 + MOD(8 - WEEKDAY(A1, 2), 7)`

, where A1 contains the current date, we find that the next Monday after May 17, 2021, is May 24, 2021.

Current Date | Next Monday |
---|---|

May 17, 2021 | May 24, 2021 |

By utilizing this method in Excel, you can efficiently determine the Monday after any given date.

## Method 3: Finding the Monday After a Date

A third method involves using the **TEXT function** to extract the **day of the week** from a date in Excel. By using the **TEXT function** with the “dddd” format, you can return the full name of the day such as “Monday” or “Tuesday” for a given date. The formula is `=TEXT(A3, "dddd")`

, where A3 is the cell containing the original date.

If you prefer a shorter version of the day names like “Mon” or “Tue”, you can use the “ddd” format in the **TEXT function**. This method allows you to customize the format of the day names based on your preference.

Here’s an example:

Date | Day of the Week |
---|---|

2022-07-01 | =TEXT(A2, “dddd”) |

2022-07-02 | =TEXT(A3, “ddd”) |

In the table above, we have two dates: July 1, 2022, and July 2, 2022. The formulas in the “Day of the Week” column use the TEXT function with different formats to display the day names in full and short versions, respectively.

This method is useful when you want to display the complete name or abbreviated version of the day of the week in your Excel worksheet. It provides flexibility in customizing the format of the day names based on your specific requirements.

## Alternative Methods and Displaying the Day of the Week

Aside from the methods mentioned above, there are additional approaches to extract the day of the week from a date in Excel. One such method involves using the **WEEKDAY function**. This function returns a number representing the day of the week, with Sunday as 1 and Monday as 2. To convert the day of the week to a number, you can simply use the formula =WEEKDAY(A1). This allows for efficient calculations based on the day of the week.

If you prefer to display the day name instead of the number, you can combine the **WEEKDAY function** with the TEXT function, similar to the methods mentioned earlier. By using the formula =TEXT(A1, “dddd”), you can extract the full name of the day. For a shorter version of the day names, you can use the “ddd” format in the TEXT function. This provides flexibility in displaying the day of the week based on your preference.

Another option to consider is using the CHOOSE function in combination with the WEEKDAY function. This allows you to create custom labels or notations for the day names. By defining a list of labels and using the CHOOSE function to select the corresponding label based on the day of the week, you can create a more personalized representation of the days.

In addition to these methods, you can apply a **custom number format** to display the day names in Excel, while preserving the original date value. This provides a visual representation of the day of the week without altering the underlying data. This approach allows for enhanced readability and ease of use when working with dates and days of the week in Excel.

## FAQ

### What is the best method to find the Monday after a date in Excel?

There are different methods you can use in Excel to find the Monday after a date. You can calculate the number of days needed, use the weekday function, or even apply a **custom number format**. Choose the method that suits your needs and preferences.

### How do I find the Monday after a date using a temporary value and the weekday function?

One method involves using a temporary value to determine the day of the week for the given date. Start by assuming that the date is in cell A1. Calculate the temporary value using the formula =WEEKDAY(A1,3). The formula will return the day of the week, where Monday is represented as 0 and Sunday is represented as 6. Once you have the temporary value in cell B1, you can calculate the Monday following the date using the formula =A1 – B1 + IF(B1

### How can I find the Monday after a date using an alternative formula?

Another method to find the Monday after a date involves using the TODAY function in combination with the MOD function. The formula =A1 + MOD(8 – WEEKDAY(A1, 2), 7) can be used to calculate the number of days needed to reach the next Monday. This formula adds the result of the MOD function (which calculates the difference between 8 and the weekday value of the given date) to the original date. The result will be the next Monday after the date.

### Is there a way to display the day of the week for a date in Excel using a custom format?

Yes, you can use the TEXT function to extract the day of the week from a date in Excel. By using the TEXT function with either the “dddd” or “ddd” format, you can return the full name or a shorter version of the day such as “Monday” or “Mon” for a given date. The formula is =TEXT(A3, “dddd”) or =TEXT(A3, “ddd”), where A3 is the cell containing the original date. This method allows you to customize the format of the day names based on your preference.

### Are there alternative methods to find the day of the week in Excel?

Yes, in addition to the methods mentioned above, there are other approaches you can use to extract the day of the week from a date in Excel. The WEEKDAY function can be used to return a number representing the day of the week, with Sunday as 1 and Monday as 2. To convert the day of the week to a name, you can combine the WEEKDAY function with the TEXT function. Another option is to use the CHOOSE function in combination with the WEEKDAY function to create your custom labels or notations for the day names. Additionally, you can apply a **custom number format** to display the day names in Excel while keeping the original date value intact.

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.