# Calculate the difference between two dates

#### bsnapool

##### Active Member
Hi All

Anyone halp me hear, Im trying to count the number of months between 2 dates where sometimes this can go over 12 months.

i have so far:

=MONTH(\$Z\$1)-MONTH(G2)

Any help??

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### venkat1926

##### Well-known Member
suppose start date is in A14 and end date in A15 then try this formula

=(IF(MONTH(A15)>MONTH(A14),YEAR(A15)-YEAR(A14),YEAR(A15)-YEAR(A14)-1))*12+IF(MONTH(A15)>MONTH(A14),MONTH(A15)-MONTH(A14),MONTH(A15)-MONTH(A14)+12)

#### Richard Schollar

##### MrExcel MVP
Hi

If you want the number of complete months (so anything less than a month is excluded) you could use:

=INT(DAYS360(G2,Z1)/30)

which assumes your start date is in G2, and end date in Z1.

Best regards

Richard

#### bsnapool

##### Active Member
This is just automatically giving me a negative value??? when it should be 13??

#### Richard Schollar

##### MrExcel MVP

Please list what your start and end dates are and which formula you are using.

Richard

#### Domenic

##### MrExcel MVP
For the number of complete months, here's another way...

=DATEDIF(StartDate,EndDate,"M")

Hope this helps!

#### bsnapool

##### Active Member

I have a normal formatted date in G2 and z1 is the today() formulae.

Domenic

Tried your formulae but it comes abck with a NUM error.

Richard

Your formulae comes back with a negative value in the OOO's

Thanks for your repsonses guys any other ideas?

#### Richard Schollar

##### MrExcel MVP
Could you use the html maker (link in my signature) to post an example of your dates and the formula(s) that are failing?

Richard

#### Domenic

##### MrExcel MVP
Maybe the starting date being used is later than the end date?

#### bsnapool

##### Active Member
Richard

im at work so can not convert tp html..

i have tried Domenic formulae again and it works??? Strange excel sometimes!!

Addition to this, is this any way I could add the days on to this formulae??

Replies
3
Views
444
Replies
6
Views
158
Replies
3
Views
361
Replies
1
Views
57
Replies
8
Views
123

1,141,630
Messages
5,707,520
Members
421,512
Latest member
jc364698

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back