Converting decimal time into hh:nn:ss time in ACCESS

[M.Garza]

This has probably been posted a gazillion times but since I can't
understand VB code just yet (very limited ACCESS knowledge) I haven't
the slightest clue on how to covert a sum of time from decimal to
hh:nn:ss format.

I have created a query based off a table that houses all call center
employee computer (log time) time per day, I've managed to extract
monthly totals for #calls taken, # calls returned to que etc. When I
try to retrieve the minute totals I get a date in ACCESS rather than
the amount of minutes on the phone (00:00:15), I 've worked myself
around this issue by changing the format from 'mmmm yyyy' (this is how
the query brought it over) to 'hh:nn:ss'. After changing the format I
add another field to the query in design view to take the hh:nn:ss
format and turn it into a decimal time, this works great as I delete
the original field and change the (group by) option to sum on the new
decimal field and it gives me the entire month total for each employee.

After all this manipulation, I create a report to bring in all of the
sums for each employee on a monthly basis, all good..... I can
reformat the decimal time to hh:nn:ss with no problem as long as it is
below 24 hours. The problem is that this is a monthly report so the
"log in time, talk time, waiting time" undoubtedly will surpass 24 hrs.
I need to change the decimal time to hh:nn:ss format so the employee
can understand it as they are looking at their "progress" report.
I am not trying to be picky but I would most likely need an explanation
I can enter into the control box of the field (report) rather than go
through Marcos because I am clueless when it comes to CODE, MACROS,
etc.


Currently the report looks like this:
NAME LOGGED WAITING TIME
AVG TALK TIME
Doe, John 143.88 60.96
00:01:05

This is what I want it to look like

10/1/06 - 10/31/06
NAME LOGGED WAITING TIME
AVG TALK TIME
Doe, John 143:52:36 60:57:32
00:01:05 (this is fine since an employee will never have a talk
time of over 24 hrs)

I tried dividing the time by 24 and setting the format but it gives me
09:06:01 for 57:06:01 hrs.

Any help would be greatly appreciated, thanks!

 

[Douglas J. Steele]

Try:

Function FormatDecimalTime(DecimalTime As Single) As String
Dim lngHours As Long
Dim sngCalculated As Single

sngCalculated = DecimalTime / 24#
lngHours = Hour(sngCalculated) + _
24 * Int(sngCalculated)

FormatDecimalTime = Format(lngHours, "00") & ":" & _
Format(sngCalculated, "nn:ss")

End Function

 

[John Vinson]

This has probably been posted a gazillion times but since I can't
understand VB code just yet (very limited ACCESS knowledge) I haven't
the slightest clue on how to covert a sum of time from decimal to
hh:nn:ss format.

Access Date/Time values are best used to store specific points in
time, rather than durations, for exactly the reason you're seeing.
Summing them gives peculiar results, especially when they exceed 24
hours.

A Date/Time field is stored as a Double Float count of days and
fractions of a day since midnight, December 30, 1899. Sums over 24
hours roll into December 31 or January 1, 1900 and so on.

Consider using the DateDiff() function to extract the duration of a
call *as integer minutes or seconds* instead. For example, if you use
an expression like

CallDuration: DateDiff("n", [CallStart], [CallEnd])

you'll get a value like 67 for a 1 hour 7 minute marathon call. This
integer value can be summed, and you can use an expression like

=[CallDuration] \ 60 & Format([CallDuration] MOD 60, ":00")

in the Control Source of a textbox on a form or report in order to see
hours and minutes, e.g. 32:15.

John W. Vinson[MVP]

 

[Marshall Barton]

This has probably been posted a gazillion times but since I can't
understand VB code just yet (very limited ACCESS knowledge) I haven't
the slightest clue on how to covert a sum of time from decimal to
hh:nn:ss format.

I have created a query based off a table that houses all call center
employee computer (log time) time per day, I've managed to extract
monthly totals for #calls taken, # calls returned to que etc. When I
try to retrieve the minute totals I get a date in ACCESS rather than
the amount of minutes on the phone (00:00:15), I 've worked myself
around this issue by changing the format from 'mmmm yyyy' (this is how
the query brought it over) to 'hh:nn:ss'. After changing the format I
add another field to the query in design view to take the hh:nn:ss
format and turn it into a decimal time, this works great as I delete
the original field and change the (group by) option to sum on the new
decimal field and it gives me the entire month total for each employee.

