elapsed time calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chris - 10 May 2007 22:46 GMT

To give me the elapsed time between 2 sets of dates and associated times, I
used a calculation and the following function to calculate it. Unfortunately,
I would like to have it formatted in DD:HH:MM instead of "x Days y Hours z
Minutes". Can anyone help with this? Thanks!

------------
Option Compare Database
Option Explicit

Public Function MyGetElapsedTime(interval) As String

Dim Day As String
Dim Time As String

Day = Int(interval) & " day "
Time = Format(interval, "h \h\r n \m\i\n")

MyGetElapsedTime = Day & Time

End Function

Signature

Chris Bork

Reply to this Message

John W. Vinson - 11 May 2007 04:41 GMT

>To give me the elapsed time between 2 sets of dates and associated times, I
>used a calculation and the following function to calculate it. Unfortunately,
[quoted text clipped - 16 lines]
>
>End Function

MyElapsedTime = Format(interval, "dd:hh:nn")

John W. Vinson [MVP]

Reply to this Message

chris - 11 May 2007 13:08 GMT

John,

Thank you for answering. Although it fixes the format, I receive errors in
the elapsed time calculation. the formula I'm using is:

Time_To_OR:
MyGetElapsedTime(([2c_fx_treatment.d_surg]+[2c_fx_treatment.or_admit])-([2_visits.dadmission]+[2_visits.tadmission]))

with the function you saw. For instance, when my start date and time were:
2/24/07 10:42
and my end date and time was:
2/25/07 18:44
I received this answer:
1 day 31:08:02

We are on 24 hour time formats here, and I don't know if that makes a
difference, but in addition, it is giving me 31 days, 8 hours and 2 minutes.

I appreciate any help....

Chris Bork

> >To give me the elapsed time between 2 sets of dates and associated times, I
> >used a calculation and the following function to calculate it. Unfortunately,
[quoted text clipped - 20 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 11 May 2007 21:53 GMT

>John,
>
[quoted text clipped - 10 lines]
>I received this answer:
>1 day 31:08:02

Sorry... I was obscure. Try

Public Function MyGetElapsedTime(interval) As String
MyElapsedTime = Format(interval, "dd:hh:nn")
End Function

Or you could adapt the code at

http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

You will just need to change the formatting - rather than concatenating the
literal string " days" you can concatenate the literal string ":".

John W. Vinson [MVP]

Reply to this Message

chris - 25 May 2007 16:31 GMT

Thank you, John. I really do appreciate all your help. It works, but still
gives me answers like "31:08:02" when calculating the time elapsed between
2/24/2007 at 10:42 and 2/25/2007 at 18:44.

Not sure why it calculates at 31 days instead of 1.

Signature

Chris Bork

> >John,
> >
[quoted text clipped - 25 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 25 May 2007 18:27 GMT

>Thank you, John. I really do appreciate all your help. It works, but still
>gives me answers like "31:08:02" when calculating the time elapsed between
>2/24/2007 at 10:42 and 2/25/2007 at 18:44.
>
>Not sure why it calculates at 31 days instead of 1.

Try

Public Function MyGetElapsedTime(interval) As String

Dim strDay As String
Dim strTime As String

strDay = Int(interval) & ":"
strTime = Format(interval, "hh:nn")

MyGetElapsedTime = strDay & strTime

End Function

John W. Vinson [MVP]

Reply to this Message

chris - 25 May 2007 20:40 GMT

thank you, John, that seems to have worked! I appreciate it.

Signature

Chris Bork

> >Thank you, John. I really do appreciate all your help. It works, but still
> >gives me answers like "31:08:02" when calculating the time elapsed between
[quoted text clipped - 17 lines]
>
> John W. Vinson [MVP]

Reply to this Message