SOLVED
Home

Different results from DatedIf and DateDiff functions

%3CLINGO-SUB%20id%3D%22lingo-sub-662633%22%20slang%3D%22en-US%22%3EDifferent%20results%20from%20DatedIf%20and%20DateDiff%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-662633%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20trying%20to%20find%20out%20the%20difference%20between%20two%20dates%20(10-06-1985%20and%2001-06-2019)%20using%20these%20functions.%20%3CSTRONG%3EDatedIf%20Function%3C%2FSTRONG%3E%20returned%20the%20result%20%3CSTRONG%3E33%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EDateDiff%20function%20%3C%2FSTRONG%3Ein%3CSTRONG%3E%20VBA%3C%2FSTRONG%3E%20returned%20%3CSTRONG%3E34%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20both%20functions%20are%20the%20same.%20Can%20anyone%20here%20tell%20me%20why%20this%20difference%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-662633%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eex%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-662671%22%20slang%3D%22en-US%22%3ERe%3A%20Different%20results%20from%20DatedIf%20and%20DateDiff%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-662671%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327197%22%20target%3D%22_blank%22%3E%40Ajay_Anand%3C%2FA%3E%26nbsp%3B%2C%20IMHO%2C%20they%20have%20different%20logic.%3C%2FP%3E%0A%3CP%3EDateDif%20calculates%20complete%20years%2C%20months%2C%20etc.%20Actually%20for%20your%20sample%20it'll%20be%2033%20years%2011%20months%20and%20some%20days%2C%20return%20for%20years%20is%2033.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDateDiff%20checks%20if%20you%20over%20the%20year%2C%20month%2C%20etc.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20more%20simple%20example%2C%20if%20start%20date%2030%20Dec%202018%20and%20end%20date%2010%20Jan%202019%20when%3C%2FP%3E%0A%3CP%3EDateDif%20returns%200%20years%20(0%20years%200%20months%2011%20days)%3C%2FP%3E%0A%3CP%3EDateDiff%20returns%201%20year%20(you%20are%20over%20year%202018)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-663884%22%20slang%3D%22en-US%22%3ERe%3A%20Different%20results%20from%20DatedIf%20and%20DateDiff%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-663884%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThanks%20for%20that%20reply.%3C%2FP%3E%3CP%3EIs%20there%20any%20function%20in%20VBA%20which%20equivalent%20to%20DatedIf%20function%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-665873%22%20slang%3D%22en-US%22%3ERe%3A%20Different%20results%20from%20DatedIf%20and%20DateDiff%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-665873%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F327197%22%20target%3D%22_blank%22%3E%40Ajay_Anand%3C%2FA%3E%26nbsp%3B%2C%20I%20don't%20think%20so.%20People%20suggest%20to%20evaluate%20DateDif%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F35397822%2Fdatedif-function-in-vba%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F35397822%2Fdatedif-function-in-vba%3C%2FA%3E%2C%20but%20I%20didn't%20play%20with%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Ajay_Anand
Occasional Contributor

I was trying to find out the difference between two dates (10-06-1985 and 01-06-2019) using these functions. DatedIf Function returned the result 33 and DateDiff function in VBA returned 34

 

I thought both functions are the same. Can anyone here tell me why this difference?

3 Replies
Solution

@Ajay_Anand , IMHO, they have different logic.

DateDif calculates complete years, months, etc. Actually for your sample it'll be 33 years 11 months and some days, return for years is 33.

 

DateDiff checks if you over the year, month, etc.

 

For more simple example, if start date 30 Dec 2018 and end date 10 Jan 2019 when

DateDif returns 0 years (0 years 0 months 11 days)

DateDiff returns 1 year (you are over year 2018)

@Sergei Baklan Thanks for that reply.

Is there any function in VBA which equivalent to DatedIf function?

@Ajay_Anand , I don't think so. People suggest to evaluate DateDif https://stackoverflow.com/questions/35397822/datedif-function-in-vba, but I didn't play with that.

Related Conversations
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
Returning list from 3D array
Paul Danaher in Excel on
17 Replies
User Defined Function not showing with up "="
katrina bethea in Excel on
5 Replies