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

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

2. Re: Need help with formula for find time (in months) for any given financial year

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):
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.
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

3. Re: Need help with formula for find time (in months) for any given financial year

What is your Fiscal Year period? When does it start and when does it finish? I ask this because Fiscal Years vary depending upon country and company. The answers to your questions are dependant upon the Fiscal Year Start and End.

4. Re: Need help with formula for find time (in months) for any given financial year

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,

5. Re: Need help with formula for find time (in months) for any given financial year

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,

6. Re: Need help with formula for find time (in months) for any given financial year

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,

7. Re: Need help with formula for find time (in months) for any given financial year

Try this: The formulae that I used are entered to the right of the columns that you calculated.

8. Re: Need help with formula for find time (in months) for any given financial year

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

9. Re: Need help with formula for find time (in months) for any given financial year

Hi Chandni..

Just another approach.

Formula:

In you case.. its.. something like below..

Formula:

10. Re: Need help with formula for find time (in months) for any given financial year

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.

11. Re: Need help with formula for find time (in months) for any given financial year

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.

12. Re: Need help with formula for find time (in months) for any given financial year

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

13. Re: Need help with formula for find time (in months) for any given financial year

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

14. Re: Need help with formula for find time (in months) for any given financial year

When applying that formula to the worksheet that I was working on, it works for some of the dates but not others.

15. Re: Need help with formula for find time (in months) for any given financial year

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1