+ Reply to Thread
Results 1 to 15 of 15

week number formula - fiscal year

  1. #1
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    41

    week number formula - fiscal year

    Hi All,

    Need some help with the week number functionality for a fiscal year calendar, the year starts on 1st of July and Wednesday is the week beginning and ends on a Tuesday. Format day, month, year!

    01/07/2015 - week1
    08/07/2015 - week 2
    15/07/2015 - week 3
    22/07/2015 - week 4
    .......................
    01/07/2016 - week 1
    06/07/2016 - week 2
    13/07/2016 - week 3
    20/07/2016 - week 4
    .....................
    01/07/2017 - week 1
    05/07/2017 - week 2
    12/07/2017 - weeek 3
    19/07/2017 - week 4


    Thanks in advance!

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: week number formula - fiscal year

    Year in A1

    A2=DATE($A$1,7,1)

    A3=A2+8-WEEKDAY(A2-3)

    A4=A3+7

    Copy down.

  3. #3
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: week number formula - fiscal year

    Hi What do you mean by year in A1? A1 is a date! also can we simplify with one formula?

  4. #4
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: week number formula - fiscal year

    Quote Originally Posted by Phuocam View Post
    Year in A1

    A2=DATE($A$1,7,1)

    A3=A2+8-WEEKDAY(A2-3)

    A4=A3+7

    Copy down.
    Hi What do you mean by year in A1? A1 is a date! also can we simplify with one formula

  5. #5
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: week number formula - fiscal year

    If you have July 1st of the year in cell A2 Ilike 2016-07-01) and any date in cell A3 then this formula (in cell B3) will give you the week number of that date in A3.
    Please Login or Register  to view this content.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  6. #6
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: week number formula - fiscal year

    Quote Originally Posted by p24leclerc View Post
    If you have July 1st of the year in cell A2 Ilike 2016-07-01) and any date in cell A3 then this formula (in cell B3) will give you the week number of that date in A3.
    Please Login or Register  to view this content.
    All I have is a date in Cell A1. DD-MM-YYYY format.
    I am trying to work on a simple formul as per below

    =WEEKNUM(A1-(1+ DATE(YEAR(A1),6,30)-DATE(YEAR(A1),1,1)))

    Which will allow Wednesday is the begining of the week and 1of July is begining of the year!

  7. #7
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: week number formula - fiscal year

    there you are:
    Please Login or Register  to view this content.
    but keep in mind that this formula is only good for the year beginning on July 1st 2016 as explicitly stated in the formula. If your date is within another fiscal year, you have to change it in the formula.

    Would you like to have a formula that will do this automatically? We can try to get one working for you.
    Regards

  8. #8
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: week number formula - fiscal year

    Quote Originally Posted by p24leclerc View Post
    there you are:
    Please Login or Register  to view this content.
    but keep in mind that this formula is only good for the year beginning on July 1st 2016 as explicitly stated in the formula. If your date is within another fiscal year, you have to change it in the formula.

    Would you like to have a formula that will do this automatically? We can try to get one working for you.
    Regards
    Yes please. That will be great! Also, as this is a fiscal year, how do we change it in between years? The year will start on 1st of July n 2016 and ends on 30th June 2017
    Last edited by rubz71; 11-23-2016 at 08:21 PM.

  9. #9
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: week number formula - fiscal year

    try this formula then.
    Please Login or Register  to view this content.
    It is a long formula but it works.

  10. #10
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: week number formula - fiscal year

    Quote Originally Posted by p24leclerc View Post
    try this formula then.
    Please Login or Register  to view this content.
    It is a long formula but it works.
    OMG!!! it works like magic! you are amazing. Thank you soo much!

  11. #11
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: week number formula - fiscal year

    Quote Originally Posted by p24leclerc View Post
    try this formula then.
    Please Login or Register  to view this content.
    It is a long formula but it works.
    OMG!!! it works like magic! you are amazing. Thank you, soo much!

  12. #12
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: week number formula - fiscal year

    don't forget to mark your thread as SOLVED.

  13. #13
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: week number formula - fiscal year

    Quote Originally Posted by p24leclerc View Post
    don't forget to mark your thread as SOLVED.
    Thanks again!

  14. #14
    Registered User
    Join Date
    07-11-2018
    Location
    Sunshine Coast, Australia
    MS-Off Ver
    2016
    Posts
    8

    Re: week number formula - fiscal year

    Hi,
    I've tried this formula as I am having the same issue but when I use it for past years, say 2014 the 9/7/2014 is resulting in week 3 for Wednesday the 9th July, when it should be week 2. Tuesday the 8th July is also resulting in week 3 - same as Wednesday when the start of the week is Wednesday. Am I doing something wrong?

    To clarify I need each week to start on a Wednesday and finish on a Tuesday as this is our pay cycle. Financial years begins on 1st July and ends on 30 June.

    Thanks.

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: week number formula - fiscal year

    jkmcn691 welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Calculate week number for Fiscal Year Dates
    By katie_10042 in forum Excel General
    Replies: 16
    Last Post: 02-14-2017, 05:58 AM
  2. Fiscal week number formula
    By tgljl in forum Excel General
    Replies: 2
    Last Post: 11-25-2015, 11:13 PM
  3. [SOLVED] Trying to return the week number of dates in a fiscal year
    By NedFlanders in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-03-2013, 09:16 PM
  4. Calculate week number for Fiscal Year Dates
    By Ginu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2013, 03:10 PM
  5. Convert date to week number for fiscal year starting July 25
    By ARayburn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-15-2013, 10:07 AM
  6. Replies: 6
    Last Post: 05-08-2013, 01:46 PM
  7. Formula for Week number within a fiscal month
    By GerryT in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-18-2012, 08:14 PM

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