Results 1 to 2 of 2
  1. #1
    3 Star Lounger baumgrenze's Avatar
    Join Date
    Feb 2001
    Location
    California, USA
    Posts
    262
    Thanks
    6
    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?

    Thanks!
    Baumgrenze
    Hier sind wir tief eingewurzelt.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    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)
    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
  •