Need help with formula for find time (in months) for any given financial year

Hi,

I am quite new to this forum and just registered myself... not sure if I am doing the right thing.. need help with setting up the formula in excel (MS office 2003) for calculating the period (in months) in a particular financial year… let say

T1- 30/11/2011 and T2- 30/11/2014

Would like to know the period in Financial Year 2011-12 for the above time limits? If I do it manually, I get it as approx. 4 months.

Similarly, for FY 2012-13, this will be 12 months,

Similarly, for FY 2013-14, this will be 12 months,

Similarly, for FY 2014-15, this will be 8 months.

So if I am given two time periods and I need find out the number of months for each passing financial year for these periods, how to calculate them? I am not sure if I am still able to explain it the exact need properly, however I tried my best to explain it through the above example.

Thank you in advance for helping me with this,

Regards,

Not sure if you are looking for whole or part months but here's a quick(ish) way to go.

I set up a couple input cells for the start and end dates. I applied a named range for each just to tidy things up a bit.
I then set up a range of financial year start dates (col A) and in the column next to them (B) I put the following formula to calculate the days associated with the financial year starting on that date example formula incell B5):

Please Login or Register  to view this content.

The nested IFs do the following (in order):
- If the relative financial year starts after the end date then display nothing, otherwise:
- If the relative financial year ends before the start date then display nothing, otherwise:
- If the end date is before the end of the relative financial year the count the days from the start of this financial year to the end date (add 1 to include end date), otherwise:
- If the start date is before the start of the relative financial year then count the days in this f.year, otherwise:
- Find the days from the start date to the start of this financial year

This let me see that I was setting the logic for the ranges correctly.

I then changed the formula (in col C) to display whole months using DATEDIF in each of the result sections.

Please Login or Register  to view this content.

This is a bit limited as DATEDIF displays full calendar months. You might want to play with the days formula to get a truer representation of elapsed time.

Financial_Months.xls

Thanks for responding.

My fiscal year starts from 1 April 20XX to 31 March 20XX. For e.g. for 2012-13, it is April 1, 2012 to March 31, 2013.

Regards,

Hi.. thank you for responding with a solution...

I am trying it out.. will let you know if this works and if this is what I wanted...

Regards,

Hi, Sorry, your solution didn't work as expected... I guess I was not clear on my requirement... anyway, I have attached the file with couple of examples which should give the clear picture.

Thanks,

Thank you so much, newdoverman, Its working... just wanted to check with you if we can use "datedif" with "IF"to calculate the months in your formula, instead of traditional formula you used... I think this will give perfect numbers..

=DATEDIF(MEDIAN(C6,D$1,E$1),MEDIAN(D6,D$1,E$1),"m")

Originally Posted by newdoverman

Using Datedif in the formula that I gave you will only complicate much of what you are trying to accomplish as you are comparing the starting and ending dates and if they fall between certain dates, how many months are in the period that you are interested in. Datedif in my opinion, would only make it more complicated and more difficult to trouble-shoot if you run into problems.

I could be proven wrong (it has been done in the past and will be in the future) but, I wouldn't use it for this case.

Not really. See post #12

You are right.. its not working properly... not giving you the right numbers..