After all this manipulation, I create a report to bring in all of the
sums for each employee on a monthly basis, all good..... I can
reformat the decimal time to hh:nn:ss with no problem as long as it is
below 24 hours. The problem is that this is a monthly report so the
"log in time, talk time, waiting time" undoubtedly will surpass 24 hrs.
I need to change the decimal time to hh:nn:ss format so the employee
can understand it as they are looking at their "progress" report.
I am not trying to be picky but I would most likely need an explanation
I can enter into the control box of the field (report) rather than go
through Marcos because I am clueless when it comes to CODE, MACROS,
etc.


Currently the report looks like this:
NAME LOGGED WAITING TIME
AVG TALK TIME
Doe, John 143.88 60.96
00:01:05

This is what I want it to look like

10/1/06 - 10/31/06
NAME LOGGED WAITING TIME
AVG TALK TIME
Doe, John 143:52:36 60:57:32
00:01:05 (this is fine since an employee will never have a talk
time of over 24 hrs)

I tried dividing the time by 24 and setting the format but it gives me
09:06:01 for 57:06:01 hrs.

It sounds like you are using a strange calculation to
calculate the total amount of time (maybe something like
endtime - starttinm?) This is apparently giving you a value
that is a duration (definitely not a time value) and you
should not try to treat it as if it were a time value. What
you are doing is essentially the same as saying you spent
3 O'Clock in yesterday's meeting. That just doesn't make
sense, you should say 3 hours instead.

The standard way of calculating an elapsed time is to use:
Logged: DateDiff("s", endtime, starttime)
and for the total:
TotalLogged: Sum(DateDiff("s", endtime, starttime))
These calculate the total number of seconds.

Since the number of seconds is not a time value, you can not
use a time format to get what you want. Instead, you need
to use an expression that calculates a string that looks
like it. You can use this expression in a text box:

=Int(TotalLogged \ 3600) & Format(TotalLogged \ 60 Mod 60,
"\:00") & Format(TotalLogged Mod 60, "\:00")

Note the use of the \ (integer divide) and Mod operators
(check VBA Help - Arithmetic Operators for details if you
are not familiar with them).

 

[M.Garza]

Thanks, but I'm not sure what to do with that. When I open the code
sheet, I copy and paste the infromation below but it does nothing.

My code sheet looks something like this:

(General) FormatDecimalTime
Function FormatDecimalTime(DecimalTime As Single) As String
Dim lngHours As Long
Dim sngCalculated As Single
sngCalculated = DecimalTime / 24#
lngHours = Hour(sngCalculated) + _
24 * Int(sngCalculated)
FormatDecimalTime = Format(lngHours, "00") & ":" & _
Format(sngCalculated, "nn:ss")
End Function

What am I supposed to do? Sorry but I'm more familiar with the design
view of the report and don't know the least bit about code. If you can
let me know what I can do and I'd truly appreciate it!

 

[M.Garza]

Well, the information in the table says that John doe was on the phone
for 5:53:25 for October 1, 2006. The info is brought over on a daily
basis so the phone time will be up to 175:25:36 for the month. I put a
report together that brings over this time but the only correct way to
bring it over is by changing it to decimal then I want to change the
total to hhh:nn:ss.

This is the current report:
NAME OFF LOGGED PAID
Janice Doe 0.25 7.56 8.25
James Doe 6.56 8.75
James Doe 7.56 7.75
James Doe 0.25 7.51 8.25
James Doe 0.75 6.84 8.75
James Foe 0.20 7.94 8
James Goe 0.25 6.26 8
James Roe 0.25 7.75 7.25
John Doe 1.87 5.88 8.75
Janice Doe 0.25 7.76 8
Janice Doe 0.42 6.56 7
Janice Doe 0.92 7.54 8.75
Janice Roe 1.87 6.59 8.75
Janie Goe 0.18 7.43 8.25
John Doe 0.25 7.68 8.5
John Doe 0.42 6.88 8
James Roe 7.56 7.75

Sum 8.12 121.85 138.75

Grand Total Grand Total Grand Total
8.12 121.85 138.75

I want both the OFF and LOGGED formatted as hhh:nn:ss

Name Sum Of Off Sum Of Logged



