Microsoft Office Tutorials and References
In Depth Information
Using Excel Functions
Chapter 9
Working with Formulas and Functions
NOW: If you enter the NOW function in a
cell, Excel will display the current date and
time. The date and time are dynamic in that
the current date and time will change
whenever you recalculate anything in the
worksheet. By default, Excel recalculates the
worksheet whenever any changes,
additions, or deletions are made. The NOW
function does not contain any arguments, so the
syntax is =NOW().
Today’s Date
A quick way to calculate the number of days
between two dates is to enter =NOW() or
=TODAY()in one cell and another date in
another cell. Then create a formula to subtract
the older date from the newer date. Excel
displays the difference in a date format, so you’ll
need to format the value as a number.
MONTH: The MONTH function returns the
month number of a date serial number or
a date in a cell. The syntax is =MONTH(
serial number or cell address ). If you have a
date of 19-May-99 in cell C16 and you
enter =MONTH(C16), Excel returns the
value of 5 since May is the 5th month in
the calendar year.
Figuring with Financial Functions
Financial functions perform elaborate calculations
such as returns on investments or cumulative
principal or interest on loans. Functions exist for
calculating future values or net present values on
investments and for calculating amortization. Take a look
at the PMT function and how you can use it.
NETWORKDAYS: The NETWORKDAYS
function returns the number of working days
between two dates. Working days exclude
weekends and identified holidays. The
syntax also includes an optional argument
where you can enter any additional number
of days to exclude. The syntax is =NET-
WORKDAYS( StartDate, EndDate, optional
holidays ). For example, if you enter a start date
of August 15, 1985 and an end date of
March 23, 2007 and using the standard
number of holidays, Excel returns a result of
5,637. See Figure 9-13 for another example.
The PMT function calculates the payment for a loan
based on a constant interest rate. You will need to
enter the interest rate, the number of payments,
and the amount of the loan. The syntax is
=PMT( rate,nper,pv,fv,type ) where rate is the interest
rate, nper is the number of payments and pv is the
loan amount. There are two other optional
arguments, including fv (future value), which Excel
assumes to be zero unless you enter a fv, and type ,
which refers to when the payment is due. The
following steps show you how you enter this function.
1. In separate cells, enter a loan amount, an
interest rate, and the number of payments
you intend to make. See Figure 9-14 for an
example.
Figure 9-13
Calculating days worked.
Search JabSto ::




Custom Search