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

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
136
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:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Can you post some sample data so we can workout a solution?
 
Upvote 0
FY 2017FY 2017FY 2017FY 2018FY 2018FY 2018FY 2018FY 2018FY 2018FY 2018FY 2018FY 2018FY 2018FY 2018FY 2018FY 2019FY 2019FY 2019FY 2019FY 2019FY 2019FY 2019FY 2019FY 2019FY 2019FY 2019FY 2019FY 2020FY 2020FY 2020FY 2020FY 2020FY 2020FY 2020FY 2020FY 2020FY 2020FY 2020FY 2020FY 2021
Savings Amount Local CurrencySavings Amount USDFY19 SavingsFY20 SavingsSavings Start DateSavings End Date01/01/172/1/20173/1/20174/1/20175/1/20176/1/20177/1/20178/1/20179/1/201710/1/201711/1/201712/1/20171/1/20182/1/20183/1/20184/1/20185/1/20186/1/20187/1/20188/1/20189/1/201810/1/201811/1/201812/1/20181/1/20192/1/20193/1/20194/1/20195/1/20196/1/20197/1/20198/1/20199/1/201910/1/201911/1/201912/1/20191/1/20202/1/20203/1/20204/1/2020
4,535.86$750.00 09/02/1808/02/19 62.5062.5062.5062.5062.5062.5062.5062.5062.5062.5062.5062.50
50,721.21$71,052.04 01/03/1812/03/18 5921.005921.005921.005921.005921.005921.005921.005921.005921.005921.005921.005921.00
17,701.00$17,701.00 11/15/1710/15/18 1475.081475.081475.081475.081475.081475.081475.081475.081475.081475.081475.081475.08
31,052.50$31,052.50 10/30/1709/30/18 2587.712587.712587.712587.712587.712587.712587.712587.712587.712587.712587.712587.71
26,291.32$7,955.29 10/01/1809/01/19 662.94662.94662.94662.94662.94662.94662.94662.94662.94662.94662.94662.94
122,214.93$36,980.08 10/01/1809/01/19 3081.673081.673081.673081.673081.673081.673081.673081.673081.673081.673081.673081.67
89,015.57$26,934.54 10/01/1809/01/19 2244.552244.552244.552244.552244.552244.552244.552244.552244.552244.552244.552244.55
80,341.04$24,309.78 10/01/1809/01/19 2025.822025.822025.822025.822025.822025.822025.822025.822025.822025.822025.822025.82
65,460.50$19,807.19 10/01/1809/01/19 1650.601650.601650.601650.601650.601650.601650.601650.601650.601650.601650.601650.60
110,754.22$33,512.27 10/01/1809/01/19 2792.692792.692792.692792.692792.692792.692792.692792.692792.692792.692792.692792.69
59,964.70$18,144.26 10/01/1809/01/19 1512.021512.021512.021512.021512.021512.021512.021512.021512.021512.021512.021512.02
167,836.43$50,784.34 10/01/1809/01/19 4232.034232.034232.034232.034232.034232.034232.034232.034232.034232.034232.034232.03
47,420.81$14,348.70 10/01/1809/01/19 1195.731195.731195.731195.731195.731195.731195.731195.731195.731195.731195.731195.73
34,071.81$26,426.00 10/16/1709/16/18 2202.172202.172202.172202.172202.172202.172202.172202.172202.172202.172202.172202.17
8,878,904.71$15,677.90 06/02/1805/02/19 1306.491306.491306.491306.491306.491306.491306.491306.491306.491306.491306.491306.49
18,225,147.01$32,181.00 06/02/1805/02/19 2681.752681.752681.752681.752681.752681.752681.752681.752681.752681.752681.752681.75
33,745,489.88$59,586.00 06/02/1805/02/19 4965.504965.504965.504965.504965.504965.504965.504965.504965.504965.504965.504965.50
1,117,940.41$1,974.00 01/10/1712/10/17164.50164.50164.50164.50164.50164.50164.50164.50164.50164.50164.50164.50
16,631.47$2,750.00 11/24/1710/24/18 229.17229.17229.17229.17229.17229.17229.17229.17229.17229.17229.17229.17
43,015.78$60,258.00 01/08/1712/08/175021.505021.505021.505021.505021.505021.505021.505021.505021.505021.505021.505021.50
7,836.04$10,977.00 01/08/1712/08/17914.75914.75914.75914.75914.75914.75914.75914.75914.75914.75914.75914.75
10,041.16$14,066.00 10/11/1709/11/18 1172.171172.171172.171172.171172.171172.171172.171172.171172.171172.171172.171172.17
5,319.34$7,451.52 06/02/1805/02/19 620.96620.96620.96620.96620.96620.96620.96620.96620.96620.96620.96620.96

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"><col span="2"><col><col span="2"><col><col span="6"><col span="3"><col span="4"></colgroup><tbody>
</tbody>
 
Upvote 0
So the result needs to calculate each row under FY19 & FY20 Savings?
 
Upvote 0
Is this the result you're after? Also dates before FY19 do you want them blank or calculated?