James Doe 0:15:00 7:33:36
James Doe 6:33:36
James Doe 7:33:36
James Doe 0:15:00 7:30:36
James Doe 0:45:00 6:50:24
James Foe 0:12:00 7:56:24
James Goe 0:15:00 6:15:36
James Roe 0:15:00 7:45:00
John Doe 1:52:12 5:52:48
Janice Doe 0:15:00 7:45:36
Janice Doe 0:25:12 6:33:36
Janice Doe 0:55:12 7:32:24
Janice Roe 1:52:12 6:35:24
Janie Goe 0:10:48 7:25:48
John Doe 0:15:00 7:40:48
John Doe 0:25:12 6:52:48
James Roe 7:33:36
8:07:48 121:51:36

Does this make sense?

 

[Douglas J. Steele]

Copy what I gave you into a new module and save the module, making sure you
don't name the module FormatDecimalTime.

Wherever you've got a calculation that returns the decimal time, wrap
FormatDecimalTime( ) around the calculation.

 

[Fatma]

hi,
I'd the same Problem here this code is just fine
get the time1 for the call begins and time2 for its end
then give them to this function it gives me the duration of the call
Put it in a new module then just type =TimeString([fld1];[fld2])
in your report in the ControlSource Property of the field u want the
duration
where fld1,fld2 are the time of call begin and end resp.
-------------------------------------------------------------------------------
Public Function TimeString(time1 As Date, time2 As Date) As String

Dim lHrs As Long
Dim lMinutes As Long
Dim lSeconds As Long
Dim Seconds As Long


Seconds = DateDiff("s", time1, time2)
lSeconds = Seconds

lHrs = Int(lSeconds / 3600)
lMinutes = (Int(lSeconds / 60)) - (lHrs * 60)
lSeconds = Int(lSeconds Mod 60)

Dim sAns As String


If lSeconds = 60 Then
lMinutes = lMinutes + 1
lSeconds = 0
End If

If lMinutes = 60 Then
lMinutes = 0
lHrs = lHrs + 1
End If

sAns = Format(CStr(lHrs), "00") & ":" & _
Format(CStr(lMinutes), "00") & ":" & _
Format(CStr(lSeconds), "00")
TimeString = sAns

End Function

 

[Marshall Barton]

Well, the information in the table says that John doe was on the phone
for 5:53:25 for October 1, 2006. The info is brought over on a daily
basis so the phone time will be up to 175:25:36 for the month. I put a
report together that brings over this time but the only correct way to
bring it over is by changing it to decimal then I want to change the
total to hhh:nn:ss.

This is the current report:
NAME OFF LOGGED PAID
Janice Doe 0.25 7.56 8.25
James Doe 6.56 8.75
James Doe 7.56 7.75
James Doe 0.25 7.51 8.25
James Doe 0.75 6.84 8.75
James Foe 0.20 7.94 8
James Goe 0.25 6.26 8
James Roe 0.25 7.75 7.25
John Doe 1.87 5.88 8.75
Janice Doe 0.25 7.76 8
Janice Doe 0.42 6.56 7
Janice Doe 0.92 7.54 8.75
Janice Roe 1.87 6.59 8.75
Janie Goe 0.18 7.43 8.25
John Doe 0.25 7.68 8.5
John Doe 0.42 6.88 8
James Roe 7.56 7.75

Sum 8.12 121.85 138.75

Grand Total Grand Total Grand Total
8.12 121.85 138.75

I want both the OFF and LOGGED formatted as hhh:nn:ss

Name Sum Of Off Sum Of Logged



James Doe 0:15:00 7:33:36
James Doe 6:33:36
James Doe 7:33:36
James Doe 0:15:00 7:30:36
James Doe 0:45:00 6:50:24
James Foe 0:12:00 7:56:24
James Goe 0:15:00 6:15:36
James Roe 0:15:00 7:45:00
John Doe 1:52:12 5:52:48
Janice Doe 0:15:00 7:45:36
Janice Doe 0:25:12 6:33:36
Janice Doe 0:55:12 7:32:24
Janice Roe 1:52:12 6:35:24
Janie Goe 0:10:48 7:25:48
John Doe 0:15:00 7:40:48
John Doe 0:25:12 6:52:48
James Roe 7:33:36
8:07:48 121:51:36

I think I misunderstood the data you are operating on here.

I believe Doug has provided a better reply and you should
pursue his approach.

 

[M.Garza]

Thanks.

I think I misunderstood the data you are operating on here.

I believe Doug has provided a better reply and you should
pursue his approach.

 

[M.Garza]

You ROCK!!!!!

It works great!

Thanks to everyone for your persistance and help.

Maria