• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

Determine difference between 2 times

i am writing a time and attendance application.  i have some datetime fields that contain the timein and timeout.  I would like to calculate the hours worked between these two times and round the hours to the 1/4 hour.

for example..

my table:

id | name | date | timein1 | timeout1 | timein2 | timeout2

or with data ..

1 | frank | 03/26/2006 | 8:00 AM | 12:00 PM | 1:00 PM | 5:15 PM

how can i cfset a variable for the correct total hours worked that day.  (for this record, the result for total hours worked should be .. 8.25)
1 Solution
<cfset TimeIn1 = CreateDateTime(year(Qry_date),Month(Qry_date),Day(Qry_date),Hour(Qry_timein1),Minute(Qry_timein1),0)>
<cfset TimeOut1 = CreateDateTime(year(Qry_date),Month(Qry_date),Day(Qry_date),Hour(Qry_timeout1),Minute(Qry_timeout1),0)>
<cfset TimeIn2 = CreateDateTime(year(Qry_date),Month(Qry_date),Day(Qry_date),Hour(Qry_timein2),Minute(Qry_timein2),0)>
<cfset TimeOut2 = CreateDateTime(year(Qry_date),Month(Qry_date),Day(Qry_date),Hour(Qry_timeout2),Minute(Qry_timeout2),0)>

<cfset TimeWorked1 = DateDiff("n",TimeIn1,TimeOut1)/60>
<cfset TimeWorked2 = DateDiff("n",TimeIn2,TimeOut2)/60>
<cfset TotalTimeWorked = TimeWorked1 + TimeWorked2>
Something like

<cfset morning = DateDiff(n, form.timein1, form.timeout1)/60> <!--- = difference between timein and out in minutes /60 to give hours --->
<cfset afternoon = DateDiff(n, form.timein2, form.timeout2)/60>
<cfset totaltime = morning + afternoon>

you'll probably have to play with rounding but should be close to what you want

datediff @ livedocs
Even better would be to calculate the difference using datediff when you pull from the database
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now