Results 1 to 2 of 2
  1. #1
    3 Star Lounger baumgrenze's Avatar
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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))
    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.



Posting Permissions

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