Weeknum
Results 1 to 9 of 9

Thread: Weeknum
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Weeknum

    I have a formula below , where I need the week number from 1 - 13 depending on the upload date & what Qtr we are in.



    =WEEKNUM(J4058)-WEEKNUM(LOOKUP(J4058,DATE(YEAR(J4058),{3,6,9,12},30)))


    When a new QTR starts, the weeknum should be "1", however it keeps populating as "0".

    Any help would be appreciated.

  2. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Weeknum

    You could also try this:

    =IF(MOD(WEEKNUM(J4058),13)=0,13,MOD(WEEKNUM(J4058),13))
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    New Member
    Join Date
    Dec 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Weeknum

    Quote Originally Posted by tyija1995 View Post
    You could also try this: =IF(MOD(WEEKNUM(J4058),13)=0,13,MOD(WEEKNUM(J4058),13))
    Unfortunately that did not work, see below example where the new week count starts at 0 and should start at 1
    28/06/2019 28-Jun-19 2019 JUN Qtr June 13
    01/07/2019 01-Jul-19 2019 SEP Qtr July 0

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,858
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Weeknum

    That formula gives me 1 not 0
    Is the date in J4508 a real date, or text?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Dec 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Weeknum

    yes , it is a date.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,858
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Weeknum

    This is what I get
    IJ
    4101/07/2019
    5208/07/2019
    6315/07/2019
    7422/07/2019
    8529/07/2019
    9605/08/2019
    10712/08/2019
    11819/08/2019
    12926/08/2019
    131002/09/2019
    141109/09/2019
    151216/09/2019
    161323/09/2019
    17130/09/2019

    Request



    Worksheet Formulas
    CellFormula
    I4=IF(MOD(WEEKNUM(J4),13)=0,13,MOD(WEEKNUM(J4),13))

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Dec 2016
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Weeknum

    Your formula works, i forgot to change the lookup cells when i pasted it in!!

    Thanks very much.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,858
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Weeknum

    It's not my formula, it's @tyija1995 that supplied it.

    But glad it's working & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Weeknum

    Thanks guys - glad it's working now, have a good day!
    √-1 2³ ∑ π
    …And it was delicious!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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