Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Time calculation formula in Access

Posted on 2009-05-06
Medium Priority
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
Question by:samithsukumar
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

Expert Comment

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).

Expert Comment

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.

Author Comment

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

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.


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

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

Expert Comment

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.
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


LVL 44

Expert Comment

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
LVL 52

Expert Comment

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

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


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



Expert Comment

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.
LVL 52

Expert Comment

by:Gustav Brock
ID: 24334794
> .. 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)
strTimeWorked = Format(Format((datOut - datIn) * 24 * 4 - 4, "0") / 4, "0.0 \h\r\s")

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

Open in new window


Accepted Solution

coffeeshop earned 250 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
LVL 52

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")

LVL 52

Expert Comment

by:Gustav Brock
ID: 24411295
Thank you!


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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