Results 1 to 3 of 3

Thread: Formula for amount of days within a date range that fall within a fiscal year

  1. #1
    Neophyte swarno01's Avatar
    Join Date
    Aug 2019
    Posts
    2
    Articles
    0
    Excel Version
    excel 2010

    Formula for amount of days within a date range that fall within a fiscal year



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

    Hi, I am trying to build a formula to calculate amount of days within a date range that fall within a fiscal year
    I have a simple formula to calculate delta days (=M1-D1) however this just provides me total days between the dates in Column M & D.

    Column A (Start Date) Column M (End Date)
    13/07/2018 16/01/2019
    Delta days for the above dates = 187

    What I now need to calculate is how many of these 187 days fall within my Fiscal year

    Fiscal year is from 01/11/2017 to 31/10/2018

    Any ideas, I have been playing with a whole bunch of "IF" formulas, but I can't get it perfect.

    Here are some sample data if you can solve this for me I would be very happy camper
    Column D Column M
    13/07/2018 16/01/2019
    11/03/2019 03/06/2019
    25/08/2017 18/09/2017
    18/05/2018 18/05/2018
    21/08/2018 26/11/2018

    FYI, If the there are no days within the Fiscal I would need it to show as 0
    Thanks again!

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,573
    Articles
    0
    Excel Version
    365
    The formula can be quite short; the likes of:
    Code:
    =MAX(0,MIN($R$2,M2)-MAX($Q$2,A2))
    See attached.
    It may need a tweak or two if it's a day out; the end of the fiscal year might really be 1/11/2018 for this calculation. See also note in cell R2.

    Check the difference in dates in column T also, especially that T22 is giving you the expected result.
    Attached Files Attached Files
    Last edited by p45cal; 2019-08-10 at 01:49 AM.

  3. #3
    Neophyte swarno01's Avatar
    Join Date
    Aug 2019
    Posts
    2
    Articles
    0
    Excel Version
    excel 2010
    Thanks this seems to have worked I will need to play with it to ensure it makes sense through out my doc.
    Much appreciated, I will let you know if it all works out or if I have additional questions.
    Thank again!

Posting Permissions

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