Calculate the difference between two dates

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
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
Joined
Aug 21, 2005
Messages
4,824
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Jul 10, 2006
Messages
452
This is just automatically giving me a negative value??? when it should be 13??
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

Richard
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
For the number of complete months, here's another way...

=DATEDIF(StartDate,EndDate,"M")

Hope this helps!
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452

ADVERTISEMENT

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
Joined
Apr 19, 2005
Messages
23,707
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
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
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??
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top