I have dates in two columns and I would need to calculate amount of full months between two dates.

I export the spreadsheet from SAP ERP in .xls format but opening with Excel 2010. DATEDIF function is not available to me.

The amount of rows is unknown and variable. Dates are always in columns D and E. I need to repeat this operation between once a week to 1-2 times a day. I can live with all months assumed to be 30 days.

For sake of speed I have merely calculated (E2-D2)/30 but would like to make it more reliable. Formula is preferred, VBA is for interest.

2 Answers

If you want to make it accurate you can try out the formula

=(YEAR(E2)-YEAR(D2))*12+MONTH(E2)-MONTH(D2)-(DAY(E2)<DAY(D2))

the formula should be self explanatory except the last part -(DAY(E2)<DAY(D2)), which subtracts one month if it is not a complete month.

1

The formula you already have in place is about as reliable as it can get if you can live with a month averaging 30 days. You could improve it by making sure it only returns the full number

=int((e2-d2)/30) will cut off the decimals.

0

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy