Rounding to nearest specified day

[Laurence83]

Hi,

I've a bunch of school term dates, and each school has their own method of stating what is the first day or last day of the month (some will say the day before is the first day, some will say the day after, some will say the last week day, etc) & to allow me to better compare i want to be able to round those to a specified day of the week, so that every first day of term is a Monday and every last day of term is a Sunday

So for example School 1 says term begins on Wed 9th Jan 2019, school 2 says terms being on Sundays 6th Jan 2019, school 3 says terms begins on Friday 11th Jan 2019. For the first two i'd want these rounded to Monday 7th Jan 2019, but the third would be Monday 14th Jan 2019.

I've not been able to find/work out a formula that'll do this.

Any help or suggestion is greatly appreciated.

Thanks

 

[Jon von der Heyden]

Assuming I understand the brief, try:

=IF(WEEKDAY(A1,1)<5,FLOOR(A1,7),CEILING(A1,7))+2

 

[Jon von der Heyden]

Simplify:

=CEILING(A1-4,7)+2