if date has Saturday or Sunday in it need to add 2 or 1 to date

R

ray

I have a speed sheet that calculates the number of days required to get a
project completed.
in a feild it calculated the estimated completion date based on the new
start date each morning.
but i need it to show Monday if thet date is Saturday or Sunday.
 
D

davebonallack

I have a speed sheet that calculates the number of days required to get a
project completed.
in a feild it calculated the estimated completion date based on the new
start date each morning.
but i need it to show Monday if thet date is Saturday or Sunday.

If your estimated completion date is Cell A1 (for example), then enter
the following formula into Cell A2. Cell A2 will be the new Monday-
ised completion date.
You may need to apply a date format to A2.

=IF(WEEKDAY(A1;2)=6;A1+2;IF(WEEKDAY(A1;2)=7;A1+1;A1))

PS some versions of XL use commas instead of semicolons in the above
formula.
Dave.
 
P

Philip Reece-Heal

use the weekday function in Excel. This function gives days of the week
different numbers.

Example; if your date was in cell A1, the following formula in B1 would
change date to following Monday, if date was Sat or Sun:

=IF(WEEKDAY(A1)=1,A1+1,IF(WEEKDAY(A1)=7,A1+2,))
Regards

Philip
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top