Looking for a better alternative of Datedif () formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2619398%22%20slang%3D%22en-US%22%3ELooking%20for%20a%20better%20alternative%20of%20Datedif%20()%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2619398%22%20slang%3D%22en-US%22%3EI%20am%20using%20the%20Datedif%20formula%20for%20a%20long%20time.%20When%20I%20need%20to%20extract%20Year%2C%20Month%2C%20Day%20in%20one%20cell-like%20%2200%20Year%2C%2000%20Month%2C%20and%2000%20Day%22.%20It%20is%20pain%20to%20write%20every%20time%20when%20criteria%20change.%26nbsp%3B%20I%20am%20attaching%20my%20.xls%20file%20and%20screenshot.%3CBR%20%2F%3E%3CBR%20%2F%3E***My%20formula%20for%20datedif()%3A%26nbsp%3BDATEDIF(A2%2CB2%2C%22y%22)%26amp%3B%22%20year(s)%2C%20%22%26amp%3BDATEDIF(A2%2CB2%2C%22ym%22)%26amp%3B%22%20month(s)%2C%20%22%26amp%3BDATEDIF(A2%2CB2%2C%22md%22)%26amp%3B%22%20day(s)%22%3CBR%20%2F%3E%3CBR%20%2F%3E***My%20formula%20for%20datedif()%20excluding%20weekend%3A%26nbsp%3B%3D%2BDATEDIF(A3%2C(B3-((B3-A3)-NETWORKDAYS.INTL(A3%2CB3%2C1)))%2C%22y%22)%26amp%3B%22%20year(s)%2C%20%22%26amp%3BDATEDIF(A3%2C(B3-((B3-A3)-NETWORKDAYS.INTL(A3%2CB3%2C1)))%2C%22ym%22)%26amp%3B%22%20month(s)%2C%20%22%26amp%3BDATEDIF(A3%2C(B3-((B3-A3)-NETWORKDAYS.INTL(A3%2CB3%2C1)))%2C%22md%22)%26amp%3B%22%20day(s)%22%3CBR%20%2F%3EI%20have%20attached%20my%20file%20for%20better%20reference.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2619398%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2619481%22%20slang%3D%22en-US%22%3ERe%3A%20Looking%20for%20a%20better%20alternative%20of%20Datedif%20()%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2619481%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F114611%22%20target%3D%22_blank%22%3E%40towshin%20sharier%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20let%20me%20first%20commend%20you%20on%20a%20very%20complicated%20formula%20that%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%2C%20let%20me%20point%20you%20to%20the%20newly%20released%20LET%20function%2C%20which%20will%20enable%20you%20to%20write%20in%20a%20formula%20only%20once%20something%20like%26nbsp%3B%3CSPAN%3EDATEDIF(A3%2C(B3-((B3-A3)-NETWORKDAYS.INTL(A3%2CB3%2C1)))%20by%20giving%20it%20a%20name%20(very%20similar%20to%20naming%20a%20range%20or%20a%20cell)%20and%20then%26nbsp%3Busing%20%26nbsp%3Bthat%20name%20to%20represent%20the%20calculation%20it%20represents%2C%20all%20of%20this%20in%20the%20same%20single%20formula.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI'll%20work%20with%20it%20too%2C%20but%20thought%20you%20might%20enjoy%20exploring%20LET%20on%20your%20own%2C%20and%20you%20certainly%20are%20more%20familiar%20than%20I%20with%20the%20ins%20and%20outs%20of%20your%20formula.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-let-function%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-let-function%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Visitor
I am using the Datedif formula for a long time. When I need to extract Year, Month, Day in one cell-like "00 Year, 00 Month, and 00 Day". It is pain to write every time when criteria change.  I am attaching my .xls file and screenshot.

***My formula for datedif(): DATEDIF(A2,B2,"y")&" year(s), "&DATEDIF(A2,B2,"ym")&" month(s), "&DATEDIF(A2,B2,"md")&" day(s)"

***My formula for datedif() excluding weekend: =+DATEDIF(A3,(B3-((B3-A3)-NETWORKDAYS.INTL(A3,B3,1))),"y")&" year(s), "&DATEDIF(A3,(B3-((B3-A3)-NETWORKDAYS.INTL(A3,B3,1))),"ym")&" month(s), "&DATEDIF(A3,(B3-((B3-A3)-NETWORKDAYS.INTL(A3,B3,1))),"md")&" day(s)"
I have attached my file for better reference.
1 Reply

@towshin sharier 

 

Well, let me first commend you on a very complicated formula that works.

 

Second, let me point you to the newly released LET function, which will enable you to write in a formula only once something like DATEDIF(A3,(B3-((B3-A3)-NETWORKDAYS.INTL(A3,B3,1))) by giving it a name (very similar to naming a range or a cell) and then using  that name to represent the calculation it represents, all of this in the same single formula.

 

I'll work with it too, but thought you might enjoy exploring LET on your own, and you certainly are more familiar than I with the ins and outs of your formula. https://exceljet.net/excel-functions/excel-let-function