=p5+364+choose(weekday(p5+364),1,0,0,0,0,0,2)

[stevembe]

Can anybody see what is wrong with the formula in the title, in P5 I have 20/04/2012 and it should be counting a year later but defaulting to the first Monday i.e. not including a weekend. However the return is 19/04/2013 which is a Friday. It seems to work in all all the other cells of the same colimn???

Baffled!

 

[jai9]

Hi,

Try,

=P5+364+CHOOSE(WEEKDAY(P5+364),1,0,0,0,0,3,2)

Jai

 

[stevembe]

Thanks for that, it worked for that cell but I have just noticed another. In P12 I have 12/03/2013 but the return is 11/03/2014 which is a Tuesday. I tried both formulae and it stays on Tuesday in each case.

This is confusing!

 

[jai9]

Maybe,

=P5+364+CHOOSE(WEEKDAY(P5+364),1,0,-1,-2,-3,3,2)

This will give the previous monday for tuesday, wednesday etc and for friday, sat next monday.

Jai

 

[Norie]

It's not 100% clear what you want to do but this should return the previous Monday.

=P5+365+1-WEEKDAY(P5+365,2)

 

[stevembe]

Thanks, still confused as to why the first formula kept going wrong on occasion so if anybody has any ideas? </SPAN></SPAN>

The formula you sent seems to work, just hope it is more reliable. Thank you</SPAN></SPAN>

 

[stevembe]

It's not 100% clear what you want to do but this should return the previous Monday.

=P5+365+1-WEEKDAY(P5+365,2)

It is to calculate a date for return to work from maternity so if the date falls on a Friday it returns Mondays date instead