Solved

Time calculation formula in Access

Posted on 2009-05-06
13
1,373 Views
Last Modified: 2013-11-29
I want to know if we have inbuilt facility/query for calculating hours worked for an employee during one day?  Swipe in in morning: 07:00 and Swipe out: 18:00 evening. Total Hrs: 11 and less one Hour for Lunch. Hence Net Hours 10.
 
My another question is to convert time (mm:ss)  into Number with rounding to 0.5
For example
Swipe in : 07:10
Swipe Out: 17:35 then it should result into 9.5 hrs (After deducting 1 hour of lunch) and not 10
0
Comment
Question by:samithsukumar
13 Comments
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24314253
You can use DateDiff("h","07:00","17:00") for calculate hours, or DateDiff("n",StartDateTime,EndDateTime) for minutes. Use smallest unit you need for the result. Convert Lunchtime to the same base and subtract it.

You need a individual function to calculate industrial minutes (10 based).
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24314313
One addition: The calculation of the 10 based time depends on the smallest unit you need, i.e. if you need hour & industrial minutes the calculation is different as for minutes & industrial seconds.
0
 
LVL 3

Author Comment

by:samithsukumar
ID: 24314421
I need to have a Query written for this. For Example. Clock in time 7:30(This is in excel in hh:mm format)and Clockout 18:00 (same hh:mm format)evening. Now deduct 1 hr for lunch. Then the reault should be in Numaric format as 10.25 or 9.5 hours. The reult I need should be eiher 1.25 or 1.5 or 1.75 or 2 hrs only. So need some rounding as well. I need an Access Query as I have linked the excel to access in table format. But dont know how to write query for that.
am totally new to access
 
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 7

Expert Comment

by:coffeeshop
ID: 24314851
This function calculates the difference, subtract always 1 hour for lunch, and rounds always up to the next full 15 minutes. The result is in format hh:mm.

Format(TimeSerial(0,((DateDiff("n","07:00","17:31")-45)\15)*15,0),"hh:nn")

Change "07:00" and "17:31" to your starttime and endtime-fields.

There is no error-handling or something else like parameter for lunchtime.
0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24314884
You can change the above format to "h.nn" for a result like 9.15. If you need to calculate the result you should stack on the minutes.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 24315154
samithsukumar ...

In your query you  can create a field called TotalDecimal as shown below ... This assumes you will always subtract 1 hour for lunch.  Basically, calculate the difference in minutes between the clock in and clock out times then convert that to decimal hours subtracting 1 hour for lunch.  Replace dteClock_In and dteClock_Out with your actual field names in your table.

TotalDecimal: Round(DateDiff("n", dteClock_In, dteClock_Out) / 60, 2) - 1


ET



0
 
LVL 44

Expert Comment

by:GRayL
ID: 24316754
samithsukumar:  You want to round to the nearest half hour less 1 hour for lunch - correct?

Int(DateDiff("n",TimeIn,TimeOut))+0.5       - rounds to the nearest half hour

Int(DateDiff("n",TimeIn,TimeOut))-0.5        - round to nearest half hour less 1 hour
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24334131
Here is how to do this:

datWorked = CDate(Format((datOut - datIn) * 24 * 2 - 2, "0") / 24 / 2)

Explanation:

datOut - datIn  
finds the time duration.

(datOut - datIn) * 24 * 2
calculates the count of half hours worked

(datOut - datIn) * 24 * 2 - 2
deducts the lunch break

Format((datOut - datIn) * 24 * 2 - 2, "0")
Rounds (correctly!) the count of worked half hours to nearest half hour.

Format((datOut - datIn) * 24 * 2 - 2, "0") / 24 / 2
Converts the count of worked half hours to the numeric value of a time value.

CDate(Format((datOut - datIn) * 24 * 2 - 2, "0") / 24 / 2)
Converts the numeric value to a time value.

Now, the output you request is a decimal count of hours.
Thus modify this as here:

strTimeWorked = Format(Format((datOut - datIn) * 24 * 2 - 2, "0") / 2, "0.0 \h\r\s")

This divides the calculated count of half hours worked by 2 to obtain the count of full hours. Finally Format is applied to convert the decimal count of hours to a string:

9.5 hrs

/gustav










0
 
LVL 7

Expert Comment

by:coffeeshop
ID: 24334576
You should consider in your solutions that samithsukumar needs

1) industrial minutes (numeric, mainly used for calculations)
2) rounding up to next full 15 minutes, not half an hour

So the following solution shows a format in hours.minutes you can calculate with (add/subtract/sum results):

((DateDiff("n",DateIn,DateOut)-(60-14))\15)*15 / 60

What I need to explain is that 60 is the lunch-hour in minuntes and 14 is the rounding. This can be 29 if rounding to half an hour is necessary.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24334794
Originally:
> .. convert time (mm:ss)  into Number with rounding to 0.5

Later (missed that, sorry):
> The result I need should be either 1.25 or 1.5 or 1.75 or 2 hrs only.

To achive correct rounding to 15 minutes, modify like this:

datWorked = CDate(Format((datOut - datIn) * 24 * 4 - 4, "0") / 24 / 4)
and:
strTimeWorked = Format(Format((datOut - datIn) * 24 * 4 - 4, "0") / 4, "0.0 \h\r\s")

/gustav
In a query where [SwipeOut] and [SwipeIn] are fields holding the times:
 
SELECT
  *,
  Format(Format(([SwipeOut] - [SwipeIn]) * 24 * 4 - 4, "0") / 4, "0.0 \h\r\s") As Workhours
FROM
  tblYourTable
 
If [SwipeOut] and [SwipeIn] are not of data type DateTime, convert with CDate:
 
  CDate([SwipeOut]) - CDate([SwipeIn])

Open in new window

0
 
LVL 7

Accepted Solution

by:
coffeeshop earned 125 total points
ID: 24334917
@cactus_data: Your solution

? Format(Format((Date()+0.2 - Date()) * 24 * 4 - 4, "0") / 4, "0.0 \h\r\s")

throws out 3,8 - think this should be 3,75
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24358250
Correct. If 15 mins' resolution is needed so is two decimals:

strTimeWorked = Format(Format((datOut - datIn) * 24 * 4 - 4, "0") / 4, "0.0# \h\r\s")

or with fixed decimal count:

strTimeWorked = Format(Format((datOut - datIn) * 24 * 4 - 4, "0") / 4, "0.00 \h\r\s")

/gustav
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24411295
Thank you!

/gustav
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question