elapsed time calculation

[chris]

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

 

[John W. Vinson]

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

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

John W. Vinson [MVP]

 

[chris]

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_TR:
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

 

[John W. Vinson]

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_TR:
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

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]

 

[chris]

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.
--
Chris Bork

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_TR:
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

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]

 

[John W. Vinson]

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]

 

[chris]

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