|
E-mail Me
Syllabus
Schedule
Handouts
Assignments
All My Webs
Dept Home
|
Date and Time Functions
Excel uses Microsoft's method for calculating time.
Dates are assigned a number, with each day being 1 larger than the previous
day. January 1, 1900, at midnight, is assigned the number 0. January
15, 1900, at midnight, is assigned the number 15. October 12, 1999,
is the 36,445th day since the starting date, so it is assigned that number.
The cells showing dates are formatted to display what we
expect - "10/12/99" or "October 12, 1999", and the
contents window will show "10/12/1999", but Excel evaluates
this information as a number which you can use for calulcations.
For example, to determine what the date will be exactly 45 days from October
12, 1999, enter that date in cell A1, and then enter the formula "=A1+45"
in cell B1. The results are shown below:
Times are calculated by proportioning the day between 0
and 1. 9:30 AM is 9.5 hours into a 24 hour day, do you add 9.5/24
= 0.39583 to the day number. Excel would evaluate the date and
time "October 12, 1999 at 9:30 AM" to be 36445.39583.
Excel has several built-in functions to help you work with
dates, including:
=TODAY( ) will return the current date from the computer's
clock
=NOW( ) will return the current date and time
=DAY(date value) will return a number between 1
and 31 representing the day of the month for the date identified by date
value.
=WEEKDAY(date value) will return a number between
1 and 7 representing the day of the week for the date identified by date
value, starting with Sunday = 1.
=EOMONTH(date, months) determines in which month
date occurs, advances by then number of months given,
and determines the date of the last day in that month. For example, =EOMONTH("10/12/99",
4) determines that the date falls in October of 1999. Excel advances
4 months to February of 2000. Since 2000 is a leap year, the last
day will be 2/29/00. Excel returns that date. If you forget
to enclose the date in quotes, Excel will assume you meant to enter a
math function, and evaluate 10/12/99 as 0.00842, which is about 12 minutes
after midnight on January 1, 1900.
|
