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
Ajay_Anand
New 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
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies