+ Reply to Thread
Results 1 to 12 of 12

Week Number Calculation

  1. #1
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    682

    Week Number Calculation

    Dear Experts,

    Please find here attached workbook, where in the column R i have used the formula for Week Number calculation and result was Week 53 from column A then i have used IF condition and changed from Week 53 to Week 1 and the second issue which i am facing is the current formula is also making quite big size and working very slow for the results. Request to you kindly do provide on the both the issues.

    Thank you for your precious phase and valuable support.

    Regards,

    Neilesh
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Week Number Calculation

    This is shorter.

    ="Week "&WEEKNUM(A2)&", "&J2

    I got a bit confused by your week 53 issue... Do you want Week 1 to be extended by the days in week 53 of the previous year?????
    Glenn



  3. #3
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    682

    Re: Week Number Calculation

    Quote Originally Posted by Glenn Kennedy View Post
    This is shorter.

    ="Week "&WEEKNUM(A2)&", "&J2

    I got a bit confused by your week 53 issue... Do you want Week 1 to be extended by the days in week 53 of the previous year?????
    Dear Expert,

    Thank you so much for your precious phase. But we are considering first week from Jan.04 (Which will starting from Monday) to Jan 10 2016 (Ending at Sunday) and Week 52 will be Dec.26 to Jan.01, 2017.

    I have tried the provided formula as well but the result throwing as Week 53. Request to you please do help me out.

    Regards,

    Neilesh

    Regards,

    Neilesh

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: Week Number Calculation

    =INT((A1-42373)/7)+1

    where A1 is a 2016 date from 04/01/2016 onwards provides the week number from 1 to 52.
    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.

  5. #5
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    682

    Re: Week Number Calculation

    Quote Originally Posted by Special-K View Post
    =INT((A1-42373)/7)+1

    where A1 is a 2016 date from 04/01/2016 onwards provides the week number from 1 to 52.
    Dear Expert,

    Thank you for your precious support but the throwing result is 0. Request to you please help me out.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Week Number Calculation

    Please confirm...

    Week 1 begins on the FIRST Monday of the year. Yes or no? If no: when does it start.

    In 2016, the first Monday was the 4th. What week/Year should Sunday 3rd January 2016 have?

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Week Number Calculation

    Is this what you wanted?

    ="Week "&IF(WEEKNUM(A2,21)>52,1,WEEKNUM(A2,21))&" of "&YEAR(A2)

  8. #8
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318
    Try this:

    =IF (DATE (A1),1,4),52, INT ((A1-DATE (YEAR (A1).1.4))/7+1)

  9. #9
    Forum Contributor Neilesh Kumar's Avatar
    Join Date
    05-26-2016
    Location
    INDIA
    MS-Off Ver
    2013 & 2016
    Posts
    682

    Re: Week Number Calculation

    Quote Originally Posted by Glenn Kennedy View Post
    Is this what you wanted?

    ="Week "&IF(WEEKNUM(A2,21)>52,1,WEEKNUM(A2,21))&" of "&YEAR(A2)
    Thank you Dear Expert. Yes i was looking for the same Dear Expert.

    Regards,

    Neilesh

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Week Number Calculation

    You're welcome and thanks for the rep.

  11. #11
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: Week Number Calculation

    Quote Originally Posted by Neilesh Kumar View Post
    Dear Expert,

    Thank you for your precious support but the throwing result is 0. Request to you please help me out.
    That's impossible, since whatever the formula does, 1 is then added to the final result.
    So it can never be 0.

    However, sounds like you have a solution.

  12. #12
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: Week Number Calculation

    HTML Code: 
    =if(a1 <  date(year(a1),1,4),52,int((a1-date(year(a1),1,4))/7)+1)
    PHP Code: 
    =if(a1 <  date(year(a1),1,4),52,int((a1-date(year(a1),1,4))/7)+1

+ 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] Highlight week number using Conditional formatting - shows incorrect week
    By spliffter in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 02-26-2016, 09:52 AM
  2. [SOLVED] Week by week automatic sales table calculation
    By artner0112 in forum Excel General
    Replies: 2
    Last Post: 11-28-2015, 11:53 PM
  3. Replies: 5
    Last Post: 09-01-2014, 03:55 PM
  4. Replies: 1
    Last Post: 07-09-2014, 05:57 AM
  5. Week number calculation problem
    By jacodtt in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-04-2013, 11:42 PM
  6. Replies: 7
    Last Post: 05-02-2013, 06:48 AM
  7. [SOLVED] Calculation based on week & Table number
    By zeko90 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-25-2013, 05:13 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