Determine next Saturday based on a given date
 

If I have a date in a cell, I need to determine the date of the following
Saturday. For example, if I have the date of 10/8/2006 in a cell, I need the
function to return the date 10/14/2006.

Answer: Determine next Saturday based on a given date
 

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.

Determine next Saturday based on a given date
 

=A1+7-WEEKDAY(A1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ann" wrote in message ...
| If I have a date in a cell, I need to determine the date of the following
| Saturday. For example, if I have the date of 10/8/2006 in a cell, I need the
| function to return the date 10/14/2006.

Determine next Saturday based on a given date
 

Or, if A1 can be a Saturday as well,

=A1+IF(WEEKDAY(A1)=7,14,7)-WEEKDAY(A1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ann" wrote in message ...
| If I have a date in a cell, I need to determine the date of the following
| Saturday. For example, if I have the date of 10/8/2006 in a cell, I need the
| function to return the date 10/14/2006.

Determine next Saturday based on a given date
 

Hi Niek, you can simplify that to

=A1+7-WEEKDAY(A1+2,3)

"Niek Otten" wrote:

Or, if A1 can be a Saturday as well,

=A1+IF(WEEKDAY(A1)=7,14,7)-WEEKDAY(A1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ann" wrote in message ...
| If I have a date in a cell, I need to determine the date of the following
| Saturday. For example, if I have the date of 10/8/2006 in a cell, I need the
| function to return the date 10/14/2006.

Determine next Saturday based on a given date
 

Yes, indeed!

Thanks,

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"daddylonglegs" wrote in message ...
| Hi Niek, you can simplify that to
|
| =A1+7-WEEKDAY(A1+2,3)
|
| "Niek Otten" wrote:
|
| Or, if A1 can be a Saturday as well,
|
| =A1+IF(WEEKDAY(A1)=7,14,7)-WEEKDAY(A1)
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Ann" wrote in message ...
| | If I have a date in a cell, I need to determine the date of the following
| | Saturday. For example, if I have the date of 10/8/2006 in a cell, I need the
| | function to return the date 10/14/2006.
|
|
|

Determine next Saturday based on a given date
 

On Mon, 9 Oct 2006 07:34:01 -0700, Ann wrote:

If I have a date in a cell, I need to determine the date of the following
Saturday. For example, if I have the date of 10/8/2006 in a cell, I need the
function to return the date 10/14/2006.

It depends on what you want to do if the date is a Saturday.

If you want the next Saturday, then:

=A1-WEEKDAY(A1+1)+8

Saturday, October 07, 2006 Saturday, October 14, 2006
Sunday, October 08, 2006 Saturday, October 14, 2006
Monday, October 09, 2006 Saturday, October 14, 2006
Tuesday, October 10, 2006 Saturday, October 14, 2006
Wednesday, October 11, 2006 Saturday, October 14, 2006
Thursday, October 12, 2006 Saturday, October 14, 2006
Friday, October 13, 2006 Saturday, October 14, 2006



If you want it to not change, then:

=A1-WEEKDAY(A1)+7

Saturday, October 07, 2006 Saturday, October 07, 2006
Sunday, October 08, 2006 Saturday, October 14, 2006
Monday, October 09, 2006 Saturday, October 14, 2006
Tuesday, October 10, 2006 Saturday, October 14, 2006
Wednesday, October 11, 2006 Saturday, October 14, 2006
Thursday, October 12, 2006 Saturday, October 14, 2006
Friday, October 13, 2006 Saturday, October 14, 2006



--ron