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

#### stevembe

##### Well-known Member
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!

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi,

Try,

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

Jai

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!

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

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

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

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>

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

Replies
4
Views
240
Replies
19
Views
335
Replies
4
Views
560
Replies
16
Views
485
Replies
1
Views
365

1,203,060
Messages
6,053,303
Members
444,650
Latest member
bookendinSA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back