Week of Sunday breaks in 2018

[toddlerzz]

I am trying to do a formula for a date that shows the week of with Sundays date. I am using:

=AC2-WEEKDAY(AA2,3) which returns the week of date as Sunday for my 2017 dates.

For some reason when the date changes to 2018, the formula is then giving Monday as the week of date instead of Sunday.

Any way to fix this?

For example, Dec 31, 2017 shows week of as Dec 31, 2017 but then Jan 2, 2018 shows week of as Jan 1, 2018 instead of Dec 31, 2017

Thanks.

 

[wideboydixon]

Depends what's in AC2 and AA2. Also, I think you need to number Monday (1) to Sunday (7). What result do you want for a Sunday? Two suggestions:

Sunday goes back 7 days:

=AC2-WEEKDAY(AC2,2)

Sunday stays "as-is":

=AC2-MOD(WEEKDAY(AC2,2),7)

WBD

 

[toddlerzz]

Depends what's in AC2 and AA2. Also, I think you need to number Monday (1) to Sunday (7). What result do you want for a Sunday? Two suggestions:

Sunday goes back 7 days:

=AC2-WEEKDAY(AC2,2)

Sunday stays "as-is":

=AC2-MOD(WEEKDAY(AC2,2),7)

WBD

Thanks WBD, second option gives me what I needed.