+ Reply to Thread
Results 1 to 11 of 11

Sundays date 11 weeks in future

  1. #1
    Registered User
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    4

    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

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,068

    Re: Sundays date 11 weeks in future

    Try this

    Given a start date in A1 the "second rate date" should be
    =A1+70+IF(WEEKDAY(A1+70)=6,9,8-WEEKDAY(A1+70))

    70 days is 10 weeks.
    So start date plus 70 would give the start date of the 11th week.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,763

    Re: Sundays date 11 weeks in future

    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!

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,068

    Re: Sundays date 11 weeks in future

    Quote Originally Posted by JohnTopley View Post
    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!
    You're very kind, thankyou.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sundays date 11 weeks in future

    Try this version

    =A1+80-WEEKDAY(A1+2)
    Audere est facere

  6. #6
    Registered User
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    4

    Re: Sundays date 11 weeks in future

    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.)

  7. #7
    Registered User
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    4

    Re: Sundays date 11 weeks in future

    Quote Originally Posted by daddylonglegs View Post
    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?

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,763

    Re: Sundays date 11 weeks in future

    Try

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

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

  9. #9
    Registered User
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    4

    Re: Sundays date 11 weeks in future

    Quote Originally Posted by JohnTopley View Post
    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.

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sundays date 11 weeks in future

    Quote Originally Posted by Haid3r View Post
    But could you also explain the formula please ......
    From your description on a Thursday you want to add 10 weeks and 3 days (73 days)....but on the next day you want to add 11 weeks + 2 days (79 days) so if you add 80 days and subtract WEEKDAY (which must be a value from 1 to 7) you will get the required 73 to 79.......you just need to make sure it gives you the required day of the week....

    On a Thursday you want WEEKDAY to return 7, so adding 2 to the date will give you a Saturday....and WEEKDAY(Saturday) = 7

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,763

    Re: Sundays date 11 weeks in future

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Add 12 weeks to date to return week number turn over at 52 weeks
    By nigelog in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-01-2017, 05:58 AM
  2. [SOLVED] Restrict Entry into excel for dates more than 2 weeks in future
    By xxxyyyy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-03-2016, 12:29 AM
  3. [SOLVED] Use Conditional Formatting to Hide Future Weeks
    By jimboryan in forum Excel General
    Replies: 3
    Last Post: 10-30-2015, 10:51 AM
  4. [SOLVED] Omit Sundays in date filling
    By drgkt in forum Excel General
    Replies: 4
    Last Post: 10-30-2015, 04:48 AM
  5. display no of sundays and its date
    By santbiju1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-11-2015, 08:35 AM
  6. Date Formula - 1st,2nd & 3rd Sundays of each month
    By lsmft in forum Excel General
    Replies: 7
    Last Post: 11-02-2007, 05:47 AM
  7. Date Validation - Must equal Sundays date
    By jeridbohmann in forum Excel General
    Replies: 14
    Last Post: 11-30-2005, 04:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1