Months Between Two Dates

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

Difference in months between two dates

Assuming the day of the month is irrelevant (i.e. the diff between 2011.1.1 and 2010.12.31 is 1), with date1 > date2 giving a positive value and date2 > date1 a negative value

((date1.Year - date2.Year) * 12) + date1.Month - date2.Month

Or, assuming you want an approximate number of 'average months' between the two dates, the following should work for all but very huge date differences.

date1.Subtract(date2).Days / (365.25 / 12)

Note, if you were to use the latter solution then your unit tests should state the widest date range which your application is designed to work with and validate the results of the calculation accordingly.

---

Update (with thanks to Gary)

If using the 'average months' method, a slightly more accurate number to use for the 'average number of days per year' is 365.2425.

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.

Calculate how many n.months between two dates

Thanks for @Cary's and @Lam's answer.

Here is my answer to find the n month.

# try to find the minimum n month between start_date and target_date
def calc_diff(start_date, target_date)
  months_diff = (target_date.year * 12 + target_date.month) - (start_date.year * 12 + start_date.month)

  ## need to check the end of month because some special case
  ## start date: 2020-01-31 ; end date 2020-06-30
  ## the minimum n month must be 5
  ## another special case of Feb must consider (test case 15)

  if start_date.day > target_date.day && !((start_date == start_date.end_of_month || target_date.month == 2) && (target_date == target_date.end_of_month))
    months_diff = months_diff - 1
  end
  puts months_diff # it will show the minimum whole n month

  # the target_date will between inside
  # (start_date + months_diff.months) <= target_date < (start_date + (months_diff + 1).months) 
  (start_date + months_diff.months)..(start_date + (months_diff + 1).months) 
end

The Test Cases:

## test case 1
## 6/15 - 7/15  => n = 5
calc_diff(Date.parse('2020-01-15'), Date.parse('2020-06-19'))

## test case 2
## 7/15 - 8/15  => n = 6
calc_diff(Date.parse('2020-01-15'), Date.parse('2020-07-15')) 

## test case 3
## 5/15 - 6/15  => n = 4
calc_diff(Date.parse('2020-01-15'), Date.parse('2020-06-01')) 

## test case 4 (special case)
## 6/30 - 7/31  => n = 5
calc_diff(Date.parse('2020-01-31'), Date.parse('2020-06-30'))

## test case 5
## 7/30 - 8/30  => n = 4
calc_diff(Date.parse('2020-04-30'), Date.parse('2020-07-31'))

## test case 6
## 6/30 - 7/30  => n = 2
calc_diff(Date.parse('2020-04-30'), Date.parse('2020-06-30'))  

## test case 7
## 5/31 - 6/30  => n = 4
calc_diff(Date.parse('2020-01-31'), Date.parse('2020-05-31'))

## test case 8
## 2/29 - 3/31  => n = 1
calc_diff(Date.parse('2020-01-31'), Date.parse('2020-02-29'))

## test case 9
## 6/29 - 7/29  => n = 4
calc_diff(Date.parse('2020-02-29'), Date.parse('2020-06-30'))

## test case 10
## 7/29 - 8/29  => n = 5
calc_diff(Date.parse('2020-02-29'), Date.parse('2020-07-31'))

## test case 11
## 1/31 - 2/29  => n = 0
calc_diff(Date.parse('2020-01-31'), Date.parse('2020-02-28'))

## test case 12
## 2/29 - 3/31  => n = 1
calc_diff(Date.parse('2020-01-31'), Date.parse('2020-03-01'))

## test case 13
## 1/17 - 2/17  => n = 0
calc_diff(Date.parse('2020-01-17'), Date.parse('2020-01-17'))

## test case 14
## 1/17 - 2/17  => n = 0
calc_diff(Date.parse('2020-01-17'), Date.parse('2020-01-18'))

## test case 15 (special case)
## 1/30 - 2/29  => n = 1
calc_diff(Date.parse('2019-12-30'), Date.parse('2020-02-28'))

## test case 16
## 2/29 - 3/30  => n = 2
calc_diff(Date.parse('2019-12-30'), Date.parse('2020-02-29'))

Excel - count days in specific month between two dates

I was thinking that you just add a day to the end of the month unconditionally - I need to try this with one or two test cases though.

MAX(0; MIN(EOMONTH(D$1;0)+1; $B3) - MAX(D$1; $A3))

Just to spell out the four possible scenarios - given start date d1/m1/y1 (A3), end date d2/m2/y2 (B3) ,first day of current month 01/mm/yy (D1) and last day of current month dd/mm/yy (last day of month in D1)

(1) d1/m1/y1>=01/mm/yy and d2/m2/y2 <= dd/mm/yy -> d2/m2/y2 - d1/m1/y1
(Both days in current month - all nights between the two dates)

(2) d1/m1/y1 < 01/mm/yy and d2/m2/y2 <= dd/mm/yy -> d2/m2/y2 - 01/mm/yy
(Start before current month, end in current month - all nights from 1st of month up to end date)

