Chapter 2 - MS Excel

Chapter 2 - MS Excel
1. Important Date Functions (TODAY, NOW, YEAR, MONTH, DAY, DATE )
2. Age Calculations
3. Calculate End of Probation period using EDATE & WORKDAY
4. Calculate no. of working days between two dates (Networkdays Function)
5. Common Error types in Excel and error handling by Iferror function.
6. Substitute and Find
7. Left, Right and Mid
8. Common IS Functions : Istext, Isnumber, Isblank, Iserror
9. Len and Text


MS EXCEL - Len and Text

MS EXCEL - Len and Text

=text(value,format_text) - Convert a value to specific text format


MS EXCEL - Common IS Functions : Istext, Isnumber, Isblank, Iserror


MS EXCEL - Common IS Functions : Istext, Isnumber, Isblank, Iserror

IS functions, checks the specified value and returns TRUE or FALSE depending on the
outcome.

EXAMPLE - isblank


MS EXCEL - Left, Right and Mid

MS EXCEL - Left, Right and Mid


MS EXCEL - Substitute and Find

MS EXCEL - Substitute and Find

=substitute(text,old, new)
Substitute old text with new text

EXAMPLE: Singh is replaced with kumari
“=SUBSTITUTE(A89,““Singh””,““Kumari””)“ Arti Kumari Chauhan

=Find(find_text,text) Find the position of one text within other text
EXAMPLE: g is at 9 position in Arti Singh Chauhan

9 =FIND(“g”,A89)

MS EXCEL - Common Error types in Excel and error handling by Iferror function.

MS EXCEL - Common Error types in Excel and error handling by Iferror function.

#NAME? error
The #NAME? error occurs when Excel does not recognize text in a formula.

EXAMPLE: SU to SUM

#VALUE! error
Excel displays the #VALUE! error when a formula has the wrong type of argument.

#DIV/0! error
Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.

#REF! error
Excel displays the #REF! error when a formula refers to a cell that is not valid.

EXAMPLE: Delete B column it will give error

MS EXCEL - Calculate no. of working days between two dates (Networkdays Function)

MS EXCEL - Calculate no. of working days between two dates (Networkdays Function)

Calculate employee benefits that accrue based on the number of days worked during a
specific term. The calculation includes all weekdays (Mon - Fri)

=NETWORKDAYS(startDate,endDate,[holidays])

holidays - [optional] A list of one or more dates that should be considered non-work days.
E.g- 25 Sept 2015 is written as 25

Note that the start_date, end_date and [holidays] arguments should be input as
either:
References to cells containing dates or Dates returned from formulas.

- If you attempt to input these date arguments as text, Excel may misinterpret them, due to
different date systems, or date interpretation settings Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2012,5,23) for the 23rd day of May, 2012. Problems can occur if dates are entered as text.

MS EXCEL - Calculate End of Probation period using EDATE & WORKDAY

MS EXCEL - Calculate End of Probation period using EDATE & WORKDAY

Returns no of month before or after the start date.

=EDATE(Start_date,Months) , Months can be positive or negative.

So type in “=EDATE(“, click on the cell that contains their start date, type a comma, then
enter the number of months. 3 months? 6 months? Type a 3 or a 6 or whatever it is. Then
close your brackets “)” and press enter.

Done.

MS EXCEL - Age Calculations

MS EXCEL - Age Calculations

=datedif(d1,d2,“x”)

(input in cells)
D1- old date
D2- new date

x- It can be Y, M, D, YM, MD
Y- Return no of year between 2 dates
M- Return no. of months between 2 dates
D - Return no. of days between 2 dates
YM- Return no. of month in current year
MD- Return no of days in current months

Example –

Date1 - 10/29/1992 (Written in cell C14)
Date2 - 10/25/2015 (Written in cell C16)

=CONCATENATE(DATEDIF(C14,C16,“Y”),” Years “,DATEDIF(C14,C16,“YM”),”
Months “,DATEDIF(C14,C16,“MD”),” Days”)

22 Years 11 Months 26 Days

Important Date Functions (TODAY, NOW, YEAR, MONTH, DAY, DATE )

1. Important Date Functions (TODAY, NOW, YEAR, MONTH, DAY, DATE )


A
B
C
D
TODAY
Returns Current Date
=TODAY()
11/8/015
NOW
Returns Current Date
=NOW()
11/29/2015
17:43
MONTH
Returns numeric month from date
=MONTH (D4)
11
YEAR
Returns year in YYYY format
from date
=YEAR(D4)
2015
DAY
Returns numeric day from date
=DAY(D4)
29
DATE
=DATE(YEAR,MONTH,DAY)
=DATE(2015,4,1)
4/1/2015