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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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)
 
Upvote 0
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
 
Upvote 0
For the number of complete months, here's another way...

=DATEDIF(StartDate,EndDate,"M")

Hope this helps!
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Maybe the starting date being used is later than the end date?
 
Upvote 0
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??
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top