Calculate Months in a year with Start/End Dates spanning multiple year 1

[cariengon]

I am using DateDiff to find the number of months. But I need to see how many months are in each year that a contract is valid - Here's an example of the results we're looking for:

A contract starts on 6/1/03 and ends on 6/30/05. How many months in '03, how many months in '04, how many months in '05.

Our result needs to be:
2003 - 7 months
2004 - 12 months
2005 - 6 months

I have a separate column for 2003, 2004, & 2005 so I need to narrow down the results by year for each column.

Any ideas on how to do this?

 

[macropod]

More information, please:

How do you want to treat part months (eg contract starts on 7th, ends on 15th in another month)?

Also, you mention columns, which suggests you may be using Excel. If so, you could use the worksheet DateDif function (which gives different results from vba's DateDiff). So, are you using Excel and, if so, is a formula-based solution acceptable/desirable?

Cheers

 

[cariengon]

Yes, I'm using Excel. I tried to find something in help for the 'DateDif' function but could not find anything.

My end result is to calculate the Annual amount due for a year and YES I do need to take in account partial years. For example, I have a contract that starts on 5/1/03 and ends on 4/30/05. The Annual Contract Value based on 2004 is $12,000.00 I need to take the Annual Contract Value to poplulate the 2003, 2004, & 2005 fields. 2003 should be $8,000, 2004 should be $12,000 and 2005 should be $4,000.

 

[KenWright]

The only comprehensive help on Datedif I have ever seen:-

http://cpearson.com/excel/datedif.htm

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

OK, assuming your start dates are in A2:A200, and finish dates are in B2:B200, and that you have the years in say
D1:Z1 (I have a blank column in C to make it look neater <g> ), in cell D2 put the following formula and copy across and down:-

=IF(YEAR($A2)>D$1,"",IF(YEAR($B2)<D$1,"",IF(AND(YEAR($A2)<D$1,YEAR($B2)>D$1),12,IF(YEAR($A2)=D$1,13-MONTH($A2),IF(YEAR($B2)=D$1,MONTH($B2),4)))))

This will give you months per year in your year columns. If you have a project value and want it spread across the months then you will need another column that contains the value, one that contains the total number of months inclusive, and then add *(value/months) to the formula.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[cariengon]

Thank you! This worked for me!

Only one question - why do you have the '4' in the ELSE section at the end of the statment?

 

[KenWright]

Oops - meant to change that something like "Check Dates". It was just quicker to chuck a value in there whilst building it. I'm not that sure it really needs it at all to be honest, but I like checksums and if there happened to be a scenario in the data that i inadvertantly hadn't catered for then that would have picked it up and flagged it.

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[cariengon]

That makes sense. I always put something in there so I know that it's giving me the result I want.

Thanks Again!!
Carie

 

[xlbo]

Ken
MikeBowers has produced an excellent FAQ here which covers DateDiff:
faq68-4037

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Cheers Geoff - must admit I'd forgotten about that FAQ.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------