XL: Using Dates and Times (XE0127) Complete TextLast reviewed: February 2, 1998Article ID: Q95948 |
|
The information in this article applies to:
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. You can obtain this Application Note from the following sources:
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:
General InformationMicrosoft 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 ExcelThe 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 LaterMicrosoft 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 DatesIn 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 DatesSerial 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 FormulasTo 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 DatesTo 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 DatesTo 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 IncrementallyTo 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 TimeWhen 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 EarlierBecause 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 LaterIn 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 EquivalentTo 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*24where 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*24The result is 4.5. If the cell contains both a date and a time, use the following formula:
=(Time-INT(Time))*24For example, if cell A1 reads "6/20/96 4:30 PM", the formula would read as follows:
=(A1-INT(A1))*24The result again is 4.5.
Converting a Decimal Number to Its Date EquivalentTo 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/24where 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/24The result is 4:30.
Transferring Files Between Microsoft Excel for the Macintosh and Microsoft Excel for WindowsBy 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 MacintoshFor 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-112For 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 WindowsFor 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-193and 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 HelpFor 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 Macrosand 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 HelpThe 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.
TO OBTAIN THIS APPLICATION NOTEThe following file is available for download from the Microsoft Software Library:
~ Xe0127.exe (size: 36480 bytes)For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q119591 TITLE : How to Obtain Microsoft Support Files from Online Services
http://www.microsoft.com/worldwide/default.htm |
Additional query words:
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |