Results 1 to 7 of 7

Thread: Calculating the week number when you have a fiscal year starting in May

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculating the week number when you have a fiscal year starting in May

    Hi - I am looking for a simple formula to work out the week number for my dates in our fiscal year.
    Column A is a range of different dates for the last 3 years
    For example= A1 is 01/5/2016 I want it to return in cell B1 the week number 1

    Any help would be great!

    Thanks!!!

  2. #2
    Board Regular
    Join Date
    Oct 2016
    Location
    Midwest, USA
    Posts
    151
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating the week number when you have a fiscal year starting in May

    In the subject you state your fiscal year begins in May, but in the body you are asking for January to be week 1.

    Does May matter, or are you simply looking for the week number?

    Code:
    =WeekNum(a1,1)
    Where A1 is your date and 1 is the week beginning on Sunday.

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating the week number when you have a fiscal year starting in May

    Sorry I am in the UK so my date in A1 would be 05/01/2016 as week 1

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Calculating the week number when you have a fiscal year starting in May

    Hi,

    Try this:

    ABC
    15/1/20161

    Sheet77



    Worksheet Formulas
    CellFormula
    C1=WEEKNUM(A1)-WEEKNUM(EOMONTH(A1,-1)-6)


  5. #5
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,391
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Calculating the week number when you have a fiscal year starting in May

    @ jtakw:

    Your solution returns 1 for the first day of every month after May and negative numbers for January.

    Here is my take on the fiscal year week number problem:

    =ROUNDDOWN((A1+7-WORKDAY.INTL(DATE(YEAR(A1)-(MONTH(A1)<5),5,2),-1,"1111110"))/7,0)

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Calculating the week number when you have a fiscal year starting in May

    Thanks Tetra, for some reason, I was Only thinking May 1st at the time...

  7. #7
    New Member
    Join Date
    Jun 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating the week number when you have a fiscal year starting in May

    Thanks Tetra! This worked perfectly!

    Just looking at the data again, I think instead of doing up to 52/53 weeks for the year I just need to show how many weeks for that quarter.
    So for example:
    Q1 = May - July (14 weeks based off the formula above)
    Q2 = August - October (X weeks)
    Q3 = November - Jan
    Q4= Feb - April

    Any help for this formula would be amazing! Thanks!

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
  •