Microsoft KB Archive/52176

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 Last reviewed: November 2, 1994 © 1998 Microsoft Corporation. All rights reserved. Terms of Use.