Book1
ABCDEF
1201820192020
2Savings Amount Local CurrencySavings Amount USDFY19 SavingsFY20 SavingsSavings Start DateSavings End Date
34,535.86$750.0051702-09-1808-02-19
450,721.21$71,052.04132503-01-1812-03-18
517,701.00$17,701.0015-11-1715-10-18
631,052.50$31,052.5030-10-1730-09-18
726,291.32$7,955.2951701-10-1809-01-19
8122,214.93$36,980.0851701-10-1809-01-19
989,015.57$26,934.5451701-10-1809-01-19
1080,341.04$24,309.7851701-10-1809-01-19
1165,460.50$19,807.1951701-10-1809-01-19
12110,754.22$33,512.2751701-10-1809-01-19
1359,964.70$18,144.2651701-10-1809-01-19
14167,836.43$50,784.3451701-10-1809-01-19
1547,420.81$14,348.7051701-10-1809-01-19
1634,071.81$26,426.0016-10-1716-09-18
178,878,904.71$15,677.9082002-06-1805-02-19
1818,225,147.01$32,181.0082002-06-1805-02-19
1933,745,489.88$59,586.0082002-06-1805-02-19
201,117,940.41$1,974.0010-01-1712-10-17
2116,631.47$2,750.0024-11-1724-10-18
2243,015.78$60,258.0008-01-1712-08-17
237,836.04$10,977.0008-01-1712-08-17
2410,041.16$14,066.0011-10-1709-11-18
255,319.34$7,451.5282002-06-1805-02-19
Sheet1
Cell Formulas
RangeFormula
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"))
 
Upvote 0
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.0051702-09-1808-02-19
450,721.21$71,052.04132503-01-1812-03-18
517,701.00$17,701.0015-11-1715-10-18
631,052.50$31,052.5030-10-1730-09-18
726,291.32$7,955.2951701-10-1809-01-19
8122,214.93$36,980.0851701-10-1809-01-19
989,015.57$26,934.5451701-10-1809-01-19
1080,341.04$24,309.7851701-10-1809-01-19
1165,460.50$19,807.1951701-10-1809-01-19
12110,754.22$33,512.2751701-10-1809-01-19
1359,964.70$18,144.2651701-10-1809-01-19
14167,836.43$50,784.3451701-10-1809-01-19
1547,420.81$14,348.7051701-10-1809-01-19
1634,071.81$26,426.0016-10-1716-09-18
178,878,904.71$15,677.9082002-06-1805-02-19
1818,225,147.01$32,181.0082002-06-1805-02-19
1933,745,489.88$59,586.0082002-06-1805-02-19
201,117,940.41$1,974.0010-01-1712-10-17
2116,631.47$2,750.0024-11-1724-10-18
2243,015.78$60,258.0008-01-1712-08-17
237,836.04$10,977.0008-01-1712-08-17
2410,041.16$14,066.0011-10-1709-11-18
255,319.34$7,451.5282002-06-1805-02-19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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"))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
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.
 
Upvote 0
Are you able to populate some of your sample rows so I can work on modifying the formula?
 
Upvote 0
Are you able to populate some of your sample rows so I can work on modifying the formula?

ABCDEF
1201820192020
2Savings Amount Local CurrencyFY18 SavingsFY19 SavingsFY20 SavingsSavings Start DateSavings End Date
34,535.86$750.0051702-09-1808-02-19
450,721.21$71,052.04132503-01-1812-03-18
517,701.005 7
015-11-1715-10-18
631,052.5066030-10-1730-09-18
726,291.32$7,955.2951701-10-1809-01-19
8122,214.93$36,980.0851701-10-1809-01-19
989,015.57$26,934.5451701-10-1809-01-19
1080,341.04$24,309.7851701-10-1809-01-19
1165,460.50$19,807.1951701-10-1809-01-19
12110,754.22$33,512.2751701-10-1809-01-19
1359,964.70$18,144.2651701-10-1809-01-19
14167,836.43$50,784.3451701-10-1809-01-19
1547,420.81$14,348.7051701-10-1809-01-19
1634,071.8166016-10-1716-09-18
178,878,904.71$15,677.9082002-06-1805-02-19
1818,225,147.01$32,181.0082002-06-1805-02-19
1933,745,489.88$59,586.0082002-06-1805-02-19
201,117,940.4106601-10-1930-09-20
21
The first two months of savings
here would have in FY2017 16,631.47​
100015-02-1714-01-18
2243,015.78$60,258.0008-01-1712-08-17
237,836.04$10,977.0008-01-1712-08-17
2410,041.16$14,066.0011-10-1709-11-18
255,319.34$7,451.5282002-06-1805-02-19

<thead>
</thead><tbody>
</tbody>

ABCDEF
1201820192020
2Savings Amount Local CurrencySavings Amount USDFY19 SavingsFY20 SavingsSavings Start DateSavings End Date
34,535.86$750.0051702-09-1808-02-19
450,721.21$71,052.04132503-01-1812-03-18
517,701.00$17,701.005015-11-1715-10-18
631,052.50$31,052.5030-10-1730-09-18
726,291.32$7,955.2951701-10-1809-01-19
8122,214.93$36,980.0851701-10-1809-01-19
989,015.57$26,934.5451701-10-1809-01-19
1080,341.04$24,309.7851701-10-1809-01-19
1165,460.50$19,807.1951701-10-1809-01-19
12110,754.22$33,512.2751701-10-1809-01-19
1359,964.70$18,144.2651701-10-1809-01-19
14167,836.43$50,784.3451701-10-1809-01-19
1547,420.81$14,348.7051701-10-1809-01-19
1634,071.81$26,426.0016-10-1716-09-18
178,878,904.71$15,677.9082002-06-1805-02-19
1818,225,147.01$32,181.0082002-06-1805-02-19
1933,745,489.88$59,586.0082002-06-1805-02-19
201,117,940.41$1,974.0010-01-1712-10-17
2116,631.47$2,750.0024-11-1724-10-18
2243,015.78$60,258.0008-01-1712-08-17
237,836.04$10,977.0008-01-1712-08-17
2410,041.16$14,066.0011-10-1709-11-18
255,319.34$7,451.5282002-06-1805-02-19

<thead>
</thead><tbody>
</tbody>

This might not be worth the agravation!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top