If weekend date display previous Friday date

  • Thread starter Thread starter jimar
  • Start date Start date
J

jimar

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

| 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
 
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
 
Back
Top