11. Calculate Age from Given Birthday
=DATEDIF(A1,TODAY(),"y")&" Years "&DATEDIF(A1,TODAY(),"ym")&" Months
"&DATEDIF(A1,TODAY(),"md")&" Days"
12. Number to Date Format Conversion
If you have numbers like 010216 and you want to convert this to date format, then the
following formula can be used
=--TEXT(A1,"00\/00\/00") for 2 digits year
Note – Minimum 5 digits are needed for above formula to work
If you have numbers like 01022016 and you want to convert this to date format, then the
following formula can be used
=--TEXT(A1,"00\/00\/0000") for 4 digits year
Note – Minimum 7 digits are needed for above formula to work
13. Number to Time Format Conversion
If you have numbers like 1215 and you want to convert this to hh:mm format, then the
following formula can be used
=--TEXT(A1,"00\:00")
Note – Minimum 3 digits are needed for above formula to work
To convert to hh:mm:ss format
=--TEXT(A1,"00\:00\:00")
Note – Minimum 5 digits are needed for above formula to work
14. Count Cells Starting (or Ending) with a particular String
- Say you want to count all cells starting with C
=COUNTIF(A1:A10,"c*")
c* is case insensitive. Hence, it will count cells starting with both c or C.
Suppose you want to find all cells starting with Excel.
=COUNTIF(A1:A10,"excel*")
