Hi,
I need to calculate the number of months a deal runs in 2012 dependant on the start and end date.
I have attached a file with a basic view of this with COLUMN C showing the results I would like the formula to show. Column D has a formula which works for most but not all.
As always many thanks for your help
Regards
Last edited by littlelax; 02-02-2012 at 08:55 AM.
Hi there,
You could try:
=DATEDIF(A2,B2,"m")
HTH,
Fermented R
Edit: Sorry just noticed you only need the months in 2012...will look into it!
Last edited by Fermented Reptile; 02-02-2012 at 06:49 AM.
Try this with DATEDIF function...
However, there is difference betwen your expected result and formula result:
from 1.4. to 30.6. I think there should be 3 months (whole 4th, whole 5th and whole 6th month).
Many thanks Zbor, This would give me the desired results bu when using in Excel 2007 I just get #VALUE. Is this a bug in 2007 and can it be fixed?
I'm using 2007 and it works.. Maybe you need to change date formats in formula...
I have tried changing the formats but still no joy. I have re-attached in case you are able to see the error. Thankyou for your help
I changed the format of the date text strings:
=DATEDIF(MAX(MIN(A2:B2),"1/1/2012"),MIN(MAX(A2:B2),"31/12/2012"),"m")+1
Format result as general.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Many thanks that works great.
In fact DATEDIF counts entire months between the 1st of the month and the 1st of the next monthOriginally Posted by zbor
So 1.4 till 30.6 is only 2 months for this fucntion
Yes, you are right..
But in example I've put +1 to match most of other cases and my point was that in this cases result should be 3 (and not 2).
Or, by same logic, in those could be 2 but in other one less.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks