Results 1 to 4 of 4

Thread: Excel Formula for moving fiscal year start date

  1. #1
    New Member
    Join Date
    Mar 2010
    Location
    Upstate, New York
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel Formula for moving fiscal year start date

    Good morning all.....
    Done a lot of looking and have made some progress but stuck now....

    My fiscal year does not always start on the same date....This is for some accounting reason...but it's been this way forever as far as i know...
    What i do know is that the fiscal year starts on the SUNDAY before the LAST WEDNESDAY in MARCH...
    =DATE(YEAR(B3),MONTH(B3)+1,0)+MOD(-WEEKDAY(DATE(YEAR(B3),MONTH(B3)+1,0),2)-2,-7)-5
    This returns the Sunday before the Last Wednesday of each month....
    I managed to get this far (with a lot of research) but cant get further....
    I have a list of dates for 10 years in column B....would love to add to Column C what the FY starting date for that date is.....
    In 2017...The last Wednesday in March was the 29th...the prior Sunday is the 26th...That is my FY start date
    In 2018...The last Wednesday in March was the 28th...the prior Sunday is the 25th...That is my FY start date

    I would love to be able to have any given date in Col. B with that date's FY Start date in order to calulate fiscal days, weeks and quarters...

    Any helps would be appreciated....

  2. #2
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    3,400
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Excel Formula for moving fiscal year start date

    Does this work?

    BC
    1DateFY Start Date
    224/03/201826/03/2017
    325/03/201825/03/2018
    425/03/201727/03/2016
    526/03/201726/03/2017

    Sheet1



    Worksheet Formulas
    CellFormula
    C2=IF($B2<DATE(YEAR($B2),3,31)-MOD(DATE(YEAR($B2),3,31)+3,7)-3,DATE(YEAR($B2)-1,3,31)-MOD(DATE(YEAR($B2)-1,3,31)+3,7)-3,DATE(YEAR($B2),3,31)-MOD(DATE(YEAR($B2),3,31)+3,7)-3)



    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - I suggest this tool.
    Remember to make a copy of your spreadsheet before running any macros that might change the data - macros can't be undone!

  3. #3
    New Member
    Join Date
    Mar 2010
    Location
    Upstate, New York
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Formula for moving fiscal year start date

    WBD....

    WOW!!!!!!! Awesome!!! Looks like it works perfectly!!! can't believe you did that in 20 minutes!!!

    THANK YOU!!!

  4. #4
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    3,400
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Excel Formula for moving fiscal year start date

    Most of it is the same formula three times. Sunday before last Wednesday in March for {YEAR} is:

    Code:
    DATE({YEAR},3,31)-MOD(DATE({YEAR},3,31)+3,7)-3
    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - I suggest this tool.
    Remember to make a copy of your spreadsheet before running any macros that might change the data - macros can't be undone!

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
  •