Weekend calculation question

[dawnfoss]

Hi

Can someone help me with a formula that will return the following:

If A1 = monday through friday then B1= A1 (monday)
If A1 =saturday then B1=A1+2 (monday)
If A1= sunday then B1=A1+1 (monday)

Thanks!
dawn

 

[Diffy]

in B1 Try (format B1 as a date):

if(and(Weekday(A1)>1, Weekday(A1)<7),A1,if(weekday(A1)=7,A1+2,A1+1))

 

[Dan Waterloo]

In B1:

=IF(WEEKDAY(A1,2) <= 5,
A1 - WEEKDAY(A1, 2) + 1,
A1 - WEEKDAY(A1, 2) + 8)

 

[Dan Waterloo]

Dawn:

My formula is assuming that if a Monday to a Friday date is in A1 then you want the Monday date from that week, whereas if A1 contains a Saturday or Sunday date then you want the Monday date from the next week.

 

[barry houdini]

I'm assuming that A1 contains a date and you want the next weekday. You could use WORKDAY function from Analysis ToolPak, i.e.

=WORKDAY(A1,1)

or without Analysis ToolPak

=A1+CHOOSE(WEEKDAY(A1),1,1,1,1,1,3,2)