Forum Discussion
towshin sharier
Aug 06, 2021Copper Contributor
Looking for a better alternative of Datedif () formula
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.
***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
Sort By
- mathetesSilver Contributor
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