Calculate number of months between 2 dates
I have a Start Date and an End Date, need to calculate the number of months in between. Conditions: Start date <=15th includes the month, End date = 15th includes the month. Examples: between 14-04-05 and 15-06-05: 3 months, between 16-04-05 and 13-06-05: 1 month, between 16-02-04 and 03-05-05: 14 months. Thank you. John -- john liem |
One way:
=12*(YEAR(B1)-YEAR(A1))+MONTH(B1)-MONTH(A1)+1-(DAY(A1)15)-(DAY(B1)<15) In article , john liem wrote: I have a Start Date and an End Date, need to calculate the number of months in between. Conditions: Start date <=15th includes the month, End date = 15th includes the month. Examples: between 14-04-05 and 15-06-05: 3 months, between 16-04-05 and 13-06-05: 1 month, between 16-02-04 and 03-05-05: 14 months. Thank you. John |
Use the following formula:
=(YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A1)-1+IF(DAY(A1)<=15,1,0)+IF(DAY(A2)=15,1,0) assume A1 is starting date and A2 is ending date. ===== * ===== * ===== * ===== Daniel CHEN Spreadsheet/VBA Specialist www.Geocities.com/UDQServices Try UDQ Consulting Services - Your "Impossible" Task Could Be Someone Else's "Piece of Cake" ===== * ===== * ===== * ===== "john liem" wrote in message .. . I have a Start Date and an End Date, need to calculate the number of months in between. Conditions: Start date <=15th includes the month, End date = 15th includes the month. Examples: between 14-04-05 and 15-06-05: 3 months, between 16-04-05 and 13-06-05: 1 month, between 16-02-04 and 03-05-05: 14 months. Thank you. John -- john liem |
Alternativly use the DATEDIF function
=DATEDIF(A1,A2,"M") The "M" indicates that you want the answer in Months...see Help for more info Simon Cleal ************************************************** ***** "The computer allows you to make mistakes faster than any other invention, with the possible exception of handguns and tequila" - Mitch Radcliffe ************************************************** ***** |
You probably should have tested this - DATEDIF() doesn't meet the
criteria in the problem statement. For instance, in the example the OP gave: A1: 14 Apr 2005 A2: 15 Jun 2005 DATEDIF returns 2, while by the OP's rules, the result should be 3. In article , "Simon Cleal" wrote: Alternativly use the DATEDIF function =DATEDIF(A1,A2,"M") The "M" indicates that you want the answer in Months...see Help for more info |
All times are GMT +1. The time now is 05:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com