Elapsed Time Formated as Y/M/D Problem (1 Viewer)

bhelms · Jun 2, 2004

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.

Jon K · Jun 3, 2004

I have attached a sample database, which contains a function getTimeElapsed() that I use.

The function makes both start and end dates inclusive and will return 1 day if the start and end dates are the same. (If you want it to return 0 day instead, you can remove the "+ 1" from the code.)

To use the function, you can pass to it a Start Date and an End Date:
getTimeElapsed([StartDate], [EndDate])

If you pass only the [StartDate] to it: getTimeElapsed([StartDate])
the function will calculate time elapsed up to the current day.

Note
If the start date falls on the 31st day of a month, but the month to be compared in the end date has only 30 days, the code will treat the start date as the 30th of the month. Compare records 9 and 12 in the query results:-

Code:

ID	StartDate	EndDate		Elapsed Time
 8	31/05/1978	29/04/2004	25 years 11 months 0 day
 [color=red]9	31/05/1978	30/04/2004	25 years 11 months 1 day[/color]
10	31/05/1978	01/05/2004	25 years 11 months 2 days
11	31/05/1978	29/05/2004	25 years 11 months 30 days
[color=red]12	31/05/1978	30/05/2004	26 years 0 month 0 day[/color]
13	31/05/1978	31/05/2004	26 years 0 month 1 day
14	15/05/1978	15/05/2004	26 years 0 month 1 day

Pat Hartman · Jun 3, 2004

I posted a database with a bunch of useful date functions. Search for it in the samples db forum.

Rose412 · Jun 3, 2004

I downloaded Pat's sample and tried to use the Diff2Dates() function with this expression:

Time Elapsed: Diff2Dates("ymd", [StartDate], [EndDate], True)

But I got an error when I tried to run the query:

Wrong number of arguments used with function in query expression
'Diff2Dates("ymd", [StartDate], [EndDate], True)'.

What was I doing wrong?

Jon K · Jun 3, 2004

Pat's Diff2Dates() function was intended to be used with the form in her database. To use the function in your query, you can change the function header from:-

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date, _
iYears, iMonths, iDays, iHours, iMinutes, iSeconds, Optional ShowZero As Boolean = False) As Variant

to:-
Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date, _
Optional ShowZero As Boolean = False) As Variant

and comment out the iYears, iMonths, iDays, iHours, iMinutes and iSeconds in the code. (See the function in the attached database.)

Pat's function returns the same results as my function does when "+ 1" is removed from my code (see explanation in my previous post), except when the start date falls on the 31st day of a month while the month to be compared in the end date has only 30 days. As seen in records 9 and 10 below, it goes from "25 years 10 months 30 days" (instead of "25 years 11 months 0 day") to "25 years 11 months 1 day".
_

Code:

[b]ID	StartDate	EndDate		Using my function 		Using Pat's function
					(with +1 removed)[/b]
 1			28/02/2004	No startdate			#Error
 2	28/02/2004	28/02/2004	0 year 0 month 0 day		0 years 0 months 0 days
 3	28/02/2004	29/02/2004	0 year 0 month 1 day		0 years 0 months 1 day
 4	28/02/2004	01/03/2004	0 year 0 month 2 days		0 years 0 months 2 days
 5	30/04/1904	29/05/2004	100 years 0 month 29 days	100 years 0 months 29 days
 6	30/04/1904	30/05/2004	100 years 1 month 0 day		100 years 1 month 0 days
 7	30/04/1904	31/05/2004	100 years 1 month 1 day		100 years 1 month 1 day
 8	31/05/1978	29/04/2004	25 years 10 months 29 days	25 years 10 months 29 days
[color=red] 9	31/05/1978	30/04/2004	25 years 11 months 0 day	25 years 10 months 30 days
10	31/05/1978	01/05/2004	25 years 11 months 1 day	25 years 11 months 1 day[/color]
11	31/05/1978	29/05/2004	25 years 11 months 29 days	25 years 11 months 29 days
12	31/05/1978	30/05/2004	25 years 11 months 30 days	25 years 11 months 30 days
13	31/05/1978	31/05/2004	26 years 0 month 0 day		26 years 0 months 0 days
14	15/05/1978	15/05/2004	26 years 0 month 0 day		26 years 0 months 0 days

bhelms · Jun 3, 2004

Thanks. I'll try these and see if they will work on this form.

Pat Hartman · Jun 3, 2004

Sorry about that. I forgot to make those arguments optional. The example I built uses the optional arguments and I forgot to check the Function without them. Change the function header to:

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date, _
Optional iYears, Optional iMonths, Optional iDays, Optional iHours, _
Optional iMinutes, Optional iSeconds, Optional ShowZero As Boolean = False) As Variant

Those additional arguments are a round-about way of getting the function to return more than 1 value. The function returns a string such as "2 years, 1 day". If you want to work with the values separately, you'll need to call the function from VBA where you can provide variables to hold the separate components of the elapsed time. In a query, you can only get the string that the function returns.

bhelms · Jun 3, 2004

Thanks

Jon,
Thank you for the information. It was priceless. I have been working on this off and on about a month now. I really appreciate it. Everything works and now I can get the reports completed and move on to something else. Again I thank you.

