Time calculation formula in Access

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
LVL 3
samithsukumarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

coffeeshopCommented:
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
coffeeshopCommented:
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
samithsukumarAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

coffeeshopCommented:
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
coffeeshopCommented:
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
Eric ShermanAccountant/DeveloperCommented:
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
GRayLCommented:
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
Gustav BrockCIOCommented:
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
coffeeshopCommented:
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
Gustav BrockCIOCommented:
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
coffeeshopCommented:
@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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
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
Gustav BrockCIOCommented:
Thank you!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.