Formula to Determine Next Saturday

To determine the next Saturday based on a given date, use the following formula:

Here, A1 is the cell containing the given date.

How the Formula Works

1. YEAR(A1), MONTH(A1), and DAY(A1) extract the year, month, and day from the given date.
2. WEEKDAY(A1,1) returns the day of the week for the given date, where Sunday is considered the first day of the week (hence the "1" argument).
3. (7-WEEKDAY(A1,1)+1) calculates the number of days until the next Saturday. For example, if the given date is a Wednesday, this part of the formula would return 4 (i.e., 7-3+1).
4. Finally, we add this number of days to the original date using the DATE function to get the next Saturday.

Example

If you have the date 10/8/2006 in cell A1, the formula would return 10/14/2006. You can simply copy the formula to other cells to apply it to different dates.

1. Enter the formula =DATE(YEAR(A1),MONTH(A1),DAY(A1)+(7-WEEKDAY(A1,1)+1)) in a cell.
2. Replace A1 with the cell containing the given date.
3. Press Enter to apply the formula.
4. Copy the formula to other cells to apply it to different dates.