Excel: Unexpected Results Returned from Date/Time Functions

 Last reviewed: November 2, 1994
Article ID: Q52176

SUMMARY

If a date in a format such as mm/dd/yy is used as an argument to a date function, such as WEEKDAY(), DAY(), MONTH(), or YEAR(), the date argument must be enclosed in quotation marks, or the function will return an unexpected value. When a value such as 12/25/89 is passed to a function, it is read as 12 divided by 25 divided by 89. Thus, Excel returns the value represented by the serial number of the month divided by the day divided by the year.

Similarly, if a time in a format such as hh:mm is used as an argument to a time function, such as HOUR(), MINUTE() or SECOND(), the time argument must be enclosed in quotation marks, or it will return an unexpected value. Two numbers separated by a colon in a function are interpreted as a row range. Thus, Excel returns the value represented by the serial number in the row range specified by hour and minute.

MORE INFORMATION

For example, the formula 

   =WEEKDAY(12/25/89)
results in "6". This is the correct weekday for the serial number that results from 12 divided by 3 divided by 89.

The formula 

   =WEEKDAY("12/25/89")
results in the correct answer, "2", because that date was a Monday.

Similarly, the formula 

   =HOUR(1:30)
returns the hour indicated by the serial number in the first row of the column containing this formula. If the formula is entered into the row specified by the hour, the formula causes a circular reference. If the cell in the indicated row is blank, the formula returns a #VALUE! error.

KBCategory: kbother
KBSubcategory:

Additional reference words: 1.50 2.20 3.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.