If weekend date display previous Friday date
 

Cell A1 contains a date. Cell B1 contains the number of weeks to be added to
cell A1. I need cell C1 to show the result of A1 plus the number of weeks,
however if the result date falls on a Saturday or Sunday I need the cell in
C1 to show the preceeding Friday date. Can anyone help please?

If weekend date display previous Friday date
 

=A1+(B1*7)-IF(WEEKDAY(A1)=7,1,IF(WEEKDAY(A1)=1,2,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"jimar" wrote in message ...
| Cell A1 contains a date. Cell B1 contains the number of weeks to be added to
| cell A1. I need cell C1 to show the result of A1 plus the number of weeks,
| however if the result date falls on a Saturday or Sunday I need the cell in
| C1 to show the preceeding Friday date. Can anyone help please?

If weekend date display previous Friday date
 

Niek, thank you for the reply but I have not been clear enough in my original
posting. The formula I was using was =A1+(B1*7)-1) So that if A1 was a
Wednesday the result in C1 would be the number of weeks minus a day ie a
Tuesday. This is ok except when A1 is a Monday as C1 would show a Sunday
date and I would need it to show the previous Friday date. Any further help
would be very much appreciated

"Niek Otten" wrote:

=A1+(B1*7)-IF(WEEKDAY(A1)=7,1,IF(WEEKDAY(A1)=1,2,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"jimar" wrote in message ...
| Cell A1 contains a date. Cell B1 contains the number of weeks to be added to
| cell A1. I need cell C1 to show the result of A1 plus the number of weeks,
| however if the result date falls on a Saturday or Sunday I need the cell in
| C1 to show the preceeding Friday date. Can anyone help please?

If weekend date display previous Friday date
 

Niel's well designed formula first establishes the exact B1 (say 3)
week period away from A1;
then he subtracts either 1 (if A1 is a Saturday(7)) or 2 (if A1 is a
Sunday(1)).

Sounds like you need to modify for If(WEEKDAY(A1)=2,3 type...

HTH


"jimar" wrote:

Niek, thank you for the reply but I have not been clear enough in my original
posting. The formula I was using was =A1+(B1*7)-1) So that if A1 was a
Wednesday the result in C1 would be the number of weeks minus a day ie a
Tuesday. This is ok except when A1 is a Monday as C1 would show a Sunday
date and I would need it to show the previous Friday date. Any further help
would be very much appreciated

"Niek Otten" wrote:

=A1+(B1*7)-IF(WEEKDAY(A1)=7,1,IF(WEEKDAY(A1)=1,2,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"jimar" wrote in message ...
| Cell A1 contains a date. Cell B1 contains the number of weeks to be added to
| cell A1. I need cell C1 to show the result of A1 plus the number of weeks,
| however if the result date falls on a Saturday or Sunday I need the cell in
| C1 to show the preceeding Friday date. Can anyone help please?

If weekend date display previous Friday date
 

JMay thankyou for the input. Formula now doing what I need it to do. Thanks
to you both.

"JMay" wrote:

Niel's well designed formula first establishes the exact B1 (say 3)
week period away from A1;
then he subtracts either 1 (if A1 is a Saturday(7)) or 2 (if A1 is a
Sunday(1)).

Sounds like you need to modify for If(WEEKDAY(A1)=2,3 type...

HTH


"jimar" wrote:

Niek, thank you for the reply but I have not been clear enough in my original
posting. The formula I was using was =A1+(B1*7)-1) So that if A1 was a
Wednesday the result in C1 would be the number of weeks minus a day ie a
Tuesday. This is ok except when A1 is a Monday as C1 would show a Sunday
date and I would need it to show the previous Friday date. Any further help
would be very much appreciated

"Niek Otten" wrote:

=A1+(B1*7)-IF(WEEKDAY(A1)=7,1,IF(WEEKDAY(A1)=1,2,0))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"jimar" wrote in message ...
| Cell A1 contains a date. Cell B1 contains the number of weeks to be added to
| cell A1. I need cell C1 to show the result of A1 plus the number of weeks,
| however if the result date falls on a Saturday or Sunday I need the cell in
| C1 to show the preceeding Friday date. Can anyone help please?