Results 1 to 8 of 8

Thread: Determine number of months between two dates based on Fiscal year.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Location
    Youngsville, NC
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Determine number of months between two dates based on Fiscal year.

    Our fiscal year is from April 1 to March 31: example April 1, 2018, thru March 31, 2019, in FY 2019.

    We have savings initiatives that may start prior to or during those months. Savings are recorded as the average of the savings divided by 12 months: 120k savings is 10k per month.

    So if savings begin in November of 2018, the amount in FY 19 would be (using the 120K, 10K per month example), 4 months at 10k or 40k.

    I have a start and end dates in each row for the savings and the amount. What I want to do is have a formula that calculates the number of months of savings for Fiscal years: So in the example just mentioned 4 months would be for FY19 and 8 months for FY 20.

    I have a column for each FY year.

    I would prefer a formula as opposed to VBA that will return a value for FYs and I can have any amount of FY year columns as I need.

    Hope this makes sense.
    Last edited by chipgiii; Feb 6th, 2019 at 05:19 PM.

  2. #2
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    548
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Determine number of months between two dates based on Fiscal year.

    Can you post some sample data so we can workout a solution?

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Location
    Youngsville, NC
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Determine number of months between two dates based on Fiscal year.

    FY 2017 FY 2017 FY 2017 FY 2018 FY 2018 FY 2018 FY 2018 FY 2018 FY 2018 FY 2018 FY 2018 FY 2018 FY 2018 FY 2018 FY 2018 FY 2019 FY 2019 FY 2019 FY 2019 FY 2019 FY 2019 FY 2019 FY 2019 FY 2019 FY 2019 FY 2019 FY 2019 FY 2020 FY 2020 FY 2020 FY 2020 FY 2020 FY 2020 FY 2020 FY 2020 FY 2020 FY 2020 FY 2020 FY 2020 FY 2021
    Savings Amount Local Currency Savings Amount USD FY19 Savings FY20 Savings Savings Start Date Savings End Date 01/01/17 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/1/2017 8/1/2017 9/1/2017 10/1/2017 11/1/2017 12/1/2017 1/1/2018 2/1/2018 3/1/2018 4/1/2018 5/1/2018 6/1/2018 7/1/2018 8/1/2018 9/1/2018 10/1/2018 11/1/2018 12/1/2018 1/1/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019 6/1/2019 7/1/2019 8/1/2019 9/1/2019 10/1/2019 11/1/2019 12/1/2019 1/1/2020 2/1/2020 3/1/2020 4/1/2020
    4,535.86 $750.00 09/02/18 08/02/19 62.50 62.50 62.50 62.50 62.50 62.50 62.50 62.50 62.50 62.50 62.50 62.50
    50,721.21 $71,052.04 01/03/18 12/03/18 5921.00 5921.00 5921.00 5921.00 5921.00 5921.00 5921.00 5921.00 5921.00 5921.00 5921.00 5921.00
    17,701.00 $17,701.00 11/15/17 10/15/18 1475.08 1475.08 1475.08 1475.08 1475.08 1475.08 1475.08 1475.08 1475.08 1475.08 1475.08 1475.08
    31,052.50 $31,052.50 10/30/17 09/30/18 2587.71 2587.71 2587.71 2587.71 2587.71 2587.71 2587.71 2587.71 2587.71 2587.71 2587.71 2587.71
    26,291.32 $7,955.29 10/01/18 09/01/19 662.94 662.94 662.94 662.94 662.94 662.94 662.94 662.94 662.94 662.94 662.94 662.94
    122,214.93 $36,980.08 10/01/18 09/01/19 3081.67 3081.67 3081.67 3081.67 3081.67 3081.67 3081.67 3081.67 3081.67 3081.67 3081.67 3081.67
    89,015.57 $26,934.54 10/01/18 09/01/19 2244.55 2244.55 2244.55 2244.55 2244.55 2244.55 2244.55 2244.55 2244.55 2244.55 2244.55 2244.55
    80,341.04 $24,309.78 10/01/18 09/01/19 2025.82 2025.82 2025.82 2025.82 2025.82 2025.82 2025.82 2025.82 2025.82 2025.82 2025.82 2025.82
    65,460.50 $19,807.19 10/01/18 09/01/19 1650.60 1650.60 1650.60 1650.60 1650.60 1650.60 1650.60 1650.60 1650.60 1650.60 1650.60 1650.60
    110,754.22 $33,512.27 10/01/18 09/01/19 2792.69 2792.69 2792.69 2792.69 2792.69 2792.69 2792.69 2792.69 2792.69 2792.69 2792.69 2792.69
    59,964.70 $18,144.26 10/01/18 09/01/19 1512.02 1512.02 1512.02 1512.02 1512.02 1512.02 1512.02 1512.02 1512.02 1512.02 1512.02 1512.02
    167,836.43 $50,784.34 10/01/18 09/01/19 4232.03 4232.03 4232.03 4232.03 4232.03 4232.03 4232.03 4232.03 4232.03 4232.03 4232.03 4232.03
    47,420.81 $14,348.70 10/01/18 09/01/19 1195.73 1195.73 1195.73 1195.73 1195.73 1195.73 1195.73 1195.73 1195.73 1195.73 1195.73 1195.73
    34,071.81 $26,426.00 10/16/17 09/16/18 2202.17 2202.17 2202.17 2202.17 2202.17 2202.17 2202.17 2202.17 2202.17 2202.17 2202.17 2202.17
    8,878,904.71 $15,677.90 06/02/18 05/02/19 1306.49 1306.49 1306.49 1306.49 1306.49 1306.49 1306.49 1306.49 1306.49 1306.49 1306.49 1306.49
    18,225,147.01 $32,181.00 06/02/18 05/02/19 2681.75 2681.75 2681.75 2681.75 2681.75 2681.75 2681.75 2681.75 2681.75 2681.75 2681.75 2681.75
    33,745,489.88 $59,586.00 06/02/18 05/02/19 4965.50 4965.50 4965.50 4965.50 4965.50 4965.50 4965.50 4965.50 4965.50 4965.50 4965.50 4965.50
    1,117,940.41 $1,974.00 01/10/17 12/10/17 164.50 164.50 164.50 164.50 164.50 164.50 164.50 164.50 164.50 164.50 164.50 164.50
    16,631.47 $2,750.00 11/24/17 10/24/18 229.17 229.17 229.17 229.17 229.17 229.17 229.17 229.17 229.17 229.17 229.17 229.17
    43,015.78 $60,258.00 01/08/17 12/08/17 5021.50 5021.50 5021.50 5021.50 5021.50 5021.50 5021.50 5021.50 5021.50 5021.50 5021.50 5021.50
    7,836.04 $10,977.00 01/08/17 12/08/17 914.75 914.75 914.75 914.75 914.75 914.75 914.75 914.75 914.75 914.75 914.75 914.75
    10,041.16 $14,066.00 10/11/17 09/11/18 1172.17 1172.17 1172.17 1172.17 1172.17 1172.17 1172.17 1172.17 1172.17 1172.17 1172.17 1172.17
    5,319.34 $7,451.52 06/02/18 05/02/19 620.96 620.96 620.96 620.96 620.96 620.96 620.96 620.96 620.96 620.96 620.96 620.96

  4. #4
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    548
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Determine number of months between two dates based on Fiscal year.

    So the result needs to calculate each row under FY19 & FY20 Savings?

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  5. #5
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    548
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Determine number of months between two dates based on Fiscal year.

    Is this the result you're after? Also dates before FY19 do you want them blank or calculated?

    ABCDEF
    1201820192020
    2Savings Amount Local CurrencySavings Amount USDFY19 SavingsFY20 SavingsSavings Start DateSavings End Date
    34,535.86$750.00 51702-09-1808-02-19
    450,721.21$71,052.04 132503-01-1812-03-18
    517,701.00$17,701.00 15-11-1715-10-18
    631,052.50$31,052.50 30-10-1730-09-18
    726,291.32$7,955.29 51701-10-1809-01-19
    8122,214.93$36,980.08 51701-10-1809-01-19
    989,015.57$26,934.54 51701-10-1809-01-19
    1080,341.04$24,309.78 51701-10-1809-01-19
    1165,460.50$19,807.19 51701-10-1809-01-19
    12110,754.22$33,512.27 51701-10-1809-01-19
    1359,964.70$18,144.26 51701-10-1809-01-19
    14167,836.43$50,784.34 51701-10-1809-01-19
    1547,420.81$14,348.70 51701-10-1809-01-19
    1634,071.81$26,426.00 16-10-1716-09-18
    178,878,904.71$15,677.90 82002-06-1805-02-19
    1818,225,147.01$32,181.00 82002-06-1805-02-19
    1933,745,489.88$59,586.00 82002-06-1805-02-19
    201,117,940.41$1,974.00 10-01-1712-10-17
    2116,631.47$2,750.00 24-11-1724-10-18
    2243,015.78$60,258.00 08-01-1712-08-17
    237,836.04$10,977.00 08-01-1712-08-17
    2410,041.16$14,066.00 11-10-1709-11-18
    255,319.34$7,451.52 82002-06-1805-02-19

    Sheet1



    Worksheet Formulas
    CellFormula
    C3=IF(YEAR(E3)<$B$1,"",DATEDIF($E3,DATE((C$1),3,1),"m"))
    D3=IF(YEAR(E3)<$B$1,"",DATEDIF($E3,DATE((D$1),3,1),"m"))


    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  6. #6
    Board Regular
    Join Date
    Nov 2008
    Location
    Youngsville, NC
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Determine number of months between two dates based on Fiscal year.

    Quote Originally Posted by RasGhul View Post
    Is this the result you're after? Also dates before FY19 do you want them blank or calculated?

    A B C D E F
    1 2018 2019 2020
    2 Savings Amount Local Currency Savings Amount USD FY19 Savings FY20 Savings Savings Start Date Savings End Date
    3 4,535.86 $750.00 5 17 02-09-18 08-02-19
    4 50,721.21 $71,052.04 13 25 03-01-18 12-03-18
    5 17,701.00 $17,701.00 15-11-17 15-10-18
    6 31,052.50 $31,052.50 30-10-17 30-09-18
    7 26,291.32 $7,955.29 5 17 01-10-18 09-01-19
    8 122,214.93 $36,980.08 5 17 01-10-18 09-01-19
    9 89,015.57 $26,934.54 5 17 01-10-18 09-01-19
    10 80,341.04 $24,309.78 5 17 01-10-18 09-01-19
    11 65,460.50 $19,807.19 5 17 01-10-18 09-01-19
    12 110,754.22 $33,512.27 5 17 01-10-18 09-01-19
    13 59,964.70 $18,144.26 5 17 01-10-18 09-01-19
    14 167,836.43 $50,784.34 5 17 01-10-18 09-01-19
    15 47,420.81 $14,348.70 5 17 01-10-18 09-01-19
    16 34,071.81 $26,426.00 16-10-17 16-09-18
    17 8,878,904.71 $15,677.90 8 20 02-06-18 05-02-19
    18 18,225,147.01 $32,181.00 8 20 02-06-18 05-02-19
    19 33,745,489.88 $59,586.00 8 20 02-06-18 05-02-19
    20 1,117,940.41 $1,974.00 10-01-17 12-10-17
    21 16,631.47 $2,750.00 24-11-17 24-10-18
    22 43,015.78 $60,258.00 08-01-17 12-08-17
    23 7,836.04 $10,977.00 08-01-17 12-08-17
    24 10,041.16 $14,066.00 11-10-17 09-11-18
    25 5,319.34 $7,451.52 8 20 02-06-18 05-02-19
    Sheet1

    Worksheet Formulas
    Cell Formula
    C3 =IF(YEAR(E3)<$B$1,"",DATEDIF($E3,DATE((C$1),3,1),"m"))
    D3 =IF(YEAR(E3)<$B$1,"",DATEDIF($E3,DATE((D$1),3,1),"m"))
    I will look closely in a bit; the number of months should never exceed 12. I might not have been clear, but it is the number of months in a given FY that include the months within the start and end time period.

  7. #7
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    548
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Determine number of months between two dates based on Fiscal year.

    Are you able to populate some of your sample rows so I can work on modifying the formula?

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  8. #8
    Board Regular
    Join Date
    Nov 2008
    Location
    Youngsville, NC
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Determine number of months between two dates based on Fiscal year.

    Quote Originally Posted by RasGhul View Post
    Are you able to populate some of your sample rows so I can work on modifying the formula?
    A B C D E F
    1 2018 2019 2020
    2 Savings Amount Local Currency FY18 Savings FY19 Savings FY20 Savings Savings Start Date Savings End Date
    3 4,535.86 $750.00 5 17 02-09-18 08-02-19
    4 50,721.21 $71,052.04 13 25 03-01-18 12-03-18
    5 17,701.00 5 7
    0 15-11-17 15-10-18
    6 31,052.50 6 6 0 30-10-17 30-09-18
    7 26,291.32 $7,955.29 5 17 01-10-18 09-01-19
    8 122,214.93 $36,980.08 5 17 01-10-18 09-01-19
    9 89,015.57 $26,934.54 5 17 01-10-18 09-01-19
    10 80,341.04 $24,309.78 5 17 01-10-18 09-01-19
    11 65,460.50 $19,807.19 5 17 01-10-18 09-01-19
    12 110,754.22 $33,512.27 5 17 01-10-18 09-01-19
    13 59,964.70 $18,144.26 5 17 01-10-18 09-01-19
    14 167,836.43 $50,784.34 5 17 01-10-18 09-01-19
    15 47,420.81 $14,348.70 5 17 01-10-18 09-01-19
    16 34,071.81 6 6 0 16-10-17 16-09-18
    17 8,878,904.71 $15,677.90 8 20 02-06-18 05-02-19
    18 18,225,147.01 $32,181.00 8 20 02-06-18 05-02-19
    19 33,745,489.88 $59,586.00 8 20 02-06-18 05-02-19
    20 1,117,940.41 0 6 6 01-10-19 30-09-20
    21
    The first two months of savings
    here would have in FY2017 16,631.47
    10 0 0 15-02-17 14-01-18
    22 43,015.78 $60,258.00 08-01-17 12-08-17
    23 7,836.04 $10,977.00 08-01-17 12-08-17
    24 10,041.16 $14,066.00 11-10-17 09-11-18
    25 5,319.34 $7,451.52 8 20 02-06-18 05-02-19

    A B C D E F
    1 2018 2019 2020
    2 Savings Amount Local Currency Savings Amount USD FY19 Savings FY20 Savings Savings Start Date Savings End Date
    3 4,535.86 $750.00 5 17 02-09-18 08-02-19
    4 50,721.21 $71,052.04 13 25 03-01-18 12-03-18
    5 17,701.00 $17,701.00 5 0 15-11-17 15-10-18
    6 31,052.50 $31,052.50 30-10-17 30-09-18
    7 26,291.32 $7,955.29 5 17 01-10-18 09-01-19
    8 122,214.93 $36,980.08 5 17 01-10-18 09-01-19
    9 89,015.57 $26,934.54 5 17 01-10-18 09-01-19
    10 80,341.04 $24,309.78 5 17 01-10-18 09-01-19
    11 65,460.50 $19,807.19 5 17 01-10-18 09-01-19
    12 110,754.22 $33,512.27 5 17 01-10-18 09-01-19
    13 59,964.70 $18,144.26 5 17 01-10-18 09-01-19
    14 167,836.43 $50,784.34 5 17 01-10-18 09-01-19
    15 47,420.81 $14,348.70 5 17 01-10-18 09-01-19
    16 34,071.81 $26,426.00 16-10-17 16-09-18
    17 8,878,904.71 $15,677.90 8 20 02-06-18 05-02-19
    18 18,225,147.01 $32,181.00 8 20 02-06-18 05-02-19
    19 33,745,489.88 $59,586.00 8 20 02-06-18 05-02-19
    20 1,117,940.41 $1,974.00 10-01-17 12-10-17
    21 16,631.47 $2,750.00 24-11-17 24-10-18
    22 43,015.78 $60,258.00 08-01-17 12-08-17
    23 7,836.04 $10,977.00 08-01-17 12-08-17
    24 10,041.16 $14,066.00 11-10-17 09-11-18
    25 5,319.34 $7,451.52 8 20 02-06-18 05-02-19

    This might not be worth the agravation!

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
  •