====================================================================== Microsoft Product Support Services Application Note (Text File) WE0127: Dates and Times ====================================================================== Revision Date: 2/92 No Disk Included The following information applies to Microsoft Excel for Windows version 3.0. -------------------------------------------------------------------- | INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY | | ACCOMPANY THIS DOCUMENT (collectively referred to as an | | Application Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY | | KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO | | THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A | | PARTICULAR PURPOSE. The user assumes the entire risk as to the | | accuracy and the use of this Application Note. This Application | | Note may be copied and distributed subject to the following | | conditions: 1) All text must be copied without modification and | | all pages must be included; 2) 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]; 3) All | | components of this Application Note must be distributed together; | | and 4) This Application Note may not be distributed for profit. | | | | Copyright 1992 Microsoft Corporation. All Rights Reserved. | | Microsoft, MS-DOS, and the Microsoft logo are registered | | trademarks and Windows is a trademark of Microsoft Corporation. | -------------------------------------------------------------------- General Information ------------------- Microsoft Excel for Windows uses serial numbers to keep track of dates and times. Using this serial number system, you can add, subtract, and compare dates and times as you do any other number values. This date and time system begins with the serial number 1.0, which represents 1/1/1900 0:00:00 (12:00:00 a.m.), and increases by 1.0 for every 24 elapsed hours, ending with the serial number 65380.99999, which represents 12/31/2078 23:59:59 (11:59:59 p.m.). The integer portion of the serial number represents the date, and the decimal portion represents the time. It is important to note that Microsoft Excel for Windows cannot manipulate dates that extend beyond the range of 1/1/1900 through 12/31/2078. Using a date outside of this range will result in an error. Comparing Dates and Times ------------------------- Because Excel uses serial numbers when calculating functions that involve dates and times, you may get results that are different from what you expect. For instance, the following function may return FALSE even if today's date is 1/31/92: =IF(NOW()=DATEVALUE("1/31/92"),TRUE,FALSE) This function will return TRUE only when the current date and time are 1/31/92 and 12:00:00 a.m. This is because the NOW function returns the serial number of the current date and time, which may not equal the serial number of only the date, which is returned by the DATEVALUE function. The correct formula for comparing today's date with another date is: =IF(TODAY()=DATEVALUE("1/31/92"),TRUE,FALSE) Another workable formula is: =IF(INT(NOW())=DATEVALUE("1/31/92"),TRUE,FALSE) In the preceding formula, the INT function strips the decimal portion of the serial number from the NOW function. Thus, the formula returns TRUE. Date Formulas ------------- The following example shows how the various date functions can be used: -------------------------------------------------- | | A | B | C | |--------------------------------------------------| | 1 | DATE1 | DATE2 | DIFFERENCE | |--------------------------------------------------| | 2 | 12/20/1970 | 1/20/2000 | | -------------------------------------------------- 1. To find the number of days between two dates, subtract the earlier date from the later date: =B2-A1 2. To compute the number of days between the current date and the date specified in cell B2, use: =B2-TODAY() 3. To find the number of years, months, and days between two dates, use: =YEAR(B2)-YEAR(A2)-IF(OR(MONTH(B2)