Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 38 Hack Excel's Date and Time Features

Excel's date and time feature
is great if you're creating simple spreadsheets, but
they can cause problems for more advanced projects. Fortunately,
there are ways to get around Excel's assumptions
when they don't meet your needs.

Excel
(by default) uses the 1900 date system. This
means the date 1 Jan 1900 has an underlying numeric value
of 1, 2 Jan 1900 has a value of 2, and so forth. These values are
called serial values in
Excel, and they enable you to use dates in calculations.

Times are very similar, but Excel treats times as decimal fractions,
with 1 being the time 24:00 or 00:00. 18:00 has a numeric value of
0.75 because it is three-quarters of 24 hours.

To see the numeric value of a date
and/or a time, format the cell
containing the value as General. For example,
the date and time 3/July/2002 3:00:00 PM has a numeric value of
37440.625, with the number after the decimal representing the time,
and the 37440 representing the serial value for 3/July/2002.

Adding Beyond 24 Hours

You
can add times by using the
SUM function (or a simple plus sign). Therefore, =SUM(A1:A5)
would result in Total Hours if A1:A5 contained valid times. There is,
however, a big "Gotcha!" Unless
told otherwise, Excel will not add past 24 hours. This is because
when a time value exceeds 24 hours (a true value of 1), it rolls into
a new day and starts again. To
force Excel not to default back to a new day after 24 hours, you can
use a cell format of 37:30:55 or a custom format of [h]:mm:ss.

You can use a similar format to get the total minutes or seconds of a
time.To get the total minutes of the time 24:00, for instance, format
the cell as [m] and you will get 1440. To get the total seconds, use a custom format
of [s] and you get 86400.

Time and Date Calculations

If you want to use these real time values in other calculations, keep
the following "magic" numbers in
mind:

60

60 minutes or 60 seconds

3600

60 secs * 60 mins

24

24 hours

1440

60 mins * 24 hours

86400

24 hours * 60 mins * 60 secs

Once you are armed with these magic numbers and the preceding
information, you'll find it's much
easier to manipulate times and dates. Take a look at the following
examples to see what we mean (assume the time is in cell A1).

If you have the number 5.50 and you really want 5:30 or 5:30 a.m.,
use this:

=A1/24

and format as needed.

If it should be 17:30 or 5:30 p.m., use this:

=(A1/24)+0.5

To achieve the oppositethat is, a decimal time from a true
timeuse this:

=A1*24

If a cell contains the true date and the true time (as in 22/Jan/03
15:36) and you want only the date, use this:

=INT(A1)

To get only the time, use this:

=A1-INT(A1)

or:

 =MOD(A1,1)

and format as needed.

To find out
the difference between two dates, use this:

=DATEDIF(A1,A2,"d")

where A1 is the earlier date.

This will produce the number of days between two dates. It also will
accept "m" or "y" as the result
to returnthat is, Months or
Years. (The DATEDIF function is
undocumented in Excel 97 and is really a Lotus 123 function.)

If you do not know in advance which
date or time is the earliest, the MIN and
MAX functions can help.
For example, to be assured of a meaningful result, you can
use this:

=DATEDIF(MIN(A1,A2),MAX(A1,A2),"d")

Also, when working with
times, you might need to account for start time and end time, with
the start time being 8:50 p.m. in
cell A1, and the end time being 9:50
a.m. in cell A2. If you subtract the
start time from the end time (=A2-A1), you get
######, as Excel, by default, cannot work with
negative times. See [Hack #74] for
more on how to work with negative times.

Alternatively, you can work around this in these two ways, ensuring a
positive result:

=MAX(A1,A2)-MIN(A1:A2)

or:

=A1-A2+IF(A1>A2,1)

You can also tell Excel to add any number of days, months, or years
to any date:

=DATE(YEAR(A1)+value1,MONTH(A1)+value2,DAY(A1)+value3)

To add one month to a date in cell A1, use this:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

Excel also offers some additional
functions that are part of the Analysis ToolPak. Click Add-Ins on the Tools menu. Click to
select the Analysis ToolPak checkbox, and then click Yes if you are
asked if you want to install it. Additional functions, such as
EDATE, EOMONTH,
NETWORKDAYS, and WEEKNUM, will
be available to you.

You can find all of these
functions under the Date & Time category of the Paste Function
dialog in the Function Wizard. The functions are easy to use; the
hard part is knowing they're available and turning
them on.

Real Dates and Times

Sometimes spreadsheets with imported
data (or data that was entered incorrectly) end up with dates and
times being seen as text and not real numbers. You can spot this
easily in Excel by widening the columns a bit, selecting a column,
selecting Format Cells
Alignment, and then changing the Horizontal alignment to
General (the default format for cells). Click OK and examine your
dates and times closely. If any are
not right-aligned, Excel
doesn't think they are dates.

To fix this, first copy any empty cell, and
then select the column and format as any Date and/or Time
format. While the column is still
selected, select Edit Paste Special Value
Add. This will force Excel to convert any text dates and
times to real dates and times. You might need to change the format
again. Another simple method is to reference the cell(s) like this:

=A1+0 or A1*1

A Date Bug?

Excel incorrectly
assumes that the year 1900 was a leap year. This means
Excel's internal date system believes there was a 29
Feb 1900, when there wasn't! The most surprising
part is that Microsoft did this intentionally, or so they
say! More information is available
at http://support.microsoft.com/default.aspx?scid=kb;EN-US;q181370.

Here are some additional good links to information on dates and times:

HOW TO: Use Dates and Times in Excel 2000

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q214094#6

Text or Number Converted to Unintended Number Format

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q214233

Maximum Times in Microsoft Excel

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q214386

Dates and Times Displayed as Serial Numbers When Viewing Formulas

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q241072

Controlling and Understanding Settings in the Format Cells Dialog Box

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q264372

How to Use Dates and Times in Microsoft Excel

http://support.microsoft.com/default.aspx?scid=kb;en-us;214094

Dates and times are probably one of the most confusing areas within
Excel. Armed with this information,
hopefully you will understand more about their many quirks and have
an easier time dealing with them.