Section 11.2. Dates


11.2. Dates

Dates also cause problems. Excel understands dates and has a number of tools for working with and displaying them. But when dates are imported, Excel may not recognize them. When this happens they are stored as text strings . If a date will not format as a date or if functions like DAY or MONTH do not work, the date was imported as text. The DATEVALUE function will solve the problem much of the time. All of the text strings can be converted to a Windows date by DATEVALUE (see Table 11-1).

Table 11-1. Date formats handled by DATEVALUE

7/4/2004

January 24, 2004

Nov 12, 2001

2004-04-15

15 December 1999

07-04-04

15 Dec 1999


At times, however, more logic is needed. If the date is in an unusual order, such as 25-03-2001 (DD-MM-YYYY), DATEVALUE will return a #VALUE! error. The LEFT, RIGHT, and MID functions can correct this kind of problem.

Julian date is a feature of some older systems. The general form is YYYYDDD. The year is in the usual four digit form (e.g., 2004), but the day is a three digit number indicating the day of the year. September 8, 2003 is the 251st day of 2003 and would appear as 2003251. The DATEVALUE function used with RIGHT and LEFT can convert a Julian date to a format that Excel can use.

If cell A1 contains the Julian date 2003251, the formula =DATEVALUE("12/31/" & LEFT(H1,4)-1)+RIGHT(H1,3) will return the Windows date. This works by building a date for the last day of the previous year, then adding the required number of days to it. DATEVALUE converts a date string, such as 12/31/2002, to a Windows date. The formula uses "12/31/" & LEFT(A1,4)-1 to build the string. LEFT(A1,4) is 2003. Then RIGHT(A1,3), which is 251, is added to it.



Analyzing Business Data with Excel
Analyzing Business Data with Excel
ISBN: 0596100736
EAN: 2147483647
Year: 2006
Pages: 101
Authors: Gerald Knight

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net