+ Reply to Thread
Results 1 to 9 of 9

Create Fiscal weeks over 3 years of data

  1. 02-06-2018, 09:38 AM #1
    Yameye
    Yameye is offline
    Registered User
    Join Date
    02-06-2018
    Location
    Belfast, UK
    MS-Off Ver
    2010
    Posts
    4

    Create Fiscal weeks over 3 years of data

    Sorry if this has been asked elsewhere, but I cant find a solution to my specific requirements.

    My Fiscal Year starts on the 1st Feb, and I'm trying to make the Week Number start at 1 for the 1st of Feb but become week 2 on the first Monday thereafter.

    I'm comparing figures across 3 years, so in 2016 week 1 was fine as it was a Monday, but 2017 week 1 would start on Wed 1st Feb, Week 2 would start on Mon 6th Feb, and in 2018 week 1 starts Thur 1st Feb, but week 2 starts on Mon 5th.

    Is there any way for to have excel 2010 create this fiscal week in a single data set from 01 Feb 2016 to present?

    Thanks in advance
    Reply With QuoteRegister To Reply
  2. 02-06-2018, 10:02 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Create Fiscal weeks over 3 years of data

    Are you wanting every date over three years mapped, or a formula that can simply work out the week number for any date in the range?
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 02-06-2018, 10:13 AM #3
    Yameye
    Yameye is offline
    Registered User
    Join Date
    02-06-2018
    Location
    Belfast, UK
    MS-Off Ver
    2010
    Posts
    4

    Re: Create Fiscal weeks over 3 years of data

    This is a data set that is being imported daily basis, I already have a fiscal year formula starting on the 1st Feb each year, so I would like the Fiscal Week to reset back to 1 on the 1st Monday in Feb each Year.

    Hope this makes sense.
    Reply With QuoteRegister To Reply
  4. 02-06-2018, 10:22 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Create Fiscal weeks over 3 years of data

    Yes, it does, so give us the formula you are using and we can show you how to adapt it.
    Reply With QuoteRegister To Reply
  5. 02-06-2018, 12:09 PM #5
    Yameye
    Yameye is offline
    Registered User
    Join Date
    02-06-2018
    Location
    Belfast, UK
    MS-Off Ver
    2010
    Posts
    4

    Re: Create Fiscal weeks over 3 years of data

    My Fiscal Year Formula is as follows...

    =YEAR(B2)+(MONTH(B2)>=2)-1



    I just cant figure out how to make this week based.
    Reply With QuoteRegister To Reply
  6. 02-06-2018, 01:10 PM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Create Fiscal weeks over 3 years of data

    So do you want the counting to restart on 01 Feb, or on the first Moday in Feb?
    Reply With QuoteRegister To Reply
  7. 02-06-2018, 01:24 PM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Create Fiscal weeks over 3 years of data

    Can you work with or adapt this?

    =IF(MONTH(B2)>1,ISOWEEKNUM(B2)-4,ISOWEEKNUM(B2)+48)
    Last edited by AliGW; 02-06-2018 at 01:31 PM.
    Reply With QuoteRegister To Reply
  8. 02-06-2018, 03:22 PM #8
    Yameye
    Yameye is offline
    Registered User
    Join Date
    02-06-2018
    Location
    Belfast, UK
    MS-Off Ver
    2010
    Posts
    4

    Re: Create Fiscal weeks over 3 years of data

    I had seen this function, but unfortunately I'm working with Excel 2010 and the ISOWEEKNUM only started in 2013...

    I have also found this formula and it seems to work, but somewhere it seems to be referencing the 1st October as the start of the financial year. (had to paste as image as I couldnt cut and paste due to html error??

    Formula.JPG
    Reply With QuoteRegister To Reply
  9. 02-13-2018, 11:32 AM #9
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,568

    Re: Create Fiscal weeks over 3 years of data

    Hello Yameye and Welcome to Excel Forum.
    To me it looks as if the formula is referencing April 1st, as the arguments for DATE are (year, month, day). You might get your desired result by changing the DATE(YEAR(B2),4,1) to DATE(YEAR(B2),2,1) throughout the formula.
    If that doesn't work perhaps you could upload a small, desensitized, sample of your spreadsheet by clicking on GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
    I imagine that the html error you encountered was due to the portion of the formula ...B2< followed immediately by IF. Put a space between any < symbol and the next letter/number as in B2< IF before submitting the formula as a part of your message.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. % change between two fiscal years sticky to bars within chart?
    By esbencito in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-31-2017, 01:51 PM
  2. Forecasting template to shift data across fiscal years
    By MikethePlugin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-22-2017, 01:08 PM
  3. Formula's using fiscal years
    By etherplain in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-14-2015, 08:06 PM
  4. Fiscal Year 2010 has 91 weeks?
    By smninos in forum Excel General
    Replies: 2
    Last Post: 02-01-2011, 12:15 PM
  5. Pivot Table Fiscal Years
    By Sadie in forum Excel General
    Replies: 1
    Last Post: 11-16-2005, 05:15 AM
  6. [SOLVED] Create fiscal Year calendar list weeks by #1-52
    By akid12 in forum Excel General
    Replies: 0
    Last Post: 06-14-2005, 07:05 PM
  7. [SOLVED] working with dates in fiscal years
    By justin_vasko in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-18-2005, 04:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1