Lambda - 00004 - DATETOWORD

LAMBDA_DATETOWORD.txt

/*
--------------------- CONVERT DATE TO WORD ---------------------
*/
DATETOWORD=LAMBDA(ExcelDate,
/*Convert Date to word
We offent time need to convert a date to text form or in word from. 
With this lambda function you can easily convert a date to word form.

IMPORTANT : Entering date will be depending on your systme local settings.

author,"Faraz Shaikh, Microsoft® MVP",
wesite,"www.ExcelExciting.com",
created,"20230606",

EXAMPLE 01 >> Entering Date in the function 
=DATETOWORD("06/12/2023")
RESULT >> Twelfth June Two Thousand Twenty-Three

EXAMPLE 02 >> Reference Date to a cell
=DATETOWORD(A1)
RESULT >> Twelfth June Two Thousand Twenty-Three

EXAMPLE 03 >> Reference Date to a cell
=DATETOWORD()
RESULT >> Please Enter Date

*/

    LET(
        v_Year, TEXT(ExcelDate,"yyyy"),
        v_Date_Day_Order, {"First","Second","Third","Fourth","Fifth","Sixth","Seventh","Eighth","Nineth",
                  "Tenth","Eleventh","Twelfth","Thirteenth","Fourteenth","Fifteenth","Sixteenth",
                  "Seventeenth","Eighteenth","Nineteenth","Twentieth","Twenty-first","Twenty-second",
                  "Twenty-third","Twenty-fourth","Twenty-fifth","Twenty-sixth","Twenty-seventh",
                  "Twenty-eighth","Twenty-nineth","Thirtieth","Thirty-first"},

        v_Text_Below_Twenty, {"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten",
                   "Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen",
                   "Eighteen","Nineteen"},

        v_Tens_Text, {"Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"},

        v_Check_Decades, MID(v_Year,3,2),
        v_Calculate_Decades, IF(VALUE(v_Check_Decades)<20, INDEX(v_Text_Below_Twenty,VALUE(v_Check_Decades)+1), INDEX(v_Tens_Text,VALUE(LEFT(v_Check_Decades,1))-1) & "-" & INDEX(v_Text_Below_Twenty,VALUE(RIGHT(v_Check_Decades,1))+1)),
        
        v_Check_Hundreds, MID(v_Year,2,1),
        v_Calculate_Hundreds, IF(VALUE(v_Check_Hundreds), INDEX(v_Text_Below_Twenty,VALUE(v_Check_Hundreds)+1) & " Hundred ", ""),
        
        v_Get_Day_Name,INDEX(v_Date_Day_Order,DAY(ExcelDate)),
        v_Get_Month_Name,TEXT(ExcelDate,"mmmm"),
        v_Get_Year_Name,INDEX(v_Text_Below_Twenty,VALUE(LEFT(v_Year,1))+1) & " Thousand " & v_Calculate_Hundreds & v_Calculate_Decades,

        v_Check_Blank_Value,TRIM(IF(ISBLANK(ExcelDate),"Please Enter Date",TEXTJOIN(" ",TRUE,v_Get_Day_Name,v_Get_Month_Name,v_Get_Year_Name))),

        result,v_Check_Blank_Value,
        result

    )
);