Microsoft Office Tutorials and References
In Depth Information
Mastering Excel’s Built-in Functions
Time values also have a serial number associated with them, measured on a scale that runs
from midnight (00:00:00 in hours, minutes, and seconds), to 11:59:59 PM (or 23:59:59, using
the 24-hour format). Because the value of a single day is 1, the associated serial values for
times run from 0 to 0.999988426, respectively. When you combine date and time into a
single serial value, you get a single value that you can use to perform calculations of date
and time. The serial value for 11:45 AM on September 29, 2010, is 40450.489583333. If you
enter a time without including a date, Excel uses a date serial value of 0. If you then format
the cell to show a date (or a combination of date and time), you’ll see it listed,
nonsensically, as January 0, 1900.
The SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR functions allow you to break out
the component parts of a date or time stored as a serial number. Each function takes a
single argument and returns the respective parts as a number. Thus, if cell C3 contains the
date 29-Sep-2010, the formula =MONTH(C3) returns 9 and DAY(C3) returns 29. To calculate
the day of the week, use =WEEKDAY(serial_number,[return_type]), which returns a numeric
value indicating the day of the week. If you leave the second argument blank, the result is
between 1 and 7, where 1 equals Sunday. If you prefer an alternative numbering system,
click the return_type argument and use any of the options shown here:
Two date functions are useful in nearly any worksheet. Neither one uses any arguments:
=TODAY() returns today’s date
=NOW() returns the current date and time
Search JabSto ::




Custom Search