Microsoft Office Tutorials and References
In Depth Information
Excel provides other techniques for automatically filling in values and text. You can
even create your own customized fill series. You can use Excel’s online Help to explore
other Auto Fill options.
Working with Date Functions
Entering the current date in a worksheet might not always address a date-related issue or
need. If you want the current date to always appear in your workbook, versus the date you
may have entered when you created the workbook, you can use a Date function rather than
re-entering the current date each time you open the workbook. The Date functions provided
by Excel store and calculate dates as numeric values, representing the number of days since
January 1, 1900. For example, when you enter the date 1/1/2008 into a worksheet cell, you
are actually entering the value 39448, because that date is 39,448 days after January 1, 1900.
This method of storing dates allows you to work with dates using the same formulas you
would use to work with any value. If you want to determine the number of days between
two dates, you simply subtract one date from the other.
Excel automatically updates the values returned by the TODAY() and NOW() functions
whenever you reopen the workbook. The TODAY() function displays the current date
based on your computer’s internal clock; the NOW() function displays both the date and
time. If you want a permanent date (reflecting when the workbook was initially created,
for example), enter the date directly into the cell without using either function.
If you have additional tasks to perform with a date or time, you can use one of the
functions listed in Figure 2-22.
Date and Time functions
DATE( year , month , day )
Creates a date value for the date represented by the year , month , and
DAY( date )
Extracts the day of the month from the date value
MONTH( date )
Extracts the month number from the date value, where 1=January,
2=February, and so forth
YEAR( date )
Extracts the year number from the date value
WEEKDAY( date , [ return _ type ])
Calculates the day of the week from the date value, where 1=Sunday,
2=Monday, and so forth. To choose a different numbering scheme, set
the optional return _ type value to "1" (1=Sunday, 2=Monday, ...), "2"
(1=Monday, 2=Tuesday, ...), or "3" (0=Monday, 1=Tuesday, ...).
Displays the current date and time
Displays the current date
You can use these functions to answer such questions as: On what day of the week
does 1/1/2008 fall? You can calculate the day of the week with the WEEKDAY function as
=WEEKDAY(1/1/2008) . This formula returns the value 7, which is Saturday—the seventh
day of the week.
Because Amanda intends to use this worksheet as a model for future budgets, she wants
the date on the Documentation sheet to always display the current date. You will replace
the date you entered when you first opened the workbook with the TODAY() function.