| |
Adding Dates
You can add some number of days to a date by simply using the =SUM
function. Since Excel stores dates as
a number of days, no further work is required. For
example, to add 5 days to 1/1/98, in A1, use =A1+5,
which gives 1/6/98.
To add a number of months or years to a date, you first need to decompose the initial
date into its year, month,
and day components, add in the desired offset, and then have
Excel put the components back together.
For example, say you have a date in A1, to which you want to add 3 months and 4 days. You'd use the
following formula:
=DATE(YEAR(A1), MONTH(A1)+3, DAY(A1)+4)
Excel will automatically handle the situation which arises when you pass a number
greater than 12 to the MONTH function, or a number greater
than 31 to the DAY function.
For example, adding 6 months and 10 days to 8/25/97, in A1 with the formula
=DATE(YEAR(A1),MONTH(A1)+6, DAY(A1)+10)
gives 3/7/98.
Generally, you cannot add two dates that are in serial format. For example, adding
1/15/1998 and 6/15/1998 gives 6/30/2096, which is essentially meaningless. If you want to add
some number of days to a date, but exclude weekends and holidays, you can use the WORKDAY function, which is part of the Analysis
Tool Pack.
Note, however, that adding a month to a date may give you a result that you
do not expect. For example, suppose A1 contains the date 31-Jan-2002.
If you use the formula
=DATE(YEAR(A1), MONTH(A1)+1, DAY(A1))
you will get the date 3-March-2002, because the "31st"
day of February, 2002, is 3-March. The formula below will work around
this issue, returning the last day of the next month if the date in A1 is a
day that does not exist in the next month.
=DATE(YEAR(A1),MONTH(A1)+2,0)
For example, if A1 contains the date 31-Jan-2002, this
formula will return 28-Feb-2002. It is important that you and your
users understand what "one month later" means in the context of your
workbook.
Subtracting
Dates
You can subtract some number of days to a date by simply using the SUM function. Since Excel stores dates
as a number of days, not
further work is required. For example, to subtract 5 days to 6-Jan-98, in A1, use =A1-5,
which gives 1-Jan-98.
To subtract a number of months or years to a date, you first need to decompose the
initial date into
its year, month, and day components, subtract the desired offset, and
then let Excel put the
components back together. For example, say you have a date in A1,
to which you want to
subtract 3 months and 4 days. You'd use the following formula:
=DATE(YEAR(A1), MONTH(A1)-3, DAY(A1)-4)
Excel will automatically handle the situation which arises when you pass a number less
than 0 to the MONTH
function, or a number less than 0 to
the DAY function.
For example, subtracting 6 months and 10 days to 4/5/97, in A1
with the formula
=DATE(YEAR(A1),MONTH(A1)-6, DAY(A1)-10)
gives 9/25/96.
Generally, you cannot subtract two dates that are in serial format. If
you want to subtract some number of
days from a date, but exclude weekends and holidays, you can use the WORKDAYfunction, which is part
of the Analysis Tool Pack.
Adding Times
You can add times using the =SUM worksheet function.
Just enter all of your times as HH:MM:SS, and then
use SUM
to add them up. You may leave off the :SS if you prefer.
By default, Excel will display the sum of
times in "time-of-day" format, meaning
that adding 12:30 + 12:45 will yield 01:15. You can prevent Excel
from "rolling
over" at 24 hours by formatting the result cell as [h]:mm
which will cause it to display 25:15
rather than 01:15.
If you want to add up minutes and seconds, you must include a leading "0:" in your data. For example,
enter "0:10:20" to indicate 10 minutes, 20 seconds. When you sum
these times, Excel will display the
sum in "time-of-day" format, meaning that
adding 0:40:10 and 0:30:20 will yield 1:10:30. You can prevent
Excel form "rolling
over" at the hour by formatting the result cell as [m]:ss
which will cause it to
display 70:30 rather than 1:10:30.
Another method of adding times is to use the TIME
function. To add 1 hour, 35 minutes, 10 seconds to a time in A1, use the function
=A1 + TIME(1,35,10)
Subtracting Times
Subtracting one time from another is a little more difficult, since Excel does not
handle negative numbers as times. When you enter a time without a date, Excel assumes this
is that time of day on January 1, 1900, since it puts a 0 in for the date component of the
serial number. For example, you cannot subtract 18 hours from 4:00 PM, since this would
result in a negative number (0.67 - 0.75 = -0.83).
You can get around this by entering a full date and then subtracting a time from this,
and the formatting the result as time-only. For example to subtract 18 hours from 4:00 PM,
enter the 4:00 PM as "1/1/98 4:00 PM" and subtract 18:00 from this. Formatting
the result as hh:mm will result in "10:00
PM" which is what we would expect.
Another method is to use the TIME
function. To subtract 1 hour, 35 minutes, 10 seconds from a
time in A1, use the function =A1
- TIME(1,35,10)
Time Intervals
You can determine the number of hours and minutes between two times by subtracting the
two times. However, since Excel cannot handle negative times, you must use an
=IF
statement to adjust the time accordingly. If your times were entered without a date
(e.g, 22:30), the following statement will compute the interval between two times in
A1 and B1
.
=IF(A1>B1,B1+1-A1,B1-A1)
The "+1" in the formula causes Excel to treat
B1 as if it were in the next day, so 02:30-22:00 will
result in 4:30, four hours and thirty minutes, which is
what we would expect. To covert this to a decimal number, for example, 4.5,
indicating how many hours, multiply the result by 24 and format the cell as General or
Decimal, as in
=24*(IF(A1>B1,B1+1-A1,B1-A1))
Rounding Times
For many scheduling or payroll applications, it is useful to round times to
the nearest hour, half-hour, or quarter-hour. The MROUND
function, which
is part of the Analysis ToolPack add-in module, is very useful for this. Suppose you have a time in cell
A1.
In B1 , enter the number of minutes to
which you want to round the time -- for example, enter 30 to round to the
nearest half-hour. The formula
=TIME(HOUR(A1),MROUND(MINUTE(A1),B1),0)
will return a time rounded to the nearest half-hour, either up or down,
depending what is closest. For example, 12:14 is rounded to 12:00, and
12:15 is rounded to 12:30.
To round either up or down to the nearest interval, enter the interval in B1,
and use either of the following formulas:
=TIME(HOUR(A1),FLOOR(MINUTE(A1),B1),0)
to round to the previous interval (always going earlier, or staying the
same).
=TIME(HOUR(A1),CEILING(MINUTE(A1),B1),0)
to round to the next interval (always going later, or staying the same).
|
|