Objectives:
* Understand how Date and Time Functions are used in Excel
* Create a Database using Date and Time Functions
* Create a Countdown Timer with Date using Excel Date and Time Functions
DATES IN EXCEL
* Excel Dates can be added, subtracted and included in other calculations
* Excel stores dates in "Assigned Serial Numbers"
1 - assigned Serial Number for Date "January 1, 1900" for WINDOWS
1 - assigned Serial Number for Date "January 1, 1904" for MAC
Question:
What Date represents 30240?
Exercise 1.a. Convert these numbers into Date
143
14344
54
Exercise 1.b. Convert these Dates to Numbers
1 April 14, 2017
2 July 5, 1984
3 March 11, 2015
TIME IN EXCEL
*Times are stored as "Decimal Numbers" between 0.0 and 0.99999
*Excel stores dates in "Assigned Serial Numbers"
0.0 - 00:00:00 (12:00:00 AM)
0.99999 - 11:59:59 PM)
Exercise 1.a. Covert these Decimal Numbers to Time
1 0.4375
2 0.47197
3 0.50347
DATE FUNCTIONS USED IN EXCEL
* NOW() and TODAY () Function
=NOW() - displays current date and time
=TODAY() - displays current date only
=int(yearfrac("Cell Number of Birthdate",TODAY()))
--INT (integer-number) Rounds the number to the nearest integer
--YEARFRAC (year fraction) Returns the year fraction representing the number of whole
days between start_date and end_date
* Determining if Minor/Adult
=if(int(yearfrac("Cell Number of Birthdate",TODAY())) <18, "Minor","Adult")
* Determining the age on a specific date
=int(yearfrac("Cell Number of Birthdate",DATE(Year,Month,Day)
--MONTH should be in number EXAMPLE: October is 10, January is 1
CALCULATING RETIREMENT DATE IN EXCEL
* Create the table below:
* Determining the Retirement Date (END DATE)
=EDATE("reference cell for birthdate",12*65)
* Determining the number of Years before Retirement
=YEARFRAC(TODAY(), reference cell for end date)
* To Display only the year in the Retirement Date
=YEAR(EDATE(reference cell for birthdate, 12*60)
