Sundays date 11 weeks in future

Hi all,

I've just joined a recruitment company and we have to put 2 different rates in the system for our candidates.
1) rate 1 is till the end of week 11
2) rate 2 that applies when the candidate starts working his 12th week.

its not as easy as it sound, as we have to manually look in the calendar and look for Sunday's date as follow:

(Candidate starts on 16-3-17 then his second rate applies from 28-5-17)
we use last Sunday of week11 if candidates 11th weeks finishes between Monday and Thursday

(If candidate starts on 17-3-17 then his second rate applies from 4-6-17)
we use first Sunday after week11 if candidates 11th weeks finishes on Friday.

Please bare in mind that its about offices jobs ONLY so when i referring to working week when i say WEEK.

There has to be a better way of doing this where we can put a start date in cell and get the correct date as result.

Anyone who can help me with formula and guide/explain if possible?

TY

Try

=IF(AND(WEEKDAY(A1+77,2)>=1,WEEKDAY(A1+77,2)=4),C1-WEEKDAY(A1+77,2),C1-WEEKDAY(A1+77,2)+7)

A1=Start Date

Beaten and better by Special_K!

Thank you Very much 'K' you are genius it worked flawless at home i'll try tomorrow at work.
one more thing if you can help me out.

any way to get the date of 12th Sunday from the start date. (doesn't matter which day the candidate started.)

Originally Posted by daddylonglegs

Try this version

=A1+80-WEEKDAY(A1+2)

hi long legs your formula worked just as K's formula. Great thank you
But could you also explain the formula please and how does your formula looks like if i need a date of 12th Sunday from the start date?

Try

=A1-WEEKDAY(A1)+92 (or 85)

Depending whether you count Sunday before or after the start date as the first Sunday

Originally Posted by JohnTopley

Try

=A1-WEEKDAY(A1)+92 (or 85)

Depending whether you count Sunday before or after the start date as the first Sunday

Hi john your formula worked perfectly i used =A1-WEEKDAY(A1)+85 as the First Sunday after the start date counts as first. thanks to you as well. would u mind explaining the formula so i know what exactly is happening here.

The formula is effectively

=A1-WEEKDAY(A1)+1 + 84 or 91

The highlighted part will return the date of the previous Sunday: you then add 12 weeks (84 days) or 13 weeks (91 days)