Elapsed period between dates

[Robin Clay]

Greetings !

Excel will give the number of days between two given dates, provided both
are post 1/1/1900.

For Family History research, I would like to obtain the time between dates
for periods going back to the 1600s !

e.g., I would like to interogate my database to find the ages at marriage of
my ancestors.

O.K, I guess to the nearest month would be accurate enough !


Regards

Robin

 

[JLGWhiz]

You can take a look at these sites. Maybe they will help.

http://j-walk.com/ss/excel/files/xdate.htm

http://support.microsoft.com/kb/214058

 

[sebastienm]

Hi,
Excel doesn't handle dates prior to 1900. When you enter such a date in a
sheet, eg. 1/1/1600, it is entered as text (left aligned while regular dates
are right aligned).
On the other hand , vba and MS Access handles dates prior to 1900.

-You could either keep your data in an MS Access table and query from excel.
- or you could wrap the regular vba functions into vba to make it a UDF
useable in a sheet. Eg regular vba DateDiff --> DateDiff2 for the sheet:
Public Function DateDiff2( _
Interval As Variant, Date1 As Variant, Date2 As Variant) As Variant
Dim d1 As Date, d2 As Date

On Error Resume Next
d1 = CDate(Date1)
d2 = CDate(Date2)
If Err <> 0 Then ''' error converting dates
DateDiff2 = CVErr(xlErrValue)
Else
DateDiff2 = DateDiff(Interval, d1, d2)
If Err <> 0 Then ''' error for Interval
DateDiff2 = CVErr(xlErrValue)
End If
End If
End Function

DateDiff2 works llike vba DataDiff, but it can also be used in the sheet, eg:
=DateDiff("m",A1,A2)
--> returns the number of months between dates in A1 and A2. Even if these
dates are in the 1600s and therefore understaood as Text by excel, when
passed to the function , they are converted to regular dates and the
computation can then work.

 

[Robin Clay]

Thank you for your help.
--
Regards

Robin

You can take a look at these sites. Maybe they will help.

http://j-walk.com/ss/excel/files/xdate.htm

http://support.microsoft.com/kb/214058

 

[Robin Clay]

Thank you for your help.
--
Regards

Robin

Hi,
Excel doesn't handle dates prior to 1900. When you enter such a date in a
sheet, eg. 1/1/1600, it is entered as text (left aligned while regular dates
are right aligned).
On the other hand , vba and MS Access handles dates prior to 1900.

-You could either keep your data in an MS Access table and query from excel.
- or you could wrap the regular vba functions into vba to make it a UDF
useable in a sheet. Eg regular vba DateDiff --> DateDiff2 for the sheet:
Public Function DateDiff2( _
Interval As Variant, Date1 As Variant, Date2 As Variant) As Variant
Dim d1 As Date, d2 As Date

On Error Resume Next
d1 = CDate(Date1)
d2 = CDate(Date2)
If Err <> 0 Then ''' error converting dates
DateDiff2 = CVErr(xlErrValue)
Else
DateDiff2 = DateDiff(Interval, d1, d2)
If Err <> 0 Then ''' error for Interval
DateDiff2 = CVErr(xlErrValue)
End If
End If
End Function

DateDiff2 works llike vba DataDiff, but it can also be used in the sheet, eg:
=DateDiff("m",A1,A2)
--> returns the number of months between dates in A1 and A2. Even if these
dates are in the 1600s and therefore understaood as Text by excel, when
passed to the function , they are converted to regular dates and the
computation can then work.