Results 1 to 2 of 2
Thread: Elapsed Time (2000 SP-3)
2007-03-29, 22:11 #1
- Join Date
- Feb 2001
- California, USA
- Thanked 0 Times in 0 Posts
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?
Hier sind wir tief eingewurzelt.
2007-03-29, 22:25 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
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)
Dd = DateDiff(Fmt, CDate(Date1), CDate(Date2))
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.