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?

=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?

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?

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?

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?