+ Reply to Thread
Results 1 to 8 of 8

Calculate the Week in the quarter using Fiscal week and a Date

  1. #1
    Registered User
    Join Date
    10-12-2016
    Location
    USA, New Jersey
    MS-Off Ver
    2013
    Posts
    3

    Question Calculate the Week in the quarter using Fiscal week and a Date

    I know the following.
    Column I,J,K,M,N
    Fiscal Yr
    Fiscal Wk
    Fiscal Qtr Num
    Fiscal Qtr
    Completed Date

    What I am trying to determine is what week in the fiscal QTR are we at based on the Completed Date?

    Our Fiscal Year Starts in February and our weeks start on Saturday and end on Friday.
    This year we started our Fiscal Year on Jan 30 2016.
    We use 13 weeks as a default within each Fiscal QTR.

    I tried this formula in another worksheet just using the completed date and it does not match up.
    =1+(WEEKNUM(EDATE(B2,-1)))-(WEEKNUM(DATE(YEAR(EDATE(B2,-1)),LOOKUP(MONTH(EDATE(B2,-1)),{1,4,7,10}),1)))

    Results
    The 30th of Jan to the 5 of Feb should be week 1 of Q1.
    (Fiscal Week in QTR) (Completed)
    14--------------------1/31/2016
    1---------------------2/01/2016
    2---------------------2/05/2016
    2---------------------2/07/2016
    3---------------------2/10/2016

    any help would be appreciated.

    Dal.
    Last edited by dalfane177; 10-12-2016 at 10:17 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Calculate the Week in the quarter using Fiscal week and a Date

    This will give week numbers for 2016's fiscal year:

    =INT((A1-DATE(2016,1,30))/7)+1
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Registered User
    Join Date
    10-12-2016
    Location
    USA, New Jersey
    MS-Off Ver
    2013
    Posts
    3

    Re: Calculate the Week in the quarter using Fiscal week and a Date

    Glenn,

    Thanks for the reply.
    The values I guess you are referencing in your formula (A1) is what I am trying to determine.
    If I am in Q1 what week is it based on the start of the fiscal year.
    I have the values/calculations for Weeks 1-52, but each Qtr is broken into 13 weeks.
    I am trying to vet out weeks 1-13 based on the completed date.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,736

    Re: Calculate the Week in the quarter using Fiscal week and a Date

    Using Glenn's formula

    =MOD(INT((A1-DATE(2016,1,30))/7),13)+1

    Will give Weeks 1 to 13 for each quarter i.e Week 14 is Week 1 of Quarter 2

    Is this what you require?

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Calculate the Week in the quarter using Fiscal week and a Date

    That formula will tell you which FY week the date in A1 is in... So I got it right, but entirely wrong...

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Calculate the Week in the quarter using Fiscal week and a Date

    John should have addressed your problem. I was on my phone and didn't see his reply.

  7. #7
    Registered User
    Join Date
    10-12-2016
    Location
    USA, New Jersey
    MS-Off Ver
    2013
    Posts
    3

    Re: Calculate the Week in the quarter using Fiscal week and a Date

    John,

    That works I tried some crazy formulas, but they broke during certain dates.

    Glenn thanks again as well. New to doing fiscal date calculations and excel formulas in general.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Calculate the Week in the quarter using Fiscal week and a Date

    Unless you tell us what you mean by "crazy formulas", we are totally unable to help any further....

+ 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. Calculate Start & End Date & Month for Each Week In Fiscal Year
    By sarndt01 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-22-2014, 02:14 PM
  3. Calculate week number for Fiscal Year Dates
    By Ginu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2013, 03:10 PM
  4. Calculate # of sales per week, month & quarter
    By AlcatrazT2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2013, 06:25 PM
  5. [SOLVED] Calculate Next Fiscal Quarter End Date
    By kmarron in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2012, 05:46 PM
  6. Fiscal week and fiscal week start date and end date
    By Nunzio in forum Excel General
    Replies: 3
    Last Post: 05-07-2012, 01:25 PM
  7. How do i calculate fiscal week of the year
    By evlai@hotmail.com in forum Excel General
    Replies: 4
    Last Post: 09-22-2005, 10:05 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