+ Reply to Thread
Results 1 to 6 of 6

Week End date on Every Saturday

  1. #1
    Registered User
    Join Date
    06-05-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2007
    Posts
    29

    Question Week End date on Every Saturday

    I want to generate Week End Date according to ISO Standards for all 52/53 weeks.

    My week end's on Saturday
    I tried this two formulas.

    =DATE(P2,1,1)+7-WEEKDAY(DATE(P2,1,1))
    &
    =DATE(P2,1,1+((1-(5>=WEEKDAY(DATE(P2,1,1))))*7)+(5-WEEKDAY(DATE(P2,1,1))))+2

    Where P2 is passing Year only.

    But it is not handling Year with 53 weeks.
    I want formula which will generate 52 or 53 week according to year.
    (Should generate 52 only where there in 52 week and 53 where there is 53 Weeks)

    Please Help. Thanks in Advance
    Last edited by Ashurk77; 01-16-2014 at 03:39 PM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Week End date on Every Saturday

    It will be easy to understand if you show couple of examples in excel with expected result


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    06-05-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Week End date on Every Saturday

    For eg in 2016 there is 53 week and in 2015 and 2017 again there is 52 weeks only.
    My week ends on Saturday
    So i want formula to generate all 52 week End Date.
    and Date for 53 week only for those years where there is 53 weeks.

    I hope my question is clear now....

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

    Re: Week End date on Every Saturday

    If you just want a list of all Saturdays in the year then with year in P2 use this formula in A1 for 1st Saturday (as per your post)

    =DATE(P2,1,1)+7-WEEKDAY(DATE(P2,1,1))

    Then in A2 use this formula copied down to A52

    =A1+7

    and in A53 for possible week 53

    =IF(MONTH(A52+7)=1,"",A52+7)
    Audere est facere

  5. #5
    Registered User
    Join Date
    06-05-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Week End date on Every Saturday

    Thanks
    Its working

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Week End date on Every Saturday

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ 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. Replies: 7
    Last Post: 05-02-2013, 06:48 AM
  2. Week Name = "Week" and the date of Saturday for that week.
    By Hudas in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-01-2012, 10:53 PM
  3. [SOLVED] Week starts on Saturday - Working days
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-17-2006, 05:39 PM
  4. Week starts on Saturday - Working days
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-17-2006, 12:50 PM
  5. Replies: 11
    Last Post: 09-19-2005, 08:15 AM

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