Lambda - 0000 - Life Saver Lambdas

Life-Saver-Lambdas.txt

/*
TIRED OF WRITING EXCEL FORMULA AGAIN AND AGAIN, WELL EXPORT THIS LAMBDA LINK INTO YOU EXCEL WORKBOOK AND ENJOY THE USING THE CODE. DON'T FORGET THE SHARE AND LIKE.
EACH LAMBDA IS CREATED BY DIFFERENT AUTHOR, PLEASE REFER TO DETAILS WITHIN EACH FORMULA
*/

/*
--------------------- HIJRI AGE CALCULATOR ---------------------
*/
HIJRIAGE =
/*Hijri Age Calculator allows you to calculate the age of person as per arabic calander.
https://excelexciting.com/how-to-calculate-hijri-age-in-excel/
EXAMPLE:
=HIJRIAGE("3/3/1991") 
RESULT>> 31 years 10 months 25 days 
If you like to cacluate the Gregorian Age then you need enter next argument as "g"
EXAMPLE:
=HIJRIAGE("3/3/1991","g") 
RESULT>> 30 years 11 months 11 days
author,"Faraz Shaikh, Microsoft® MVP",
wesite,"www.ExcelExciting.com",
Created,"20211203",
Modified,"20220214"*/

LAMBDA(DOB,[CAL_TYPE],
  LET(      
      v_hijri_month,12,
      v_gregorian_today_date,TODAY(),
      v_synodic_month,29.530575,

      v_hijri_number_of_days_in_a_year,v_hijri_month*v_synodic_month,

      v_gregorian_age_in_days,DATEDIF(DOB,v_gregorian_today_date,"D"),
      v_gregorian_age_in_years,DATEDIF(DOB,v_gregorian_today_date,"Y"),
      v_gregorian_age_in_month,DATEDIF(DOB,v_gregorian_today_date,"YM"),
      v_gregorian_age_in_day,DATEDIF(DOB,v_gregorian_today_date,"MD"),

      v_hijri_age_years,v_gregorian_age_in_days/v_hijri_number_of_days_in_a_year,
      v_hijri_age_months,(v_hijri_age_years-INT(v_hijri_age_years))*v_hijri_month,
      v_hijri_age_days,(v_hijri_age_months-INT(v_hijri_age_months))*v_synodic_month,

      v_concat_hijri_age,CONCAT(
                        TEXT(INT(v_hijri_age_years),"00")," years ",
                        TEXT(INT(v_hijri_age_months),"00")," months ",
                        TEXT(INT(v_hijri_age_days),"00")," days"
                         ),

      v_concat_gregorian_age,CONCAT(
                        TEXT(INT(v_gregorian_age_in_years),"00")," years ",
                        TEXT(INT(v_gregorian_age_in_month),"00")," months ",
                        TEXT(INT(v_gregorian_age_in_day),"00")," days"
                         ),
    
      result,IF(ISOMITTED(CAL_TYPE),v_concat_hijri_age,IF(CAL_TYPE="g",v_concat_gregorian_age,v_concat_hijri_age)),

      result
     )
);

/*
--------------------- CBM CONVERTER ---------------------
*/
CBMCONVERTER = 
/*Calculate Cubic Meters (CBM)
Calculate Cubic Meters (CBM). CBM Converter allow you to measure your cargo volume in CBM (m³). 
CBM calcuation is widely use in shipping/logistics industry to calculate a cargo volume.

author,"Faraz Shaikh, Microsoft® MVP",
wesite,"www.ExcelExciting.com",
created,"20221129",
update1,"20230601",
update1_remarks,"update the code with number of packages",

*UOM = Unit of Measurement

lenght = select the lenght
width =  select the width
height = select the width
[number_of_packages] = this opetional argument which is by defalut 1 (one), incase if you 7 packages with the same dimensions then you need declare dimension once and declare number of packages.
[UOM_IN] = is optional argument which accepts the current UOM & convert into meters see the Example 01
if the UOM is not declared it will consider values as meter and do the calculations 

EXAMPLE 01 >> when UOM is in centimeters
=CBMCONVERTER(95,70,65,,"cm")
RESULT>> 0.43225

EXAMPLE 02 >> when UOM is in milimeters
=CBMCONVERTER(950,700,650,,"mm")
RESULT>> 0.43225

EXAMPLE 03 >> when UOM is in inches
=CBMCONVERTER(37.4016,27.5591,25.5906,,"in")
RESULT>> 0.43225

EXAMPLE 04 >> When UOM is in meter
=CBMCONVERTER(0.95,0.7,0.65)
RESULT>> 0.43225

EXAMPLE 05 >> When we declare number of packages 
=CBMCONVERTER(0.95,0.7,0.65,10)
RESULT>> 4.3225

*/
LAMBDA(lenght, width, height, [number_of_packages], [UOM_IN],
    LET(
        v_UOM_FROM, LOWER(IF(ISOMITTED(UOM_IN), "m", UOM_IN)),
        v_number_of_packages,IF(ISOMITTED(number_of_packages),1,number_of_packages),
        v_convert_lenght, CONVERT(lenght, v_UOM_FROM, "m"),
        v_convert_width, CONVERT(width, v_UOM_FROM, "m"),
        v_convert_height, CONVERT(height, v_UOM_FROM, "m"),
        v_calculate_result, v_number_of_packages * (v_convert_lenght * v_convert_width * v_convert_height),
        result, v_calculate_result,
        result
    )
);

/*
--------------------- 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 system 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

    )
);

/*
--------------------- MATHS TABLES ---------------------
*/
TABLES=
LAMBDA(Table_Of,[Until],
/* Mathematics Tables, It is fun to have our tables in excel. 
I saw many teachers are struggling to make table in word or in excel. 
So here is the simple Excel Function that can return to us the table. 
By defaults take only 12 times [Until] and you are flexible to change it as per your need.

SYNTAX: =TABLE(Table_Of, [Until])

EXAMPLE:
=TABLE(2,3)

RESULT:
02 x 01 = 02
02 x 02 = 04
02 x 03 = 06

author,"Faraz Shaikh, Microsoft® MVP",
wesite,"www.ExcelExciting.com",
Created,"20231231",
*/
LET(
    _tableNumber, Table_Of,
    _tableRows, IF( ISOMITTED(Until), 12 , Until),
    _genNumber, SEQUENCE(_tableRows, , _tableNumber, 0),
    _gentablesequence, SEQUENCE(_tableRows, , 1, 1),
    _calculatetable, TEXT(MAP(_genNumber, _gentablesequence, LAMBDA(a, b, a * b)), "00"),
    _combine, HSTACK(
        TEXT(_genNumber, "00") & " x " & TEXT(_gentablesequence, "00") & " = " & _calculatetable
    ),
    _result, _combine,
    _result
)
);

"each lambda created by different author"... "life-saver lambdas".... gives impression this text contains a variety of useful lambda, albeit the only thing it appears to contain is a single (hardly life-saving) lambda for calculating hirji age - which might only appeal to those wishing to use / determine hifri ages... is this a work in progress?