Elapsed period between dates
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. -- Regards, Sébastien <http://www.ondemandanalysis.com "Robin Clay" wrote: 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 |
Elapsed period between dates
Thank you for your help.
-- Regards Robin "sebastienm" wrote: 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. -- Regards, Sébastien <http://www.ondemandanalysis.com "Robin Clay" wrote: 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 |
All times are GMT +1. The time now is 03:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com