Calculate Age Using DATEDIF Function

Written by kazamraza no comments

Excel Formula


=DATEDIF(A2, TODAY(), "y") &" years, "&DATEDIF(A2, TODAY(), "ym") &" months, " &DATEDIF(A2, TODAY(), "md") &" days"

 

 

Example
A B C D E
1 Data Formula Result    
2 06-Jun-90 =DATEDIF(A2, TODAY(), "y") &" years, "&DATEDIF(A2, TODAY(), "ym") &" months, " &DATEDIF(A2, TODAY(), "md") &" days" 30 years, 0 months, 17 days    
3          
4          

 

Note: Today date is assumed as 23/Jun/2020.

Which Excel file extension should I use?

Written by kazamraza no comments
Classified in : Miscellaneous Tags : none

Following are some important file types:

XLSX XLSM XLSB XLS CSV

The most common file format is XLSX. The advance users of Excel also use XLSM and XLSB to record and save Macros in Workbook.

Excel file formats

Format

Extension

Description

Excel Workbook

.xlsx

The default XML-based file format for Excel 2010 and Excel 2007. Cannot store Microsoft Visual Basic for Applications (VBA) macro code or Microsoft Office Excel 4.0 macro sheets (.xlm).

Excel Macro-Enabled Workbook (code)

.xlsm

The XML-based and macro-enabled file format for Excel 2016, Excel 2013, Excel 2010, and Excel 2007. Stores VBA macro code or Excel 4.0 macro sheets (.xlm).

Excel Binary Workbook

.xlsb

The binary file format (BIFF12) for Excel 2010 and Excel 2007.

Excel 97- Excel 2003 Workbook

.xls

The Excel 97 - Excel 2003 Binary file format (BIFF8).

XML Data

.xml

XML Data format.

Excel Add-In

.xlam

The XML-based and macro-enabled Add-In format for Excel 2010 and Excel 2007. An Add-In is a supplemental program that is designed to run additional code. Supports the use of VBA projects and Excel 4.0 macro sheets (.xlm).

Excel 97-2003 Add-In

.xla

The Excel 97-2003 Add-In, a supplemental program that is designed to run additional code. Supports the use of VBA projects.

CSV (comma delimited) .csv Saves a workbook as a comma-delimited text file for use on another Windows operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.

If you receive a file with extention .xls, it means it is Excel 97 - Excel 2003 file format, you can save as this file into new excel file format, i.e, .xlsx. In this way latest functionality is available and file size has also been reduced.

Find the Last Row of data in Excel Using INDEX and COUNTA

Written by kazamraza no comments
Classified in : Excel Formulas, Lookup Functions Tags : none

Sometime we have a requirement that we want to fetch data from last cell in a column. This task can be achieve by using

  • INDEX Function (Return the value or reference of the cell at the intersection of particular row and column in a give range)
  • COUNTA Function (Count the number of cell in a range that are not empty)

Read more Find the Last Row of data in Excel Using INDEX and COUNTA