PRODUCT : Quattro Pro NUMBER : 582 VERSION : All OS : DOS DATE : March 28, 1991 PAGE : 1/8 TITLE : Entering and Manipulating Date and Time Values What is a Date? In Quattro Pro, dates and times are stored as values rather than labels. The date number represents the number of days between the current date and December 30, 1899. December 30, 1899 is day zero, and each day after that has its own unique number. This number is referred to as the Date Serial Number. For example, the date serial number 33065 represents July 11, 1990 (i.e. the 33065th day since December 30, 1899). These date serial numbers allow Quattro Pro to perform mathematical calculations on dates and times. Labels, on the other hand, always have a value of zero, and therefore cannot easily be used in time calculations. Time is stored as a fraction of a day, and is always a value between zero and one. This number is commonly referred to as the Time Serial Number. For example, 0.00 is 12:00 AM, 0.25 is 6:00 AM, 0.75 is 6:00 PM, 0.324 is 7:46:34 AM, and 0.689 is 4:32:10 PM. The date and time serial numbers can be stored in a single cell (i.e. the @NOW function), or individual cells (i.e the @TODAY function). The date serial number and time serial number can be combined in the same cell by adding them together. For example, 33065.893345 represents the combined date/time serial number for July 11, 1990 at 9:26:25 PM. Formatting a Cell The display of a cell can be different from what is actually stored in the cell. The display is called the format of a cell. For example, the contents of a cell may be the date serial number 33065, but onscreen, the cell may display "11-Jul-90". A cell can be formatted for a date or time by selecting /Style|Numeric Format|Date (or Date|Time), or by executing the CTRL-D shortcut key combination, which automatically formats your entry as a valid date. PRODUCT : Quattro Pro NUMBER : 582 VERSION : All OS : DOS DATE : March 28, 1991 PAGE : 2/8 TITLE : Entering and Manipulating Date and Time Values VALID DATE FORMAT EXAMPLE ----------------------------------------------------------------- (DD-MMM-YY) 11-JUL-90 (DD-MMM) 11-JUL (MMM-YY) JUL-90 (Long Int'l) {Default is (MM/DD/YY)} 07/11/90 (Short Int'l) {Default is (MM/DD)} 07/11 The default cell display format is determined by the keystrokes used to enter the data, such as the CTRL-D, or by the /Options| International|Date (or Time) settings. /Options|International sets the global display options for all spreadsheets, and sets the sequence of arguments for entering dates. For example, DD/MM/YY format can be set as the default, so that July 11, 1990 is entered (and displayed) as 11/07/90 instead of 7/11/90. Changing this option does not effect any cells specifically formatted with /Style|Numeric Format. When using a combined date/time serial number, a cell can be formatted for either a date or a time, but not both. Entering Date and Time Values There are four methods for entering date and time values: 1. @Functions 2. CTRL-D 3. /Database|Data Entry 4. Manually entering actual date/time serial number value. Using @functions There are several @functions you can use to enter dates and times. @DATE accepts three parameters and converts them into a date serial number. Likewise, @TIME converts three parameters into a time serial number. @DATEVALUE and @TIMEVALUE convert text strings that are in a valid date format and time format into date serial numbers and time serial numbers, respectively. @TODAY looks at the computer's internal clock and inserts the current date serial number. @NOW goes one step further and PRODUCT : Quattro Pro NUMBER : 582 VERSION : All OS : DOS DATE : March 28, 1991 PAGE : 3/8 TITLE : Entering and Manipulating Date and Time Values inserts the current date/time serial number. Since @TODAY and @NOW take information from the system clock, they are recalculated each time a spreadsheet is loaded, or the F9 (CALC) key is pressed. The examples in the following table are for the time of 9:26:25 PM on July 11, 1990: FUNCTION CONVERT FROM CONVERT TO EXAMPLE ---------------------------------------------------------------------------- @NOW Computer Clock Date/Time Serial Number @NOW = 33065.89335 @TODAY Computer Clock Date Serial Number @TODAY = 33065 @DATE YR,MO,DAY Date Serial Number @DATE(90,7,11) = 33065 @TIME HR,MIN,SEC Time Serial Number @TIME(21,26,25) = 0.89335 @DATEVALUE Date String Date Serial Number @DATEVALUE("11-Jul-90") = 33065 @TIMEVALUE Time String Time Serial Number @TIMEVALUE("9:26:25PM") = 0.89335 Using the CTRL-D shortcut key The CTRL-D shortcut key lets you enter a date in a valid date format. Quattro Pro automatically places the appropriate date serial number in the cell and formats it to display as a date. Similarly, CTRL-D preceding a time format of HH:MM:SS where hours are in military time or HH:MM:SS(AM or PM) with hours in standard time places the time serial number in the cell and formats the cell to display as a time. For example, after pressing CTRL-D and entering 7/11/90. The screen shows 7/11/90 and the date serial number 33065 shows on the edit line as the actual contents of the cell. Using the /Database|Data Entry command The best way to enter a large number of dates and/or times is to use /Database|Data Entry|Dates Only. This lets you select an area where dates (or times) are the only data that can be entered. If a cell has been formatted for Dates Only, you don't have to precede your entries with CTRL-D. The edit line displays DATE, only valid date entries are allowed, and the date/time serial number does not show on the edit line or in the cell. This also makes it easier to edit the date/time. For example, if PRODUCT : Quattro Pro NUMBER : 582 VERSION : All OS : DOS DATE : March 28, 1991 PAGE : 4/8 TITLE : Entering and Manipulating Date and Time Values a cell was formatted with /Database|Data Entry|Dates Only, entering 7/11/90 shows just that in the cell. The edit line also shows 7/11/90 preceded by the word DATE. If 7-11-90 was entered, the error message "Invalid date or time" will display because that is not a valid date format. Manually entering date/time serial number values A date/time value can be entered just like any other number. The cell can then be formatted to show the desired date and/or time format. It is the date and time display formats that make these values different from any other number. For example, if the number 33065 is entered, choosing /Style|Numeric Format|Date|Long Int'l makes the cell display 07/11/90. Manipulating date and/or time serial numbers There are several @functions that are useful in evaluating a date and/or time serial number and retrieving information from it. For a date serial number, the @YEAR, @MONTH, and @DAY functions return the specific year, month, and day of that number. Likewise, the @HOUR, @MINUTE, and @SECOND functions return the hour, minute, and second information for a time serial number. FUNCTION CONVERT FROM CONVERT TO EXAMPLE ---------------------------------------------------------------------------- @DAY Date Serial Number Day of Month @DAY(33065) = 11 @MONTH Date Serial Number Month of Year @MONTH(33065) = 7 @YEAR Date Serial Number Year of Century @YEAR(33065) = 90 @SECOND Time Serial Number Second of Minute @SECOND(0.89335) = 25 @MINUTE Time Serial Number Minute of Hour @MINUTE(0.89335) = 26 @HOUR Time Serial Number Hour of Day @HOUR(0.89335) = 21 Calculating elapsed time To determine the elapsed time between starting and ending date or time values, subtract the start value from the end value. The trick is converting this number into a useful form. If the result is formatted as a date, it displays as a date late in the year 1899 because time is a fraction of a day. Quattro Pro assumes the calculation is referring to day 1, which is December PRODUCT : Quattro Pro NUMBER : 582 VERSION : All OS : DOS DATE : March 28, 1991 PAGE : 5/8 TITLE : Entering and Manipulating Date and Time Values 30, 1899. In most cases, the date and time serial number should be combined in the same cell when dealing with elapsed time. This will cover the case where the end value has passed midnight into the next day. If the date and time are not combined and time values are entered, the difference of two cells displays as a negative number if the ending time has passed midnight, or an incorrect number if the difference has passed a 24-hour period. For example, if a beginning time of 9:26:25 PM (which has the time serial number of 0.893345) and an ending time of 7:41:36 AM (which has the time serial number of 0.320556) are entered, subtracting the beginning time from the ending time will result in the number -0.572789 (which displays as 10:15:11 AM when formatted for time). Obviously, this is not the correct answer. The problem is that the elapsed time has passed midnight. Simply subtracting the two time values gives an incorrect (in this case negative) elapsed time. The solution? Combine the date serial number with the time serial number, creating a combined date/time serial number for both the beginning and ending times, before subtracting one from the other. To correct the example above, assume the beginning time is 9:26:25 PM July 11, 1990 (date serial number 33065), and the ending time is 7:41:36 AM July 12, 1990 (date serial number 33066). Combining the date and time serial number results in the beginning number 33065.893345 and the ending number 33066.320556. Subtracting the beginning number from the ending number gives the value 0.427211, which you can convert to hours, minutes, and seconds. Three additional @functions that useful in calculating elapsed time: @INT, @MOD, and @ROUND. @INT evaluates a real number (combined integer and decimal) and returns only the integer portion of that number. Similar to the @INT function, @MOD is used to return the decimal portion of a real number. Since @MOD divides the first argument ("X") by the second argument ("Y"), the second argument ("Y") should be set to 1 when doing elapsed time calculations. If "Y" is not set to 1, the correct remainder will not returned. @ROUND is used to change the accuracy of a number to the closest whole number. For instance, in the following example, there is PRODUCT : Quattro Pro NUMBER : 582 VERSION : All OS : DOS DATE : March 28, 1991 PAGE : 6/8 TITLE : Entering and Manipulating Date and Time Values no need to deal with partial seconds, so we round the real number 10.94400014 to the next whole second, 11. PRODUCT : Quattro Pro NUMBER : 582 VERSION : All OS : DOS DATE : March 28, 1991 PAGE : 7/8 TITLE : Entering and Manipulating Date and Time Values FUNCTION DESCRIPTION OF FUNCTION EXAMPLE ----------------------------------------------------------------- @INT(X) Drops Fraction of X, Returning Integer Value @INT(5.35) = 5 @MOD(X,Y) Divides X Value by Y and Returns Remainder Value @MOD(5.35,1) = 0.35 @ROUND(X,NUM) Adjusts Precision of X to NUM Decimal Points @ROUND(5.35,1) = 5.4 The following example shows how to calculate the number of days and hours between a beginning and ending date and time. The concept here is to: 1. Input two date/time values. 2. Calculate the difference between the values giving elapsed time. 3. Separate the integer and decimal portions of the elapsed time. 4. Convert the decimal portion into a different measure (i.e. hours to minutes). 5. Repeat steps 3 & 4 until the desired precision is reached. Cells A2 and B2 are used as input date cells in this example. Enter the following information into the cells indicated (The semicolons and the descriptions following them can be omitted): A1: 'Begin B1: 'End C1: 'Elapsed D1: 'Days E1: 'Part Days F1: 'Part Day(Hr) ;Partial days measured in hours A2: 33065.89335 B2: 33070.32056 C2: +B2-A2 ;The difference between the two times D2: @INT(C2) ;Integer portion of elapsed time returns days E2: @MOD(C2,1) ;Remainder portion returns partial days PRODUCT : Quattro Pro NUMBER : 582 VERSION : All OS : DOS DATE : March 28, 1991 PAGE : 8/8 TITLE : Entering and Manipulating Date and Time Values F2: +E2*24 ;Converts the remainder from days to hours 1. Set columns A through F to a column width of 12 using /Style|Block Widths|Set Width|A1..F1|12. 2. Use /Style|Alignment|Center|A1..F2 to center the labels and numbers. 3. Format cells A2 and B2 to a time format using /Style|Numeric Format|Date|Time. Note that cells A2 and B2 each contain a combined date/time serial number formatted for time, and thus do not indicate the date onscreen. Remember, a cell cannot be formatted for both a date and a time. Here is how the example looks for the two values entered: Begin ³ End ³ Elapsed ³ Days ³ Part Days ³Part Day(Hr) ÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÄÄ 09:26:25 PM³07:41:36 AM³ 4.42721 ³ 4 ³ 0.42721 ³ 10.25304 The same concept can be used to calculate the hours, minutes and seconds elapsed. The following is an addition to the above example, adding hours, minutes, and seconds. G1: 'Hours H1: 'Part Hrs I1: 'Part Hrs(Min) ;Partial hours measured in minutes J1: 'Minutes K1: 'Part Mins L1: 'Part Min(Sec) ;Partial minutes measured in seconds M1: 'Seconds G2: @INT(F2) ;Integer portion returns whole hours H2: @MOD(F2,1) ;Remainder portion returns partial hours I2: +H2*60 ;Converts decimal from hours to minutes J2: @INT(I2) ;Integer portion returns whole minutes K2: @MOD(I2,1) ;Remainder portion returns partial minutes L2: +K2*60 ;Converts decimal from minutes to seconds M2: @ROUND(L2,0) ;Rounds decimal to whole seconds 1. Set columns G through M to a column width of 12 using /Style|Block Widths|Set Width|G1..M1|12. PRODUCT : Quattro Pro NUMBER : 582 VERSION : All OS : DOS DATE : March 28, 1991 PAGE : 9/8 TITLE : Entering and Manipulating Date and Time Values 2. Use /Style|Alignment|Center|G1..M2 to center the labels and numbers. 3. Use /Style|Hide Columns to hide columns C, E, F, H, I, K, and L. The hidden column option suppresses the screen display and printing of intermediate steps, which may clutter the ending results. The spreadsheet will look like the following: Begin ³ End ³ Days ³ Hours ³ Minutes ³ Seconds ÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄ 09:26:25 PM³07:41:36 AM³ 4 ³ 10 ³ 15 ³ 11