Best Way to Find the Months Between Two Dates

Best way to find the months between two dates

Update 2018-04-20: it seems that OP @Joshkunz was asking for finding which months are between two dates, instead of "how many months" are between two dates. So I am not sure why @JohnLaRooy is upvoted for more than 100 times. @Joshkunz indicated in the comment under the original question he wanted the actual dates [or the months], instead of finding the total number of months.

So it appeared the question wanted, for between two dates 2018-04-11 to 2018-06-01

Apr 2018, May 2018, June 2018 

And what if it is between 2014-04-11 to 2018-06-01? Then the answer would be

Apr 2014, May 2014, ..., Dec 2014, Jan 2015, ..., Jan 2018, ..., June 2018

So that's why I had the following pseudo code many years ago. It merely suggested using the two months as end points and loop through them, incrementing by one month at a time. @Joshkunz mentioned he wanted the "months" and he also mentioned he wanted the "dates", without knowing exactly, it was difficult to write the exact code, but the idea is to use one simple loop to loop through the end points, and incrementing one month at a time.

The answer 8 years ago in 2010:

If adding by a week, then it will approximately do work 4.35 times the work as needed. Why not just:

1. get start date in array of integer, set it to i: [2008, 3, 12], 
       and change it to [2008, 3, 1]
2. get end date in array: [2010, 10, 26]
3. add the date to your result by parsing i
       increment the month in i
       if month is >= 13, then set it to 1, and increment the year by 1
   until either the year in i is > year in end_date, 
           or (year in i == year in end_date and month in i > month in end_date)

just pseduo code for now, haven't tested, but i think the idea along the same line will work.

How to get full months between two dates in javascript

You can use something like this:

function monthDiff(d1, d2) {    var months;    months = (d2.getFullYear() - d1.getFullYear()) * 12;    months -= d1.getMonth() + 1;    months += d2.getMonth();    return months <= 0 ? 0 : months;}
var result = monthDiff(    new Date(2008, 10, 4),     new Date(2010, 2, 12)  );
document.getElementById("result").innerHTML = result

<span id="result"></span>

How do I calculate the number of months between two dates in a specific year

Assumptions

Your start date and months are both strings in separate columns.
You can convert them to actual dates in a helper column.
Data is layed out per image below.

General

I am just laying out my solution in a simplified method. You can in turn nest formulas, hard code items, etc to suit your needs

Process

Convert your start date from a string in A6 to an actual excel date and place it in D6. I simply used the following formula, but other methods exist and may be needed depending on system settings.

=DATEVALUE(A6)

Calculate the end date based on month offset and place it in E6. To achieve this I used the following formula which is different then DATEDIFF which you seem to understand.

=EOMONTH(D6,LEFT(B6,FIND(" ",B6)-1)-2)+1

Place the given year you are looking for in a designated cell. In this case I chose F1.

Based on the integer that is placed in F1 generate an actual date for the first month of the year and for the last month of the year. I placed these in F2 and F3 respectively.

=DATE(F1,1,1)

=DATE(F1,12,1)

Note:  These can easily be embedded in formulas but can be cleaner and make formulas easier to read.

Next you have 6 conditions to test for:

1. The entire period is before the given year => 0
2. The entire period is after the given year => 0
3. The period starts before the given year and finishes after the end of the given year => 12
4. The period starts before the given year and finishes within the given year => formula 1
5. The period starts within the given year and finishes after the given year => formula 2
6. The period starts within the given year and finishes within the given year = formula 3

I am going to approach this with a nested IF and will explain it in steps. Condition 1 and 2 both result in 0. Therefore check if either condition is true and return 0 if so:

