Solved

Time calculation formula in Access

Posted on 2009-05-06
13
1,357 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 7

Expert Comment

by:coffeeshop
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
Thank you!

/gustav
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now