Microsoft KB Archive/66001

Works: Returning Numeric Values Using Date Format

PSS ID Number: Q66001 Article last modified on 10-16-1998

1.00 1.05 2.00 3.00

MS-DOS

=
========================================================= 1.00 1.05 2.00 3.00 MS-DOS kbotherTime/Date TimeTime-Date Conversions

= SUMMARY =

You can produce a numeric value from a computation using Date-formatted values within a spreadsheet.

For example, assume two dates are recorded in two separate cells within a spreadsheet and the result of their difference in days is desired. A computation similar to the following will produce the values desired. For example, 3/12/82 will be in cell A1, 4/15/82 will be in cell B1, and the result of the computation will be placed in C1.


 * 1) Format cell C1 for General.
 * 2) Place the formula “=B1-A1” (without the quotation marks) in cell C1. The resulting number will be an integer value of 34. This value is in days.

Do the following to return a value in years:

Divide the resulting number in C1 by 365.

Do the following to return a value in months:

Divide the resulting number in C1 by 30. (This value will not be completely accurate because 30 is the average number of days in a month.)

Do the following to return a value in hours:

Multiply the resulting number in C1 by 24 (hours).

Do the following to return a value in minutes:

Multiply the resulting number in C1 by 1140 (24 hours x 60 minutes).

Do the following to return a value in seconds:

Multiply the resulting number in C1 by 86400 (24 hours x 60 minutes x 60 seconds).

= MORE INFORMATION =

Works uses an integer number between 1 and 36525 to represent the days from 1/1/00 to 12/31/99, or the total number of days in this century (36526). For example, a date of March 12, 1976 corresponds to the integer 27831. This is how Works assigns a usable value to the Date format.

KBCategory: kbother KBSubcategory: dworkskb Additional reference words: ====================================================================== Version : 1.00 1.05 2.00 3.00 Platform : MS-DOS ============================================================================= Copyright Microsoft Corporation 1998.