B
bhelms
Guest
Trying to calculate an elapsed time formatted as
years, months, and days.
Applications being used are MS Access 97 & MS Access 2000
The code shown below originally was for calculating elapsed time between
two dates formatted as Days, Hours, Minutes, Seconds. I tried to adapt it
to perform the calculations and was unsuccessful.
Problems:
The interval is days, should it be something else?
Using integer divide "\" 365 at years is not accurate but a quick
fix, only solution I could come up with.
When you go from one month to the next it counts it as a month even
though it's only been one day i.e. 2/28/04 to 3/1/04 shows 0 years, 1 month,
1 day
A similar problem is encountered going from year to year.
The {If IsNull(dateStart) = True Then ElapsedTimeString = "No
startdate"} does not work.
This function will be used to calculate dates anywhere from 1 day to
100 years.
Option Explicit
Public Function ElapsedTimeString(dateStart As Date, dateEnd As Date) As
String
'Function ElapsedTimeString (dateStart As Date, dateEnd As Date) As
string
'Retrurns the time elapsed between a starting Date and an ending Date
formatted as
'a string that looks like this:
'"10 years, 10 months, 5 days".
Dim interval As Single, str As String, years As Variant
Dim months As String, days As String
If IsNull(dateStart) = True Then
ElapsedTimeString = "No startdate"
Else
interval = dateEnd - dateStart
years = interval \ 365
months = Format(interval, "m")
days = Format(interval, "d")
'Years part of the string
str = IIf(years = 0, "0 Years, ", IIf(years = 1, years & " Year, ",
years & " Years, "))
'Months part of the string
str = str & IIf(months = 0, "0", IIf(months = 1, months & " Month,
", months & " Months, "))
'Days part of the string
str = str & IIf(days = 0, "0", IIf(days = 1, days & " Day", days & "
Days"))
ElapsedTimeString = IIf(str = " ", "0", str)
End If
End Function
Can this be fixed? Please help.
years, months, and days.
Applications being used are MS Access 97 & MS Access 2000
The code shown below originally was for calculating elapsed time between
two dates formatted as Days, Hours, Minutes, Seconds. I tried to adapt it
to perform the calculations and was unsuccessful.
Problems:
The interval is days, should it be something else?
Using integer divide "\" 365 at years is not accurate but a quick
fix, only solution I could come up with.
When you go from one month to the next it counts it as a month even
though it's only been one day i.e. 2/28/04 to 3/1/04 shows 0 years, 1 month,
1 day
A similar problem is encountered going from year to year.
The {If IsNull(dateStart) = True Then ElapsedTimeString = "No
startdate"} does not work.
This function will be used to calculate dates anywhere from 1 day to
100 years.
Option Explicit
Public Function ElapsedTimeString(dateStart As Date, dateEnd As Date) As
String
'Function ElapsedTimeString (dateStart As Date, dateEnd As Date) As
string
'Retrurns the time elapsed between a starting Date and an ending Date
formatted as
'a string that looks like this:
'"10 years, 10 months, 5 days".
Dim interval As Single, str As String, years As Variant
Dim months As String, days As String
If IsNull(dateStart) = True Then
ElapsedTimeString = "No startdate"
Else
interval = dateEnd - dateStart
years = interval \ 365
months = Format(interval, "m")
days = Format(interval, "d")
'Years part of the string
str = IIf(years = 0, "0 Years, ", IIf(years = 1, years & " Year, ",
years & " Years, "))
'Months part of the string
str = str & IIf(months = 0, "0", IIf(months = 1, months & " Month,
", months & " Months, "))
'Days part of the string
str = str & IIf(days = 0, "0", IIf(days = 1, days & " Day", days & "
Days"))
ElapsedTimeString = IIf(str = " ", "0", str)
End If
End Function
Can this be fixed? Please help.