Long Hour Format??

[Anand]

Iam using an A2k application that calculates Regular Hours
and Over Time Hours on a monthly basis.

I want access to sum the Regular and Over Time hours over
the month. My problem is that Access does not
display "hours" beyond 24 hours. I need Access to display
a sum of over 100 hours (3 digits). How do I do this..Is
there a custom format for hours that I am missing.
Please help

Thanks in Advance
Anand

 

[Rick Brandt]

Iam using an A2k application that calculates Regular Hours
and Over Time Hours on a monthly basis.

I want access to sum the Regular and Over Time hours over
the month. My problem is that Access does not
display "hours" beyond 24 hours. I need Access to display
a sum of over 100 hours (3 digits). How do I do this..Is
there a custom format for hours that I am missing.

Very common issue. DateTimes are good for storing "points in time" not
"amounts of time". The usual advice is to store the Number-Of-Hours or
Number-Of-Minutes as a numerical value, do your summing on that, and then
do your own math to figure out how to *display* it using a string that
looks like Hours and Minutes.

 

[Anand]

Thanks for that tip Rick.

Ive tried Calculating Number-Of-Hours as a numerical value
using a "Single" data type (to allow for parts of an hour)

This doesnt seem to work either. 0.3333 is 8 hours.
0.9999 is 24 hours. What do I do beyond this. 1.3333 is
treated as 8 hours again. The "1" portion gets ignored as
the date part.

Am I making a mistake in using the Single Datatype. Is
there a way around?
Anand

 

[John Vinson]

This doesnt seem to work either. 0.3333 is 8 hours.
0.9999 is 24 hours. What do I do beyond this. 1.3333 is
treated as 8 hours again. The "1" portion gets ignored as
the date part.

Am I making a mistake in using the Single Datatype. Is
there a way around?

A Single will work - but you can't use a Time or Date format to
display the result.

Try instead

Fix([Hours]) & Format(60 * ([Hours] - Fix([Hours]), ":00")

 

[Douglas J. Steele]

You can write your own function to format the times.

Function ReformatTime(TimeSum As Date) As String

Dim lngDays As Long
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long

lngDays = Int(TimeSum)
lngHours = Hour(TimeSum) + 24 * lngDays
lngMinutes = Minute(TimeSum)
lngSeconds = Second(TimeSum)

ReformatTime = Format$(lngHours, "0") & ":" & _
Format$(lngMinutes, "00") & ":" & _
Format$(lngSeconds, "00")

End Function


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

This doesnt seem to work either. 0.3333 is 8 hours.
0.9999 is 24 hours. What do I do beyond this. 1.3333 is
treated as 8 hours again. The "1" portion gets ignored as
the date part.

Am I making a mistake in using the Single Datatype. Is
there a way around?

A Single will work - but you can't use a Time or Date format to
display the result.

Try instead

Fix([Hours]) & Format(60 * ([Hours] - Fix([Hours]), ":00")

 

[Anand]

Thanks Doug. Big Help
Anand

-----Original Message-----
You can write your own function to format the times.

Function ReformatTime(TimeSum As Date) As String

Dim lngDays As Long
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long

lngDays = Int(TimeSum)
lngHours = Hour(TimeSum) + 24 * lngDays
lngMinutes = Minute(TimeSum)
lngSeconds = Second(TimeSum)

ReformatTime = Format$(lngHours, "0") & ":" & _
Format$(lngMinutes, "00") & ":" & _
Format$(lngSeconds, "00")

End Function


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

This doesnt seem to work either. 0.3333 is 8 hours.
0.9999 is 24 hours. What do I do beyond this. 1.3333 is
treated as 8 hours again. The "1" portion gets ignored as
the date part.

Am I making a mistake in using the Single Datatype. Is
there a way around?

A Single will work - but you can't use a Time or Date format to
display the result.

Try instead

Fix([Hours]) & Format(60 * ([Hours] - Fix ([Hours]), ":00")

.

 

[Anand]

Thanks John
Anand

-----Original Message-----

This doesnt seem to work either. 0.3333 is 8 hours.
0.9999 is 24 hours. What do I do beyond this. 1.3333 is
treated as 8 hours again. The "1" portion gets ignored as
the date part.

Am I making a mistake in using the Single Datatype. Is
there a way around?

A Single will work - but you can't use a Time or Date format to
display the result.

Try instead

Fix([Hours]) & Format(60 * ([Hours] - Fix([Hours]), ":00")



.