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

1. ## 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.

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

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

3. ## 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. ## Re: Determine number of months between two dates based on Fiscal year.

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

5. ## 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"))

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

Originally Posted by RasGhul
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. ## 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?

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

Originally Posted by RasGhul
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!