(3) d1/m1/y1 >= 01/mm/yy and d2/m2/y2 > dd/mm/yy -> 01/(mm+1)/yy - d1/m1/y1
(Start in current month, end after current month - all nights from start date up to 1st of following month)

(4) d1/m1/y1 < 01/mm/yy and d2/m2/y2 > dd/mm/yy -> 01/(mm+1)/yy - 01/mm/yy
(Start before current month, end after current month - all nights in month).

In other words, it counts each night following a day in the range, but not the night preceding a day in the range.

Number of months between two dates

I was about to say that's simple, but difftime() stops at weeks. How odd.

So one possible answer would be to hack something up:

# turn a date into a 'monthnumber' relative to an origin
R> monnb <- function(d) { lt <- as.POSIXlt(as.Date(d, origin="1900-01-01")); \
                          lt$year*12 + lt$mon } 
# compute a month difference as a difference between two monnb's
R> mondf <- function(d1, d2) { monnb(d2) - monnb(d1) }
# take it for a spin
R> mondf(as.Date("2008-01-01"), Sys.Date())
[1] 24
R> 

Seems about right. One could wrap this into some simple class structure. Or leave it as a hack :)

Edit: Also seems to work with your examples from the Mathworks:

R> mondf("2000-05-31", "2000-06-30")
[1] 1
R> mondf(c("2002-03-31", "2002-04-30", "2002-05-31"), "2002-06-30")
[1] 3 2 1
R> 

Adding the EndOfMonth flag is left as an exercise to the reader :)

Edit 2: Maybe difftime leaves it out as there is no reliable way to express fractional difference which would be consistent with the difftime behavior for other units.

Excel - count days in each month between two dates (both dates available)

SECOND REVISION (R2)

• Updated workbook link to capture fixed column headings
• Eqn / formula also includes static view

Depending upon years spanned, R2 represents a less favourable outcome re space (will require more columns). In this case, number of additional columns required to match stacked view ~100 (!!).

FIRST REVISION (R1) etc. based upon @Charlotte.Sarah.Smith feedback

• Days in each month were 1 day less than they should have been - this has been corrected (in both screenshot, linked sheet, and relevant eqns)
• Also showcase a couple of sample illustrations/visual representations which may/not be of interest (included within linked sheet too)

---

Suppose we wanted to expand upon @Will's solution by stacking the dates by year, so that column headings can vary according to different start dates (as opposed to the very first start date that happens to appear)...

For instance, if the next row included the date range 'start = 10/02/16' through 'end = 15/03/19' - you'll appreciate that the number of columns spanning Jan-Dec ('16), Jan-Dec ('17),... up to (and incl.) the range in the first row (Jan '21 - Mar '23) becomes unwieldy.

---

By using a data table (see here) you can produce a 'stacked' view of the number of days per month regardless of the year (!) - see screenshot below and link to this [updated/corrected] worksheet.

R2 screenshot:

---

REVISION

See validation/reconciliation column at end

*Visual representations - could be useful for assessing trends etc.

---

Fun in 3D too!

1] Red font: first row that defines the construct of the data table: enter date range in the format '10/02/2021-15/08/2023'

Cell E3 eqn (drag to right):

=IFERROR(IF($A3>EDATE(E$2,1),0,MAX(,IFERROR(IF(MIN($B3,MIN(EDATE(E$2,1),$B3))=E$2-1,0,MIN($B3,MIN(EDATE(E$2,1),$B3))),"")-MAX($A3,E$2))),0)

(Similar to what we've seen previously)

2] Table below red font: enter any start date (as a regular date, e.g. dd/mm/yyyy) < end date (likewise, regular date) in columns A, B as desired/req.

---

Data Table

3] Data Table (column data): enter the following in cell c4 (drag down as req.)

=TEXT(A4,"dd/mm/yyyy")&"-"&TEXT(B4,"dd/mm/yyyy")

4] Data Table (highlight cells c3:d42, insert data table, select blank/empty cell for 'row input' and c2 for 'column input')

The data table should 'come to life' (calculate sheet, shift + F9) otherwise.

FilterXML

5] Split result by delimiter '|' using FilterXML as follows (cell E4, only drag down, not to right):

=IFERROR(TRANSPOSE(FILTERXML("<AllText><Num>"&SUBSTITUTE(LEFT(MID(D4,2,LEN(D4)-1),LEN(MID(D4,2,LEN(D4)-1))-1),"|","</Num><Num>")&"</Num></AllText>","//Num")),"")

VALIDATION

Note the check column: date difference = sum of days in table (default cell colour is otherwise RED):

REVISION 2

Here is the formula for a static version of above (i.e. no stacking by year, instead, each date in column headers are distinct re calcs) - it was already available in row with red font(!!)

=IFERROR(IF($A3>EDATE(C$2,1),0,MAX(,IFERROR(IF(MIN($B3,MIN(EDATE(C$2,1),$B3))=C$2-1,0,MIN($B3,MIN(EDATE(C$2,1),$B3))),"")-MAX($A3,C$2))),0)

Viola!

---

Related Topics