By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,663 Members | 1,813 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,663 IT Pros & Developers. It's quick & easy.

DateDiff in Query

P: n/a
I am running a query with the following SQL below:

SELECT TimeClockHistory.ClockInDate, TimeClockHistory.ClockOutDate,
TimeClockHistory.Employee, Sum(DateDiff("n",[ClockIn],[ClockOut]))/60
AS TimeElapsed
FROM TimeClockHistory
GROUP BY TimeClockHistory.ClockInDate, TimeClockHistory.ClockOutDate,
TimeClockHistory.Employee;

ClockInDate, ClockOutDate, ClockIn, & ClockOut are all Date/Time
format.

I am trying to calculate the hours per week an employee works. Right
now when it is run, it might say... 8.31125455654 hours worked.

Is there any way, within the query I can make it so it will format
08:12 hours worked... As far as time is goes, a decimal format will not
work.

Any help would be greatly appreciated!

Feb 23 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
To get a custom format from 8.31125455654 you will have to write some
code. Here is a little bit to help you get started:

Dim i As Single, j As Single, str1 As String
i = 8.31125455654
'--separate out the decimal portion of the number
'--and get the minutes
j = (i - Int(i)) * 60
If Int(i) < 10 Then
str1 = "0" & Int(i)
Else
str1 = Int(i)
End If
If Int(j) < 10 Then
str1 = str1 & ":0" & Int(j)
Else
str1 = str1 & ":" & Int(j)
End If

This will yield "08:18". Then you can append "hours worked"

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Feb 23 '06 #2

P: n/a
Thank you!

Feb 24 '06 #3

P: n/a
Here is a function I wrote, that converts a number of seconds to a
time.

USAGE:
sec2dur(DateDiff("s",[starttime],IIf(IsNull([stoptime]),Now(),[stoptime])))
Just modify to remove the & format(secs,"00") to display hhh:mm without
seconds.

Public Function sec2dur(seconds As Long) As String
'converts a start and stop time to number of seconds. and
'then displays as hhh:mm:ss

On Error Resume Next

Dim hrs As Long
Dim mins As Integer
Dim secs As Integer

hrs = Int(seconds / 3600)
mins = Int((seconds - (3600 * hrs)) / 60)
secs = seconds - (hrs * 3600 + mins * 60)

sec2dur = Format(hrs, "#,##0") & ":" & Format(mins, "00") & ":" &
Format(secs, "00")

End Function

Feb 24 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.