How to Round Date to Previous or Next Specific Weekday in Excel?

The WEEKDAY function produces a number between 1 and 7 that corresponds to specific days of the week. WEEKDAY, by default, assumes a Sunday-based week and allocates 1 to Monday, 2 to Tuesday, etc., with 7 given to Sunday. In order to retrieve the prior weekday occurring before a given date in Excel, we must utilize the WEEKDAY function.  In order to get the next specified weekday occurring on a certain date in Excel, we must utilize the MOD function. 

In this article we will learn how to round a date to the previous or next specific weekday.

Round Date To Previous Weekday

For finding the previous Monday, we are using simple if-else logic with a weekday function i.e., if weekday(date, 2) which returns the first day of the week. So, Monday = 1, Tuesday = 2, …, Sunday = 7, if that returned value is greater than one then return true. So, we will subtract the given date from the value of Weekday(date, 2) and add 1 to it. If the condition returns false or returned value is not greater than one, we will subtract the given date from the value of Weekday(date, 2) and subtract 6 from it.

Follow the further steps to find the previous Monday 

Step 1: Choose a cell in which to enter the following Monday date. To calculate the previous Monday date, we may apply the formula =IF(WEEKDAY(A2,2)>1, A2-WEEKDAY(A2,2)+1, A2-WEEKDAY(A2,2)-6)

Step 2: Then press the Enter key to obtain the first previous Monday, which appears as a five-digit number, then drag auto-fill down to get all results.

Step 3: If you want to alter the date format, keep the formula cells selected, and click Ctrl + 1 to open the Format Cells dialogue.

Step 4: Pick a Date from the Number tab and select one of the Data types from the right list as needed. Select OK.

Step 5: As a result, the following date format will be displayed:

Round Date To Next Specific Weekday

For finding the next Monday, we are using simple if-else logic with MOD functions. First, let’s understand about MOD function, this function returns the remainder after a number is divided by the divisor. The outcome is of the same sign as the divisor.

Follow the further steps to find the next Monday 

Step 1: Choose a cell in which to enter the following Monday date. To calculate the next Monday date, we may apply the formula =IF(MOD(A2-1,7)>7, A2+7-MOD(A2-1,7)+7, A2+7-MOD(A2-1,7).

Syntax

MOD(number, divisor)

The MOD function syntax has the following arguments:

• Number: The number for which you wish to calculate the remainder.
• Divisor: The number by which the number should be divided.

Now, Let’s understand the formula which we are using for finding next Monday. We are using the if-else condition for finding next Monday. So, we have a condition like MOD(date-1, 7) which will return an integer value and we will check whether that returned value is greater than 7 or not. If it is greater than 7, we will use this formula (date+7 – MOD(date-1, 7)+7) if not greater than 7, we will use this formula (date+7-MOD(date-1,7)). This if-else condition will give us our desired output.

Step 2: Then press the Enter key to obtain the first next Monday, which appears as a five-digit number, then drag auto-fill down to get all results.

Step 3: If you want to alter the date format, then while keeping the formula cells selected, click Ctrl + 1 to open the Format Cells dialogue.

Step 4: Pick a Date from the Number tab and select one date type from the right list as needed. Select OK.

Step 5: As a result, the following date format will be displayed: