Working with Dates in Excel
• Learning Outcomes
- Typing Dates in a Spreadsheet
- Times in a Spreadsheet
- Time and Date Functions in Excel
- IRR and NPV with Dates
Typing Dates in a Spreadsheet
• Excel stores dates as sequential serial numbers so that
they can be used in calculations.
• By default, January 1, 1900 is serial number 1.
• You will need to change the number format (Format
Cells) in order to display a proper date.
Typing Dates in a Spreadsheet
Example 01
a) Find the serial number of the day 1st January 2019.
b) Find the days between 1st January 2019 and 20th
June 2021.
c) Find the date which comes 100 days after 9th
January 2019.
Times in a Spreadsheet
• Excel recognizes time in 24 hour clock.
• It identifies time as a fraction of a day.
• Excel also recognizes the symbol a for “am” and p
for “pm”.
• Excel allows to enter Date and Time in a single cell.
• Similar to dates, time gaps also can be calculated.
Times in a Spreadsheet
Example 02
a) Enter 1.30 pm and 1.30 am in excel with time
format.
b) Enter 9th January 2019 1.30 pm in a single cell with
the relevant format and interpret the respective
number format.
c) Find the time between 1.38 am and 5.45pm and
interpret the respective number format.
Time and Date Functions in Excel
=Now( ) ----> Read the computer clock and reflects date and Time
=Today( ) ----> Read the computer clock and reflects date
=Date(yy,mm,dd) ----> Gives the Date entered
=Weekday( ) ----> Gives the day of the week
=Month( ) ----> Gives the month
Time and Date Functions in Excel
Example 03
a) Find today’s Date using TODAY function.
b) Enter 1st January 2019 using DATE function.
c) Find the current Date and Time using NOW function.
d) Find the day of the week;
- Today
- 1st January 2019?
e) Find the current month using MONTH function.
Time and Date Functions in Excel
Function - DATEDIF
Example 04
Find the following between 3rd April 1947 and 22nd
December 2002;
a) Number of Years
b) Number of Months
c) Number of Days
d) Number of months in excess of full no. of years
e) Number of days in excess of full no. of months
f) Number of days in excess of full no. of years
IRR and NPV with Dates
Functions – XIRR and XNPV
• These functions calculate the IRR and NPV for a series
of cash flows received on specific dates.
• IRR – 1st CF today, next CF one period hence and so on.
• NPV – 1st CF one period from now, next CF in two
periods and so on.
• Both assumes “Even Spacing of CFs”
• When the spacing of the CFs are not even, you’ll need
XNPV & XIRR.
IRR and NPV with Dates
Functions – XIRR and XNPV
How to Activate?
File → Options → Add-Ins →Analysis ToolPak →
Go → Select Analysis ToolPak → Ok
IRR and NPV with Dates
Example 05
You are planning to invest in a project with the following
CFs which will occur at the dates specify. Calculate NPV
and IRR. Date Payment
16-Feb-01 -600
05-Apr-01 100
15-Jul-01 100
22-Sep-01 100
22-Sep-02 100
22-Sep-03 100
22-Sep-04 100
22-Sep-05 100
22-Sep-06 100
22-Sep-07 100
22-Sep-08 100
22-Sep-09 100
IRR and NPV with Dates
Example 06
Calculate NPV, XNPV, IRR and XIRR and identify the
reasons for the differences.
Date Payment
16-Feb-01 -600
16-Feb-02 120
16-Feb-03 120
16-Feb-04 120
16-Feb-05 120
16-Feb-06 120
16-Feb-07 120
16-Feb-08 120
16-Feb-09 120
Thank You