=IF(OR(E6<$F$2,D6>$F$3),0,

Note the formula is not finished. The false part of the if was not supplied and its where we can check condition 3.

=IF(OR(E6<$F$2,D6>$F$3),0,IF(AND(D6<=$F$2,E6>=$F$3),12,

So this checks if the Start and end date span the current year. Note I use <= and >= because if the period start date is equal to the start of the current year and or the end date is equal to the end of the current year its the same result and saves going through a math calculation later. Again the false side has not been provided. To fill this part in, you need to check condition 4.

Check to see if the period start date is before the given year. You do not need to check the end date at the same time because if the end date is after you know it was already caught by your previous condition 3 check. If it is, the month of the end date will tell you how many months were in the given year. The month of the end of the period calculation is formula 1

=IF(OR(E6<$F$2,D6>$F$3),0,IF(AND(D6<=$F$2,E6>=$F$3),12,IF(D6<=$F$2,MONTH(E6),

Now you now the next condition check already knows you period start date is after the start of the year otherwise it would have already been caught by one of the previous checks. what you need is to determine if then end of the period is to the end of the year, and calculate the number of month from start of period to end of year which is formula 2.

=IF(OR(E6<$F$2,D6>$F$3),0,IF(AND(D6<=$F$2,E6>=$F$3),12,IF(D6<=$F$2,MONTH(E6),IF(E6>=$F$3,12-MONTH(D6)+1,

There are no further conditions to check as you have caught all the possibilities except one, the final condition. All that needs to be determined is is formula 3 and close all the brackets for your nested IFs.

=IF(OR(E6<$F$2,D6>$F$3),0,IF(AND(D6<=$F$2,E6>=$F$3),12,IF(D6<=$F$2,MONTH(E6),IF(E6>=$F$3,12-MONTH(D6)+1,MONTH(E6)-MONTH(D6)+1))))

Place the formula above in G6 and copy down as required.

This process is not the only way just the first one I went to. Others may have a more elegant solution.

Difference in Months between two dates in JavaScript

The definition of "the number of months in the difference" is subject to a lot of interpretation. :-)

You can get the year, month, and day of month from a JavaScript date object. Depending on what information you're looking for, you can use those to figure out how many months are between two points in time.

For instance, off-the-cuff:

function monthDiff(d1, d2) {
    var months;
    months = (d2.getFullYear() - d1.getFullYear()) * 12;
    months -= d1.getMonth();
    months += d2.getMonth();
    return months <= 0 ? 0 : months;
}

function monthDiff(d1, d2) {    var months;    months = (d2.getFullYear() - d1.getFullYear()) * 12;    months -= d1.getMonth();    months += d2.getMonth();    return months <= 0 ? 0 : months;}
function test(d1, d2) {    var diff = monthDiff(d1, d2);    console.log(        d1.toISOString().substring(0, 10),        "to",        d2.toISOString().substring(0, 10),        ":",        diff    );}
test(    new Date(2008, 10, 4), // November 4th, 2008    new Date(2010, 2, 12)  // March 12th, 2010);// Result: 16
test(    new Date(2010, 0, 1),  // January 1st, 2010    new Date(2010, 2, 12)  // March 12th, 2010);// Result: 2
test(    new Date(2010, 1, 1),  // February 1st, 2010    new Date(2010, 2, 12)  // March 12th, 2010);// Result: 1

How to get Months between two dates

Use this code

$start    = new DateTime('2012-10-21');
$start->modify('first day of this month');
$end      = new DateTime('2017-12-05');
$end->modify('first day of next month');
$interval = DateInterval::createFromDateString('1 month');
$period   = new DatePeriod($start, $interval, $end);

foreach ($period as $dt) {
    echo $dt->format("m-Y") . "<br>\n";
}

Elegant way to get the count of months between two dates?

For PHP >= 5.3

$d1 = new DateTime("2009-09-01");
$d2 = new DateTime("2010-05-01");

var_dump($d1->diff($d2)->m); // int(4)
var_dump($d1->diff($d2)->m + ($d1->diff($d2)->y*12)); // int(8)

DateTime::diff returns a DateInterval object

If you don't run with PHP 5.3 or higher, I guess you'll have to use unix timestamps :

$d1 = "2009-09-01";
$d2 = "2010-05-01";

echo (int)abs((strtotime($d1) - strtotime($d2))/(60*60*24*30)); // 8

But it's not very precise (there isn't always 30 days per month).

Last thing : if those dates come from your database, then use your DBMS to do this job, not PHP.

Edit: This code should be more precise if you can't use DateTime::diff or your RDBMS :

$d1 = strtotime("2009-09-01");
$d2 = strtotime("2010-05-01");
$min_date = min($d1, $d2);
$max_date = max($d1, $d2);
$i = 0;

while (($min_date = strtotime("+1 MONTH", $min_date)) <= $max_date) {
    $i++;
}
echo $i; // 8

---

Related Topics