Results 1 to 5 of 5

Thread: fiscal year

  1. #1

    fiscal year



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all.
    I just started new job and im already stock with excel. Hope someone can help me. Is there some way how to calculate days from fiscal year? Im working with date data: 05/01/15 and fiscal date : Q4 wk11 I need create new column that will show that order is created in or not in 28 days. This example i used will show not because there is only 1 week between date and fiscal year date. Thanks for help.

  2. #2
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi
    Not really understanding your full question, but all dates in Excel are stored as serial numbers, being the number of days since 01 Jan 1900. For example, today, 16 Jan 2015 is day number 4020.
    So regardless how you may have your cell formatted to show either 16 Jan 2015, or 01/16/2015 or any other format, Excel stores it as a number.

    Therefore it is really easy to calculate the number of days between 2 dates, you just take the EARLIEST date away from the LATEST date.
    I emphasis the order, so that you end up with a positive number of days as opposed to negative.
    So with your Fiscal date in A1 of say 01 Jan 2015, and your oder date in B1 of 05 Jan 2015, then
    =B1-A1 will give you 4
    These are the elapsed days between the 2 dates, and it is not counting the 05 Jan as a day, because as far as Excel is concerned, the date is the beginning of the day. If you want the period from 1 minute past midnight on 01 Jan 2015, until midnight on 05 Jan 2015, then you place a +1 at the nd of your formula.

    If this doesn't answer your query, then post back.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  3. #3
    Quote Originally Posted by Roger Govier View Post
    Therefore it is really easy to calculate the number of days between 2 dates, you just take the EARLIEST date away from the LATEST date.
    I emphasis the order, so that you end up with a positive number of days as opposed to negative.
    Or you just absolute it so as not to care

    =ABS(date1-date2)


  4. #4
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Absolutely, Bob!!!
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  5. #5
    I suspect that the OP is using a 4/4/5 fiscal reporting calendar where
    Reporting Fiscal Year=2015
    Reporting Start Month= April
    Reporting Quarter= 4
    Reporting Period=11
    Reporting Day=1
    With the help of the 9MB DataTableFisc.xlsx by Colin,
    this translates to 1 Feb 2015.
    The difference between the 2 dates is indeed 28 days.
    PowerPivot with Custom Calendar is a sine qua non.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •