XL: Using Dates and Times (XE0127) Complete TextID: Q95948
|
The following is the complete text of the application note "Using
Dates and Times," (XE0127). This Application Note describes how to use
new and existing date and time functions in Microsoft Excel 97 and
earlier. It also describes how dates and times are stored.
The following file is available for download from the Microsoft
Software Library:
~ XE0127.exe
For more information about downloading files from the Microsoft Software
Library, please see the following article in the Microsoft Knowledge Base:
Q119591 : How to Obtain Microsoft Support Files from Online Services
THE TEXT OF XE0127
==================
======================================================================
Microsoft(R) Product Support Services Application Note (Text File)
XE0127: USING DATES AND TIMES
======================================================================
Revision Date: 4/97
No Disk Included
--------------------------------------------------------------------
The information in this Application Note applies to:
- Microsoft Excel for the Macintosh( versions 1.0, 1.03, 1.04, 1.06, 1.5,
2.2, 3.0, 4.0, and 5.0
- Microsoft Excel for Windows versions 2.0, 2.1, 2.1c, 2.1d, 3.0, 4.0,
5.0, and 7.0
- Microsoft Excel 97 for Windows
General Information
===================
Microsoft Excel stores all dates as integers and all times as decimal
fractions. With this system, dates and times can be added, subtracted, or
compared like any other numbers. In this date system, the serial number 1
represents 1/2/1904 12:00 a.m. (midnight) in Microsoft Excel for the
Macintosh, or 1/1/1900 12:00:00 a.m. in Microsoft Excel for Windows. In
Microsoft Excel, all dates are manipulated using this system. Times are
stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and
.99999 is 23:59:59.
The date integers and time decimal fractions can be combined to create
numbers that have a decimal and an integer portion. For example, the number
32331.06 represents the date and time 7/8/1992 1:26:24 a.m. if you are
using the 1904 (Macintosh) date system, or 7/7/1988 1:26:24 a.m. if you are
using the 1900 (Windows) date system. To perform complex date and time
calculations, use the built-in DATE and TIME functions in Microsoft Excel.
Using the TODAY and NOW Functions in Microsoft Excel
====================================================
The TODAY function returns the serial number of today's date based on your
system clock and does not include the time. The NOW function returns the
serial number of today's date and includes the time.
New DATE Functions in Microsoft Excel Version 4.0 and Later
===========================================================
Microsoft Excel version 4.0 introduced five new date functions: EDATE,
EOMONTH, NETWORKDAYS, WORKDAY, and YEARFRAC. These functions are included
in the Analysis ToolPak add-in macro. For more information about how to
use this macro, see your User's Guide or Microsoft Excel Help.
Function name Purpose
----------------------
EDATE This function returns the serial number date that is a given
number of months before or after a stated date. Use EDATE to
calculate maturity or due dates that fall on the same day of
the month as the date of issue.
EOMONTH This function returns the serial number date for the last
day of the month that is the indicated number of months
before or after a stated date. EOMONTH can be used to
calculate maturity or due dates that fall on the last day of
the month.
NETWORKDAYS This function returns the total number of working days,
excluding weekends, and has an option to exclude holidays.
NETWORKDAYS can be used to calculate accrued employee
benefits based on the number of days between two dates.
WORKDAY This function returns a serial number date that is a given
number of working days from a stated date, excluding
weekends, and has an option to exclude holidays. WORKDAY can
be used for calculations in which weekends, holidays, or
both are to be excluded, such as the number of days worked
or invoice due dates.
YEARFRAC This function is used to calculate the fraction of a partial
year to a whole year. This fraction can be calculated based
on one of the following options: 30/360, actual/actual,
actual/360, or actual/365.
Sorting Dates
=============
In Microsoft Excel, dates are sorted based on the serial number of the
date, rather than on the displayed number. Therefore, when you sort dates
in Microsoft Excel, you may not receive the results you expect. For
example, if you sort a series of dates that are displayed in the mmmm date
format (so that only the month is displayed), the months will not be
sorted alphabetically. Instead, the dates will be sorted based on their
underlying date serial number.
Comparing Dates
===============
Serial numbers also influence date and time comparisons. For example, when
you use the NOW function to compare a date with the current date, as in
the formula
=IF(NOW()=DATEVALUE("10/1/92"),TRUE,FALSE)
the formula will return FALSE, even if the current date is 10/1/92; it
will return TRUE only when the date is 10/1/92 12:00:00 a.m. If you are
comparing two dates in a formula, and you do not need to have the time
included in the result, you can work around this behavior by using one of
the following formulas.
For Microsoft
Excel versions Use the formula
---------------------------------------
3.0 and later =IF(TODAY()=DATEVALUE("10/1/92"),TRUE,FALSE)
2.0 and earlier =IF(INT(NOW())=DATEVALUE("10/1/92"),TRUE,FALSE)
Note that the TODAY function is available only in Microsoft Excel 3.0 and
later.
Working with Formulas
=====================
Date Formulas
-------------
To find the number of days between now and a date sometime in the future,
use the following formula
="mm/dd/yy"-NOW()
where mm/dd/yy is the future date. Use the General format to format the
cell that contains the formula.
Finding the Number of Days, Months, and Years Between Two Dates
---------------------------------------------------------------
To calculate the number of days, months, and years between two dates (with
the start and end dates entered in cells A1 and A2 respectively), use the
DAY, MONTH, and YEAR functions as shown in the following formula:
=YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),AND(MONTH(A2)=MONTH(A1),
DAY(A2)<DAY(A1))),1,0)&" years, "&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2)
<=MONTH(A1),DAY(A2)<DAY(A1)),11,IF(AND(MONTH(A2)<MONTH(A1),
DAY(A2)>=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<DAY(A1)),-
1)))&" months, "&A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),
1,0),DAY(A1))&" days"
This formula can also be broken down into individual segments of days,
months, and years as follows.
To find this time segment Use this formula
---------------------------------------------------------------------------
The remaining number of =A2-DATE(YEAR(A2),MONTH(A2)-
days between two dates, IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&" days"
Ignoring year and months
The remaining number of =MONTH(A2)- MONTH(A1)+IF(AND(MONTH(A2)
months between two dates, <=MONTH(A1), DAY(A2)<DAY(A1)),11,
ignoring years and days IF(AND(MONTH(A2)<MONTH(A1),
DAY(A2)>=DAY(A1)),12,IF(AND(MONTH(A2)>
MONTH(A1),DAY(A2)<DAY(A1)),-1)))&" months"
The number of whole years
=YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),
between two dates AND(MONTH(A2)=MONTH(A1),
DAY(A2)<DAY(A1))),1,0)&" years"
NOTE: In the above formulas, &" days", &" months", and &" years" are
optional. This simply allows you to distinguish the results as days,
months, and years.
Finding the Number of WeekDays Between Two Dates
------------------------------------------------
To find the number of weekdays between two dates, where the start and end
dates are entered in cells A1 and A2 respectively, use the appropriate
formula below.
For Microsoft
Excel versions Use the formula
--------------------------------------
4.0 and later =NETWORKDAYS(A1,A2)
3.0 and earlier =A2-A1+1-INT((A2-A1+1)/7)*2-IF(INT((A2-A1+1)/7)=
(A2-A1+1)/7,0,IF(WEEKDAY(A2)<WEEKDAY(A1),2,0))-
IF(OR(WEEKDAY(A1)=1,WEEKDAY(A2)=7),1,0)
Increasing Dates Incrementally
------------------------------
To increase a date by a number of years, months, or days, use the formula
=DATE(YEAR(reference)+value1,MONTH(reference)+value2,
DAY(reference)+value3)
where reference is either the date value or cell reference that contains
the date, and value1, value2, and value3 are the increments by which you
want to increase the year, month, and day, respectively.
For example, to increase a date by one month, the formula would be
calculated as follows:
=DATE(YEAR(DATEVALUE("6/20/96")),MONTH(DATEVALUE("6/20/96"))+1,
DAY(DATEVALUE("6/20/96")))
Time Formulas
=============
Calculating Elapsed Time
------------------------
When you subtract the contents of one cell from another to find the amount
of time elapsed between them, the result will be a serial number
representing the elapsed hours, minutes, and seconds. To make this number
easier to read, use the h:mm time format to format the cell containing the
result. In the example below, if cells C2 and D2 contain the formula =B2-A2
and cell C2 is formatted in the General format (see the table below), the
cell will display a decimal number-in this case, 0.53125 (the serial number
representation of 12 hours and 45 minutes). To display the difference in
Microsoft Excel 97 for Windows, and Microsoft Excel 7.0, as it is shown in
cell D2 below, select cell D2, click Cells on the Format menu, click the
Number tab, click Time, and then click 1:30 PM in the Type list. In earlier
versions, click Number on the Format menu, and select the h:mm time number
format. For additional information on how to apply number formats, see your
User's Guide or Microsoft Excel Help.
A1: Start Time B1: End Time C1: Difference D1: Difference
(General) (h:mm)
A2: 6:30 AM B2: 7:15 PM C2: 0.53125 D2: 12:45
If midnight falls between your start time and end time, you must account
for the 24-hour time difference. You can do this by adding the number 1,
which represents one 24-hour period. For instance, you could set up the
following table, which allows for time spans beyond midnight.
A1: Start Time B1: End Time C1: Difference D1: Difference
(General) (h:mm)
A2: 7:45 PM B2: 10:30 AM C2: 0.61458333 D2: 14:45
To set up this table, type the following formula in cells C2 and D2:
=B2-A2+IF(A2>B2,1)
Accumulating Hours and Minutes Greater Than 24 Hours
====================================================
Microsoft Excel Version 4.0 and Earlier
---------------------------------------
Because Microsoft Excel uses fractions of a day to keep track of times, you
must indicate when a total will be accumulating amounts of time greater
than 24 hours. To instruct Microsoft Excel to keep accumulating the number
of hours after the total exceeds 24, use the following formula
=INT(SUM(HOUR(range))+SUM(MINUTE(range))/60)+
MOD(SUM(MINUTE(range))/60,1)*0.6
where range is the range of cells that contain the accumulated time.
Because this is an array formula, you must enter it by pressing
command+enter (in Microsoft Excel for the Macintosh) or shift+ctrl+enter
(in Microsoft Excel for Windows). The cell in which you enter this formula
should be formatted using the General format.In the example below, cells
A2 through A4 contain times formatted with the h:mm number format. Cell A5
contains the formula above.
A1: Times
A2: 12:08
A3: 8:17
A4: 13:43
A5: 34.08
If you want the time to display in a time format (h:mm:ss), use the
following formula
=INT(SUM(range*24))&":"&TEXT(MINUTE(SUM(range)),"00")&":"&
TEXT(SECOND(SUM(range)),"00")
where range is the range of cells that contain the accumulated time.
Because this is an array formula, you must enter it by pressing
command+enter (in Microsoft Excel for the Macintosh) or shift+ctrl+enter
(in Microsoft Excel for Windows).
Note that the result will be returned as Text and you will not be able to
use this in another calculation.
Microsoft Excel Version 5.0 and Later
-------------------------------------
In Microsoft Excel versions 5.0 and later, times beyond 24 hours add
correctly. You can reformat a cell containing a time greater than 24 hours
or the sum of several times that results in a time greater than 24 hours
by using the following number formats.
For Microsoft
Excel versions Use the number format
--------------------------------------------
97 [h]:mm:ss
7.0 26:30:55
5.0 37:30:55
Converting a Date to Its Decimal Number Equivalent
--------------------------------------------------
To convert a serialized date (h:mm:ss) to a decimal number (0.00), you
must convert the serial number to a decimal by converting to a 24-hour
base.
You do this by multiplying the time by 24
=Time*24
where Time is the number you want to convert from a time format to a
decimal number; this can be a cell reference or a string of numbers within
the TIMEVALUE function.
For example, if you have a time of "4:30" representing four hours and 30
minutes in cell A1, the formula would read as follows:
=A1*24
The result is 4.5. If the cell contains both a date and a time, use the
following formula:
=(Time-INT(Time))*24
For example, if cell A1 reads "6/20/96 4:30 PM", the formula would read as
follows:
=(A1-INT(A1))*24
The result again is 4.5.
Converting a Decimal Number to Its Date Equivalent
--------------------------------------------------
To convert a decimal number (0.00) to its serial date equivalent (h:mm:ss),
you must convert the serial number to a decimal by converting to a 24-hour
base. You do this by dividing the time by 24
=Time/24
where Time is the number you want to convert from a decimal number to a
date serial number and can be a cell reference or a real number. For
example, if you have a value of 4.5 representing four hours and 30 minutes
in cell A1, the formula would read as follows:
=A1/24
The result is 4:30.
Transferring Files Between Microsoft Excel for the Macintosh
and Microsoft Excel for Windows
===============================
By default, Microsoft Excel for the Macintosh uses the 1904 date system and
Microsoft Excel for Windows uses the 1900 date system. This means that when
you enter the serial number 1 in Microsoft Excel for the Macintosh and
format it as a date, it is displayed as 1/2/1904 12:00 a.m.
In Microsoft Excel for Windows, the serial number 1 is displayed as
1/1/1900 12:00 a.m. If you transfer files from one version of Microsoft
Excel to another, this difference in the date systems can cause dates to
be displayed four years and one day away from their correct date. In
Microsoft Excel 97 for Windows, and Microsoft Excel 7.0, you can change
to the 1904 date system by clicking Options on the Tools menu, clicking
the Calculation tab, and clicking 1904 Date System. In earlier versions
of Microsoft Excel for Windows, click Calculation on the Options menu
and click 1904 Date System under Sheet Options. In Microsoft Excel for
the Macintosh, you can switch to the 1900 date system by clicking
Calculation on the Options menu and clicking to clear the 1904 Date
System check box under Sheet Options.
More Information about Microsoft Excel for the Macintosh
========================================================
For more information about manipulating dates and times, see the
appropriate manual below.
Version of
Microsoft Excel Manual Title Page Numbers
-------------------------------------------------------------------
1.5 and earlier Microsoft Excel User's Guide 72-74
2.2 Microsoft Excel Reference 179-185, 591-596
3.0 Microsoft Excel User's Guide 86-94, 178-183
4.0 Microsoft Excel User's Guide 1 108-110, 114
5.0 Microsoft Excel User's Guide 111-112
For more information about a specific function, see the appropriate
resource below.
Version of
Microsoft Excel Manual Title
----------------------------------
1.5 and earlier Microsoft Excel Arrays, Functions, and Macros
2.2 Microsoft Excel Functions and Macros
3.0 Microsoft Excel Function Reference
4.0 Microsoft Excel Function Reference
5.0 On-line help
More Information About Microsoft Excel for Windows
==================================================
For more information about manipulating dates and times see the
appropriate resource below.
Version of
Microsoft Excel Manual Title Page Numbers
--------------------------------------------------------------------
2.0, 2.1, 2.1c, Microsoft Excel Reference Guide 187-193
and 2.1d
3.0 Microsoft Excel User's Guide 93-101, 186-191
4.0 Microsoft Excel User's Guide 1 108-110, 114
5.0 Microsoft Excel User's Guide 111-112
7.0 Microsoft Excel Help
97 Microsoft Excel Help
For more information about a specific function, see the appropriate
resource below.
Version of
Microsoft Excel Manual Title
----------------------------------
2.0, 2.1, 2.1c, Microsoft Excel Functions and Macros
and 2.1d
3.0 Microsoft Excel Function Reference
4.0 Microsoft Excel Function Reference
5.0 Microsoft Excel Help
7.0 Microsoft Excel Help
97 Microsoft Excel Help
The disk and software contained on it, including any accompanying
documentation (the “Software”), are provided to you at no additional
charge. Microsoft Corporation owns all rights, title, and interest in and
to the Software. The user assumes the entire risk as to the accuracy and
the use of the Software.
COPYRIGHT NOTICE. Copyright © 1997 Microsoft Corporation. Microsoft and/or
its suppliers, One Microsoft Way, Redmond, Washington 98052-6399 U.S.A.
All rights reserved.
TRADEMARKS. Microsoft, Windows, Windows NT, MSN, The Microsoft Network
and/or other Microsoft products referenced herein are either trademarks or
registered trademarks of Microsoft. Other product and company names
mentioned herein may be the trademarks of their respective owners.
The names of companies, products, people, characters and/or data mentioned
herein are fictitious and are in no way intended to represent any real
individual, company, product or event, unless otherwise noted.
NO WARRANTY. THE SOFTWARE IS PROVIDED “AS-IS,” WITHOUT WARRANTY OF ANY
KIND, AND ANY USE OF THIS SOFTWARE PRODUCT IS AT YOUR OWN RISK. TO THE
MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND ITS SUPPLIERS
DISCLAIM ALL WARRANTIES AND CONDITIONS, EITHER EXPRESS OR IMPLIED,
INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES AND CONDITIONS OF
MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE, TITLE, AND NON-
INFRINGEMENT, WITH REGARD TO THE SOFTWARE.
LIMITATION OF LIABILITY. TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW,
IN NO EVENT SHALL MICROSOFT OR ITS SUPPLIERS BE LIABLE FOR ANY SPECIAL,
INCIDENTAL, INDIRECT, OR CONSEQUENTIAL DAMAGES WHATSOEVER (INCLUDING,
WITHOUT LIMITATION, DAMAGES FOR LOSS OF BUSINESS PROFITS, BUSINESS
INTERRUPTION, LOSS OF BUSINESS INFORMATION, OR ANY OTHER PECUNIARY LOSS)
ARISING OUT OF THE USE OF OR INABILITY TO USE THE SOFTWARE, EVEN IF
MICROSOFT HAS BEEN ADVISED OF THE POSSIBLITY OF SUCH DAMAGES. BECAUSE SOME
STATES AND JURISDICTIONS DO NOT ALLOW THE EXCLUSION OR LIMITATION OF
LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES, THE ABOVE LIMITATION MAY
NOT APPLY. MICROSOFT’S ENTIRE LIABILITY AND YOUR EXCLUSIVE REMEDY UNDER
THIS EULA SHALL NOT EXCEED FIVE DOLLARS (US$5.00).
The following conditions also apply to your use of the Software:
The Software may be copied and distributed internally only, subject to the
following conditions:
All text must be copied without modification and all pages must be
included;
If software is included, all files on the disk(s) |must be copied without
modification [the MS-DOS(R) utility diskcopy is appropriate for this
purpose];
All components of this Software must be distributed together; and
This Software may not be distributed to any third party.
If you are not a Microsoft Premier customer, Microsoft shall not provide
technical support for this Software.
The Software is provided with RESTRICTED RIGHTS. Use, duplication, or
disclosure by the Government is subject to restrictions set forth in
subparagraph (c)(1)(ii) of the Rights in Technical Data and Computer
Software clause at DFARS 252.227-7013 or subparagraphs (c)(1) and (2) of
the Commercial Computer Software—Restricted Rights at 48 CFR 52.227-19, as
applicable. Manufacturer is Microsoft Corporation, One Microsoft Way,
Redmond, WA 98052-6399. Any transfer of the Software must be accompanied
by this statement and may only be transferred if first approved by
Microsoft.
You agree that you will not export or re-export the Software to any
country, person, entity or end user subject to U.S.A. export restrictions,
and you are responsible for complying with all applicable U.S. and local
export laws in connection with the use of this Software. You warrant and
represent that neither the U.S.A. Bureau of Export Administration nor any
other federal agency has suspended, revoked or denied you export
privileges.
This EULA is governed by the laws of the State of Washington, U.S.A.
Keywords : kbappnote
Version : WINDOWS:1.0,2.0,3.0,4.0,5.0,7.0,97; MACINTOSH:1.0,2.0,3.0,4.0,5.0,98
Platform : MACINTOSH WINDOWS
Issue type : kbinfo Last Reviewed: March 30, 1999