Recently Written- Lion, iCloud, and Bento… October 25, 2011
- Bento 4.06 Available October 1, 2011
- Bento Price Drop Confirmed! August 23, 2011
- Bento 4.0.5 Available! August 9, 2011
- It’s Confirmed: Lion Is Fighting Bento! August 1, 2011
- Bento 4.0.3 Update Is Unstable May 21, 2011
- Customizing Themes Even Further April 23, 2011
- Sorely Needed: Page Guides On Forms! April 23, 2011
- List Field Refinements Needed April 12, 2011
- Dear Filemaker & Bento Product Manager: April 11, 2011
- New Free Service: iChat Screen-Sharing! April 10, 2011
- Bento 4 Crashes Reach BentoUsers! March 17, 2011
- Finally: Bento 4 – First Review! March 16, 2011
- Bento Black-Friday Pricing! November 26, 2010
- Important Software Update – Bento 3.0.5 November 4, 2010
Jul
20
Tracking Hours
Filed Under Tutorials
Many Bento users want to create a time-recording system of some type. This may be for something such as billing customers, tracking hours contributed by volunteers or monitoring employees. Although these are all slightly different the core functionality is the same and this article will explore some of the various options available.
The simplest form of time tracking is to simply keep a list of dates and the hours worked. This solution would use two fields, one a Date field and one a Number field:
The next step up from this is to use start and end times to calculate the number of hours worked:
Here the start and end times are Time fields and the Duration is a Calculation field. Showing the Time fields as hours, minutes and seconds is useful because Bento will assign the current seconds to a time if you do not explicitly set them yourself. For example, if the current time is 13:15:45 and you enter a time as 09:00 because you are only displaying hours and minutes Bento will actually store 09:00:45 which may cause problems with rounding. Therefore it is safest to have Time fields use the Medium display format rather than the Short one.
The Duration formula is:
The End Time – Start Time part is fairly self-explanatory and the division of the result by (60 * 60) is necessary because Bento returns the results of date and time calculations in seconds. Since the result of the calculation should be in hours the seconds need to be converted to hours which is done by dividing them by 3,600 or 60 * 60. Remember to set the results of the Calculation field to be displayed as a Number and with one or two decimal places depending upon how much detail you want to see.
Obviously, if the time recording system is being used to allocate hours to projects or clients additional fields may be needed to store the name of the project and/or the name of the client. Similarly if multiple employees or volunteers are being tracked then a field to store the employee or volunteer names would be necessary.
A more advanced time recording system would perhaps tie in with other Libraries. For example, rather than repeating employee or volunteer names it may be a good idea to use the Address Book or a dedicated Library to store the person’s details and then use a Related Records List to store the time-based details:
For more information about Related Records List please see our guide.
However, because Bento’s related record capabilities are designed to be simple there is an inherent lack of power and flexibility in them and so it may be better to either stick with a one-Library solution or live with some duplicate data and store the person’s name in the main time recording Library. This will allow for the creation of Smart Collections to filter and total records by person and date range which may be useful for calculating weekly, monthly, etc. totals.
Comments
19 Responses to “Tracking Hours”
Leave a Reply
I think some of thee tutorials start to show how limited bento is for a DB application, is that why you do not update this site so often anymore?
I agree that Bento does have limitations but they are for two good reasons as far as I can tell:
1. So as not to cannibalise sales of FileMaker Pro.
2. To cater for people who do not want to have to deal with the complexities of a fully-relational database application and who may not even be aware that they are technically using a database application.
As for the site updates being sporadic, the main reason is that I have been incredibly busy with my day job for the past six months or so and this has restricted my spare time and ability to add the content I would like to the site. Things should calm down again in September and then I can hopefully devote more time to the site.
Hi Simon,
When following your advice in regards to the intermediate time recording paragraph. When I set the time fields to medium and enter a time, I get the date Monday 1st January 2001 showing up all the time? What am I doing wrong??
Many thanks
Scott
Hi Scott,
It sounds like you may have created a Date field rather than a Time field. If you double-click on the field name to edit the filed it should indicate what type of field you have created.
‘me’ is a punk. Nice site, thanks for the help.
I am trying to do a time tracking system that allows me to enter the time and then assign it to categories.
That part works great. I can even create smart collections to sort by individual category.
However, I was wondering if there is anyway to easily create a summary page that would add all the hours in each category and then show those totals.
Thanks.
Hi WD. Unfortunately I can’t think of an easy way to do this. It may be worth posting your question in the official Bento Forums (http://forums.filemaker.com/fmbnto/) however because someone there may come up with something.
Will do and thanks for the response.
Cheers.
I’ve been using your duration calculation just fine, until I hit an end time that crossed into the next morning. Is there any calculation you can think of that can handle this without using IF/THEN statements?
John, you’d need to use the dates as well as the times and then it should work. Essentially change the start and end time fields so they are date fields which also display the time and then the same calculation should be okay. Obviously you can then remove the original date field.
In addition to not cannibalizing Filemaker, IMO a major reason Bento is so useful is that it doesn’t have all the features that the target user of Bento would need to bother with. Bento fills a gap that no one (to my knowledge) has managed to properly address on either a Windows or Mac platform. Bento is easy to use, customize, and not to mention — afford. (After buying a Mac, one needs a break on software costs! heh.)
Thank you for your efforts with this site, Simon.
What would be the Calculation for overtime hours,
and if No time is entered how can i set it to register 0
Hi,
Thanks, this is exactly the application is was looking for. I use ical and Bento, but now do the math myself for the accountant (in NL you need to work so many hours, to qualify for tax reduction is you’re a freelancer).
But somehow your trick doesn’t work. In the icall part of Bento, the date and starttime / date and end time are in the same field. Any thoughts?
It’s actually simpler if you are using the iCal Start Date and End Date fields. Simply make the formula: [End Date] – [Start Date]
There is no need to convert the result from seconds and you can also display the result as a duration type which makes it even more readable.
You can see a screenhot of the field’s definition at http://emberapp.com/sgaw/images/calculating-durations-in-a-bento-ical-library.
Thanks Simon, that did the trick!! Working perfectly now..
Hey Simon, I am testing Bento to create a simple DB for creating job sheets and hours worked for us to use in house.
So far so good – beats the price on FMP
Qu.1 – How do you set your time to 24 hour settings (cannot seem to find any preferences for it)
Qu.2 – Noticed from your home page comments you are a bit concerned about no release of Bento 4, you don’t think Bento is going the way of the dodo. I have already invested a whole day to setting up a template, would hate to start adding data and then they can the software.
Thanks for your tutorial above. very cool.
cheers,
Dave
Hi Dave, it’s Florian now, but Simon is still available when needed.
Answer 1 – Bento uses your OSX time settings. If you want 24 hour display, you’ll have to change your system time formats (in Language & Text).
Answer 2 – I’m not concerned that Bento will go away or not be supported by Filemaker. I wouldn’t worry.
I am working on a travel form in Bento 4 and want a simple field for displaying my layover between flights. The only solutions I have seen for calculating time durations are the 60*60 thing you suggest above. But this comes out as a percentage of time, not as a number of hours and minutes. Its telling me I have .83 hours to wait, when I want it to tell me I have 50 minutes. Any way to refine this?
Because you’re dealing with minutes you only want to divide the time by 60. If I set up two time fields, Start Time and End Time, and then add a Calculation field, Layover, with the formula: ([End Time] – [Start Time]) / 60 and the result shown as a number I get the number of minutes. Alternatively, create a Calculation field with the formula: [End Time] – [Start Time] and set the result to Duration.