Rose412 · Jun 4, 2004

Thank you Jon K for the sample database.

Since November has 30 days, obviously "25 years 10 months 30 days" is actually "25 years 11 months". Your function seems to be able to handle it correctly.

Jon K · Jun 5, 2004

Pat Hartman said:
...... I forgot to make those arguments optional. ......... Change the function header to:

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date, _
Optional iYears, Optional iMonths, Optional iDays, Optional iHours, _
Optional iMinutes, Optional iSeconds, Optional ShowZero As Boolean = False) As Variant

Pat,

Just a suggestion. I think it would be better to put ShowZero as the first optional argument, so that users would not need to use an expression like this to set ShowZero to True:-

Diff2dates("ymd",[StartDate],[EndDate],0,0,0,0,0,0,True)

Jon

Pat Hartman · Jun 5, 2004

You don't need the zeros but you do need the comas so I agree. I will make one more change.

Jon K · Jun 5, 2004

Pat Hartman said:
You don't need the zeros but you do need the comas so I agree. .........

No, the syntax ",,,,,,,something" works in VBA, not in queries.

Putting ElapsedTime: Diff2Dates("ymd",[StartDate],[EndDate],,,,,,,True) in the query grid will result in an error:-
The expression you entered contains invalid syntax.

You may have entered a comma without a preceding value or identifier.

raskew · Dec 13, 2005

Hi -

The following, written/tested in A97, will return the difference--as a string--between two dates, in any variation of year ("y"), month("m"), day("d") as specified by the user.

If an end date isn't specified, the function will default to today's date.

Date input may be either in date(#12/5/05#) or string ("12/5/05") format.

Try copying/pasting to a new module, then following the comments to tests.
Note that the example outputs won't be accurate today, as they were written using 2003 dates. Hope this might be helpful.

Bob

Code:

Function Agecount6(ByVal pDOB As Variant, _
                   Optional ByVal pEdte As Variant, _
                   Optional ByVal pWhat As Variant) As String

'*******************************************
'Purpose:   Display age or difference between
'           two dates with options to display
'           any variation of years, months,
'           days.
'coded by:  raskew
'Inputs:    1) ? Agecount6(#3-Mar-80#) 'defaults
'                to current date & "ymd" display
'           2) ? Agecount6(#3-Mar-80#, "4/25/04")
'                Uses PEdte in place of date(),
'                and default "ymd" display
'           3) ? Agecount6(#3-Mar-80#, "4/25/04", "d")
'                Same as 2), but with display as days
'Output:    1)  24 years, 1 month, 15 days
'           2)  24 years, 1 month, 22 days
'           3)  8819 days
'*******************************************
                   
Dim dte2        As Date
Dim dteMyDate   As Date
Dim intHold     As Integer
Dim i           As Integer
Dim n           As Integer
Dim strhold     As String
Dim strHold2    As String
Dim strInterval As String
Dim strTemp     As String
Dim strWhat     As String

    strInterval = "ymd"
    strWhat = IIf(IsMissing(pWhat), "ymd", pWhat)
    
    'Check/correct string sequence
    strhold = ""
    For i = 1 To 3 '3 = possible max length of strWhat
       If InStr(strWhat, Mid(strInterval, i, 1)) > 0 Then
          strhold = strhold & Mid(strInterval, i, 1)
       End If
    Next i
    strWhat = strhold
    
    dteMyDate = DateValue(pDOB)
    dte2 = IIf(IsMissing(pEdte), Date, DateValue(pEdte))
    For n = 1 To Len(strWhat)
       strhold = Mid(strWhat, n, 1)
       Select Case strhold
          Case "y"
             intHold = DateDiff("yyyy", dteMyDate, dte2) + _
                      (dte2 < DateSerial(Year(dte2), Month(dteMyDate), Day(dteMyDate)))
             dteMyDate = DateAdd("yyyy", intHold, dteMyDate)
             strHold2 = strHold2 & LTrim(Str(intHold)) & " year" & IIf(intHold <> 1, "s, ", ", ")
          Case "m"
             intHold = DateDiff("m", dteMyDate, dte2) + (Day(dteMyDate) > Day(dte2))
             dteMyDate = DateAdd("m", intHold, dteMyDate)
             strHold2 = strHold2 & LTrim(Str(intHold)) & " " & "month" & IIf(intHold <> 1, "s, ", ", ")
          Case "d"
             intHold = DateDiff("d", dteMyDate, dte2)
             strHold2 = strHold2 & LTrim(Str(intHold)) & " " & "day" & IIf(intHold <> 1, "s", "")
       End Select
    Next n
    
    Agecount6 = strHold2

End Function

Elapsed Time Formated as Y/M/D Problem (1 Viewer)

bhelms

Guest

Jon K

Registered User.

Attachments

Pat Hartman

Super Moderator

Rose412

Registered User.

Jon K

Registered User.

Attachments

bhelms

Guest

Pat Hartman

Super Moderator

bhelms

Guest

Rose412

Registered User.

Jon K

Registered User.

Pat Hartman

Super Moderator

Jon K

Registered User.

raskew

AWF VIP

Similar threads

Users who are viewing this thread