Calculating Months within a specific period

[Sarah]

Hi....

Just wondering if anyone could help me with a formula or suggest how I may
do the following:

I have a list of contracts with dates from and to and would like to
calculate the number of months in each contract and which financial year they
fall in (financial year running from 01/04 - 31/03) for example:

Contract A: 01/01/08 - 01/06/09 this will have 3months in 07/08 12 in 08/09
and 2 in 09/10.

Any help will be appreciated.

Thanks

 

[ShaneDevenshire]

Hi,

I really don't understand you data or your questions. But here are some
ideas
If you want to know the number of "whole" months between two dates you can
use =DATEDIF(A1,B1,"md")
where the start date is in A1 and the end date is in B1. This gives no
credit for partial months.
You can find the Fiscal year by setting up a lookup table something like this

4/1/2008 2008
4/1/2009 2009
4/1/2010 2010
4/1/2011 2011

If this was in the cells H1:I4 then your formula would be
=VLOOKUP(B1,H$1:I$4,2,TRUE)

All this said, I don't understand how a contract that goes from 1/1/08 to
1/06/09 would have 3 months in 7/8? Assuming that 7/8 represents August 8
and you started on January 1, how do you get 3 months? Or any of the other
figures?


Well if any of this helps, please click the Yes button.

 

[Sarah]

Hi,

Thanks for your suggestion.

When i say 07/08, 08/09 i mean the financial year they fall in meaning that
the financial year of 07/08 runs from 01/04/2007 to the 31/03/2008 meaning
that in the example i gave 3 months of the contract would of fallen in the
financial year of 2007/2008 etc.

 

[ShaneDevenshire]

Hi,

I need to correct my previous formula
=DATEDIF(A1,B1,"m")
I entered "md" in my original formula, sorry.

That is clearer. So now the question becomes
suppose the contract starts on Apr 30 and today is May 1

There are three ways at least that you could calculate the number of months:
1. 0 months because no full month has passed
2. 1 month because April is finished but May is not finished
3. 2 months because we have at least 1 day in each of the two months.

The Datedif function only counts a month is you have reached the same day of
the month in the following month.


If any of this helps, please click the Yes button.

 

[Sandy Mann]

With your Contract start date in A3, the Contract end date in B3 and 2007,
2008, 2009, 2010, 2011 etc in C1:F1 respectively then try:

In C3 enter the formula:

=IF($A$3<DATE(C1,4,1),DATEDIF($A$3,DATE(C1,4,1),"m"),"")

then in D3:

=IF(AND($B$3>DATE(C1,4,1),$A$3<DATE(D1,4,1)),DATEDIF($A$3,MIN($B$3,DATE(D1,4,1)),"m")-SUM($C$3:C3),"")

and drag across as far as year dates are in Row 1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sarah]

Thanks for that thats great.

 

[Sarah]

Thats great thanks for your help

Hi,

I need to correct my previous formula
=DATEDIF(A1,B1,"m")
I entered "md" in my original formula, sorry.

That is clearer. So now the question becomes
suppose the contract starts on Apr 30 and today is May 1

There are three ways at least that you could calculate the number of months:
1. 0 months because no full month has passed
2. 1 month because April is finished but May is not finished
3. 2 months because we have at least 1 day in each of the two months.

The Datedif function only counts a month is you have reached the same day of
the month in the following month.


If any of this helps, please click the Yes button.