Microsoft Office Tutorials and References
In Depth Information
Using DATEVALUE to Convert Text Dates to Real Dates
minute — This is a number from 0 to 59, representing the minute.
second — This is a number from 0 to 59, representing the second.
As with the DATE function, Excel can handle situations in which the minute
or second argument calculates to more than 60. For example, =TIME(12,72,120)
evaluates to 1:14 PM.
Additional examples of TIME are shown in the bottom half of Figure 11.39 .
to Convert Text Dates to Real Dates
It is easy to end up with a worksheet full of text dates. Sometimes this is due
to importing data from another system. Sometimes it is caused by someone not
understanding how dates work.
If your dates are in many conceivable formats, you can use the DATEVALUE
function to convert the text dates to serial numbers, which can then be
formatted as dates.
DATEVALUE to Convert Text Dates to Real Dates
The DATEVALUE function returns the serial number of the date represented
by date_text. You use DATEVALUE to convert a date represented by text to a
serial number. The argument date_textis text that represents a date in an Ex-
cel date format. For example, "1/30/1998" and "30-Jan-1998" are text strings
within quotation marks that represent dates. Using the default date system
in Excel for Windows, date_textmust represent a date from January 1, 1900
to December 31, 9999. DATEVALUE returns a #VALUE! error if date_textis out
of this range. If the year portion of date_textis omitted, DATEVALUE uses
the current year from your computer ’ s built-in clock. Time information in