Forum Discussion

towshin sharier's avatar
towshin sharier
Copper Contributor
Aug 06, 2021

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.
excel
Formulas and Functions
office 365

1 Reply

  • mathetes's avatar
    mathetes
    Silver Contributor
    Aug 06, 2021

    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

     

     

Resources