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
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:
- The entire period is before the given year => 0
- The entire period is after the given year => 0
- The period starts before the given year and finishes after the end of the given year => 12
- The period starts before the given year and finishes within the given year => formula 1
- The period starts within the given year and finishes after the given year => formula 2
- 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.
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.
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
Count the number of months between two dates
This may help you:
$dateFrom = '2021/07/20'; // Suppose dateFrom is older than dateTo
$dateTo = '2021/08/10';
$from = new DateTime($dateFrom);
$to = new DateTime($dateTo);
$monthsDiff = $to->format('m') - $from->format('m');
$yearsDiff = $to->format('Y') - $from->format('Y');
if ($monthsDiff < 0){
$yearsDiff = $yearsDiff - 1;
}
echo ((($monthsDiff + 12) % 12) + 1) + $yearsDiff * 12;
how can I calculate month count between two dates
For getting the days, try this:
$begintime = '2012-12-19';
$endtime = '2013-02-22';
$bd = new DateTime($begintime);
$ed = new DateTime($endtime);
$c = $bd->format('t') - $bd->format('d') + 1;
$pass = false;
while($bd->format('Y') < $ed->format('Y')
|| $bd->format('n') < $ed->format('n')) {
$bd->modify("+1 month");
echo $c." ";
$c = $bd->format('t');
$pass = true;
}
$c = $ed->format('d');
if(!$pass)
$c -= $bd->format('d') - 1;
echo $c;
See http://ideone.com/07wqkp
$bd->format('t')
gives the maximum number of days in a month.
ideone uses PHP 5.2.11. I suppose with PHP 5.4 you could use
$bd->add(new DateInterval("P1M"));
instead of $bd->modify("+1 month");
.
EDIT: Fixed bug when starting and ending in the same month and year.
EDIT: Reverted to explicit comparisons. On second thought, it's better without the if/else.
How to get the months difference of two dates
Actually, when you print_r()
the interval object, you'll see this:
DateInterval Object
(
[y] => 1
[m] => 7 // that 7 months is just a component of it
[d] => 6
[h] => 14
[i] => 12
[s] => 9
[weekday] => 0
[weekday_behavior] => 0
[first_last_day_of] => 0
[invert] => 0
[days] => 585
[special_type] => 0
[special_amount] => 0
[have_weekday_relative] => 0
[have_special_relative] => 0
)
// its 1 year 7 months
To convert it:
$date1 = new DateTime(); // no need to put `date(Y-m-d)` just create that object, its already undestood its today
$date2 = new DateTime('2013-04-10');
$interval = $date2->diff($date1);
echo '<pre>' . print_r($interval, true) . '</pre>';
$months = $interval->m + ($interval->y * 12);
echo $months;
Pandas - Number of Months Between Two Dates
Here is a very simple answer my friend:
df['nb_months'] = ((df.date2 - df.date1)/np.timedelta64(1, 'M'))
and now:
df['nb_months'] = df['nb_months'].astype(int)
Related Topics
Access Controller Method from Another Controller in Laravel 5
Convert Utf8-Characters to Iso-88591 and Back in PHP
Interpolation (Double Quoted String) of Associative Arrays in PHP
Sanitizing Strings to Make Them Url and Filename Safe
PHP - Redirect and Send Data Via Post
How to Use Arrays in Curl Post Requests
PHP 5 Strpos() Difference Between Returning 0 and False
Stop People Uploading Malicious PHP Files Via Forms
Persistent/Keepalive Http With the PHP Curl Library
Byethost Server Passing HTML Values "Checking Your Browser" With Json String
How to Import a .SQL File in MySQL Database Using PHP
Rebase Array Keys After Unsetting Elements
Serializing and Submitting a Form With Jquery and PHP
Regular Expression Pattern to Match Url With or Without Http://Www
With "Magic Quotes" Disabled, Why Does PHP/Wordpress Continue to Auto-Escape My Post Data