Serial value, date

Excel records dates and times by means of a serial value. There are two different serial value systems — the 1900 Date System, used by Excel for Windows, and the 1904 Date System, used by Excel for the Macintosh. In Excel for Windows, a date is calculated as the number of days elapsed since the base date, January 1, 1900 in Excel for the Macintosh, the base date is January 1,1904. Thus in Excel for Windows, July 1, 2001 is represented as the serial value 37073, the number of days elapsed since the base date. Dates can extend to the year 9999 but not prior to 1900 (or prior to 1904 in Excel for the Macintosh). 

Times are represented by the decimal part of the serial number. You can use either number formatting or worksheet functions to convert these date serial values into comprehensible dates. 

Dates can also be entered using the worksheet functions DATE(yy,/nni,dcO, TODAY() or NOW(). DATE is used to enter the serial value of any date. The function TIME(AiAj, mm, ss) performs the same function for a particular time. 

TODAY()and NOW() return the serial value of today s date, or date and time, respectively. TODAY() returns an integer number (date only) while NOW() produces a number with decimal (date and time). Both functions return the serial value at the time the function was entered this value is not updated continuously but is updated each time the worksheet is recalculated. 

There are two additional functions for entering dates and times. DATEVALUE(fexf) and TIMEVALUE(fexf) convert text arguments into date serial values. For example, TIMEVALUE("8 30 PM") returns the value 0.85416667. 

The following worksheet functions operate on the date serial value to return a date or time YEAR(value), MONTH(va/ue) and DAY value). Value canhe a serial value, a cell reference or a date as text. MONTH returns a number between 1 and 12, DAY a number between 1 and 31. The text function TEXT can also be used to format a date serial value any custom date format can be applied (see "The FIXED and TEXT Functions" later in this chapter). 

There are three types of data that can be entered in the cells of the spreadsheet number, date/time, and text. For multiple entries of serial number with constant increment, enter the initial value into the first cell, highlight the cells, and select Edit -> Fill -> Series. To fill multiple entries of the same value into a range, enter the value into the first cell, move the pointer to the right-hand corner of the active cell to activate fill handle (a bold crosshair), and drag it through the range. Data are edited with the usual cut, copy, and paste operations in the Edit menu and the decimal places of scientific numbers is controlled via dialog box in Format -> Cells. 

Data sets are compilations of data, such as spectra or property tables. These data sets are often published serially as loose-leaf services, but the content is not always organized in chapters as in other serial publications. The citation of a serial data set should contain the title of the data set, the publisher, the place of publication, the date of the volume, the data entry number (as opposed to the data value), and the name of the figure or other identifying information. The page number can be included in the citation if page numbers are used in the index of the data set. 

If you keep in mind that Excel stores dates and times as date serial numbers, performing date or time arithmetic is simple. For example, in a kinetics experiment you may have a table of times at which data points were recorded at irregular intervals (Figure 3-15). To analyze the data you need the elapsed time from finitial- Subtracting the time values from the initial value yields numbers that are decimal fractions of a day and are converted into minutes by multiplying by 24 X 60. The formula in cell B2 is =(A2- A 2) 1440. 

The Step 3 dialog box (Figure 7-6) offers several options in the Column Data Format category. If you press the Date option button, a column of text values representing dates will be converted into date serial numbers. 

It is always preferable to record too much data rather than too little. Data recorded and found of no value shortly after the tests, can prove invaluable in other work, years later. Parameter values of the decanter need to be recorded in full, particularly of any special features that have been added after the original manufacture. Often these details will be encompassed, for brevity, if not confidentiality, in a serial number of the machine, plus the date of the test to fix the last modification date. All the design parameters discussed in Chapter 2 ought to be available for cross-referencing with the test data. 

---