Showing times > 24 hrs

[Paul Sutton]

In excel there is a format type that will let you show
times > 24 hrs - for example 36:23 for 36 hrs, 23
minutes. How can this be emulated in Access.

I am taking the difference between 2 Date/Times, and want
to display the difference in hrs.

Thx

 

[Henro]

And I would very much like to hear that tooooooo!

:-D

 

[Rick Brandt]

There is no such Time type in Access. You would need to write a custom function
that uses DateDiff() to get the difference in minutes and then do the math to
determine the hours/minutes and then return the result of that as a String.

SIMPLE EXAMPLE:

Function HoursMinutes(StartTime As Date, EndTime As Date) As String

Dim lngMinutes As Long

lngMinutes = DateDiff("n", StartTime, EndTime)
HoursMinutes = CStr(lngMinutes \ 60) & ":" & Format(lngMinutes Mod 60, "00")

End Function

 

[Henro]

Any suggestions as to how to use this?

There is no such Time type in Access. You would need to write a custom function
that uses DateDiff() to get the difference in minutes and then do the math to
determine the hours/minutes and then return the result of that as a String.

SIMPLE EXAMPLE:

Function HoursMinutes(StartTime As Date, EndTime As Date) As String

Dim lngMinutes As Long

lngMinutes = DateDiff("n", StartTime, EndTime)
HoursMinutes = CStr(lngMinutes \ 60) & ":" & Format(lngMinutes Mod 60, "00")

End Function

 

[John Vinson]

In excel there is a format type that will let you show
times > 24 hrs - for example 36:23 for 36 hrs, 23
minutes. How can this be emulated in Access.

It cannot, not with a Date/Time field.

I am taking the difference between 2 Date/Times, and want
to display the difference in hrs.

Calculate the difference in *minutes* (as a Long Integer) and use an
expression to display that duration as hours and minutes:

DateDiff("n", [start], [end]) \ 60 & Format(DateDiff("n", [start],
[end]) MOD 60, ":00")

 

[Rick Brandt]

The same way you use any other function. The OP indicated the need to display
the difference between two DateTimes in HH:NN format. Supplying two DateTimes
as arguments to the function below will do exactly that. In a query (for
example) you would add a calculated field...

TimeDifference: HoursMinutes(SomeDateField, SomeOtherDateField)

 

[Henro]

This one helped me out of this problem

http://support.microsoft.com/default.aspx?scid=kb;EN-US;210604

Grtz & Sccss Henro

 

[Douglas J. Steele]

It cannot, not with a Date/Time field.

Not quite true, John. Since Access stores times as fractions of a day, when
you've exceeded 24 hours, Access adds a day to what it's storing. You can
convert it back if you really want to.

Function FormatTime(TimeToFormat As Date) As String

Dim intHours As Integer
Dim intMinutes As Integer

intHours = 24 * Int(TimeToFormat) + Hour(TimeToFormat)
intMinutes = Minute(TimeToFormat)

FormatTime = Format(intHours, "0") & ":" & _
Format(intMinutes, "00")

End Function

Of course, the alternative you proposed is a far better solution.

 

[Tom Wickerath]

You might also want to try "A More Complete DateDiff Function"
http://members.rogers.com/douglas.j.steele/Diff2Dates.html

Tom
___________________________________________


The same way you use any other function. The OP indicated the need to display
the difference between two DateTimes in HH:NN format. Supplying two DateTimes
as arguments to the function below will do exactly that. In a query (for
example) you would add a calculated field...

TimeDifference: HoursMinutes(SomeDateField, SomeOtherDateField)

 

[lou lei]

In excel there is a format type that will let you show
times > 24 hrs - for example 36:23 for 36 hrs, 23
minutes. How can this be emulated in Access.

I am taking the difference between 2 Date/Times, and want
to display the difference in hrs.

Thx