Solved

Time calculation formula in Access

Posted on 2009-05-06
13
1,393 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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 50

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 50

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 24411295
Thank you!

/gustav
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

737 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