# Thread: Elapsed Time (2000 SP-3)

1. ## Elapsed Time (2000 SP-3)

This is in regard to Excel 2000 9.0.6926 SP-3 under Win 98 SE-2.

My question relates to genealogical data. I cannot always calculate the elapsed time between two dates.

Perhaps this is a result of how Excel interprets dates and an arbitrary starting with 1900?

I find I can subtract 20th C dates from 20th or 21st C dates and Excel yields the elapsed time in date format as mos/day/years.

The same subtraction fails to work if I use 19th C dates.

I've read that Excel uses a string of numbers to represent the date. Is this true only for the current and previous century?

If I want to calculate the difference between March 5th, 1872 (3/5/1872) and April 18th, 1962 (5/18/62) should I increment the years in both cases by 100. It looks as though this would be a manual entry situation if the pre-1900 dates are coded differently than the post-1900 dates.

If I need to go to manual entry, perhaps the easiest way out is to copy selected columns from Excel to Word, search and replace / with ^t, and then copy and paste back into separate columns in Excel where the differences could be easily computed.

Is there an easier way?

Thanks!

2. ## Re: Elapsed Time (2000 SP-3)

Depending on a setting in the Calculate tab of Tools | Options, the Excel date system starts either at 1/1/1900 or 1/1/1904.
Dates before that are not recognized and will be interpreted as text.

You could create the following user-defined function:

Function Dd(Date1, Date2, Fmt As String)
If Not IsDate(Date1) Or Not IsDate(Date2) Then
Dd = CVErr(1)
Else
Dd = DateDiff(Fmt, CDate(Date1), CDate(Date2))
End If
End Function

This will work with dates from 1/1/100 to 12/31/9999, whether they are seen by Excel as text or as dates. Fmt is a quoted string representing the kind of elapsed time you want: "d" for days, "m" for months, and "yyyy" for years.

Example:

=Dd(A1,B1,"yyyy")

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•