A datedif function for excel that calculates correctly

This is one of those little quirks in excel that's annoyed me for a while, there must be some method in their madness but I've yet to come across anybody that thinks it is logically the way forward; so I've written one for you to use.

The way the in-built function works is that it reports back the answer as how many months apart are the two dates so regardless of the day in the month they fall, any date in May vs any date in June the answer will be one.

Can't actually think of any real world use of this as always want to know how many full months are bewteen the two dates. For example 23-June vs 1-July is less than one month away so 0; 23-June vs 27-July would give 1. Contrast this to the excel way and you'd get the answer of 1 for both.

So explanation over we'll give give you the code, head over to the VBA editor (Alt-F11), insert...module then paste the code in:

Function DateDifm(Date1 As Date, Date2 As Date) As Date

' =====================================================================================
' Author:      Martin Childs
' Create date: 2017-03-08
' Description: Returns correct date difference in months for given inputs
' =====================================================================================

    DateDifm = DateDiff("m", Date1 - Day(Date1) + 1, Date2 - Day(Date1) + 1)
    
End Function

When you completed that head back into excel and you can use the formula:

=DateDifm(A1,B1)

Without VBA option

If you don't want to use VBA, heres how you can do it straight in an excel formula, in this example the two dates we are comparing are in A1 and A2:

= DATEDIF( EOMONTH(A1,-1)+1, B1-DAY(A1)+1, "m")

I've slightly re-written it compared to the VBA function above to have less linking within the formula, but you can do it this way also:

= DATEDIF( A1-DAY(A1)+1, B1-DAY(A1)+1, "m")

SQL equivalent

The correct way of calculating it came about when I was performing a critical calculation in SQL and in the back of my mind it was nagging me whether, as they are both from Microsoft, the DATEDIFF() function in T-SQL had the same flaw. My suspisions were correct - both calculate in the same way, but from the same post on StackOverflow I had noticed a very efficient craft bit of code which was so much shorter than everything else and from here went on to adapt it into VBA.

Here the SQL function if you are also interested:

IF OBJECT_ID (N'dbo.DateDiffm', N'FN') IS NOT NULL  
    DROP FUNCTION dbo.DateDiffm;  
GO  

-- ==================================================================================
-- Author:	Martin Childs
-- Create date: 2017-01-23
-- Description:	Returns correct date difference in months for given inputs
-- Version:	v001
-- ==================================================================================

CREATE FUNCTION dbo.DateDiffm( @Date1 as datetime, @Date2 as datetime )  
RETURNS int
AS  
BEGIN

Return (
	datediff(month, dateadd(day,-day(@Date1)+1,@Date1),dateadd(day,-day(@Date1)+1,@Date2))
       )
END;