Choose Weekday

[Papi]

Cell A1 represents the first day of the month. Cell B4 needs to be the first day of that particular month but must be a weekday i.e. Monday to Friday and not weekends. This is the closest I got but it does not work. =CHOOSE(WEEKDAY(A1),0,0,1,0,1,0,2)+A1

Any help would be greatly appreciated.

 

[barry houdini]

Using WEEKDAY a Sunday is 1 through to 7 for a Saturday, so given that you want to add 1 to a Sunday and 2 to a Saturday that should be

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

or if you don't mind using Analysis ToolPak functions you can use WORKDAY, i.e.

=WORKDAY(A1-1,1)

 

[Papi]

Thanks very much Barry for a very quick response and two perfect solutions. My splitting headache (and somewhat limited skills) would not allow me to hone in closer to what you produced. Again...thanks.

 

[Papi]

I forgot to mention, I appreciate your expalantion on how the weekday works. That really helped as well.