Convert number into hh:mm and calculate p/hr rate?

[Sticks1977]

I am not even sure if this is possible - but if it is I think I would have come to the right place. So far everyone here has been helpful with any problems I have had with FileMaker calculations.

My next issue with the database is to do with the 'Time' function. Is it possible to setup a script/calculation/formula to convert a numeric entry into a time result? I have looked into the 'Time' sub-menu in the 'Format' menu without any luck...

As an example, entering 360 (minutes) will produce 06:00, or entering 15 (minutes) will produce a result of 00:15 0 I would then use this as the base to calculate the per hour rate.

Currently the minute charges are in a number field and are entered as just a number, the calculation to produce the rate is:

Artwork (name of field) * 2.06666

I would be reluctant to create another field that would display the result (eg. 15 in one field, and then the 00:15 result in another field).

After this would be sorted, I would then require a calculation field that would use the time as the basis for a per hour rate.

The charge is $124.00 for 1 hour of work (01:00) - to break that down further, 15 minutes of charges (00:15) would give me a total of $31.00. I presume this would be a calculation? If so I have no problems that this would be a separate field.

If anybody could assist me or point me in the direction of another thread that has covered this topic it would be greatly appreciated. My thanks to all of those that have helped me with other topics already - I am very grateful.

Regards, Shaun (sticks1977)

[Søren Dyhr]

If you're paid by the minute:

Int ( theTime / 60) * 2.06666

--sd

[Sticks1977]

We are paid by the minute, but I will want to end up having two separate fields:

Field 1 will be entered by the user, say they enter in 15 and it will display 00:15 as a result for 00 hours and 15 minutes.

Field 2 will automatically calculate the p/hour rate, in this case 15 minutes will give a result of $31.00.

Will the calculation you have mentioned only work for Field 2? Should there be a separate calculation to achieve the 'time' result for Field 1?

[comment]

You can define your Field 1 as a Time field and set it auto-enter a calculated value (replacing existing value) =

Field 1 / 60

This will automatically convert an entry of "360" into "6:00:00". However, as a user I would find the discrepancy between the entry and the display formats rather confusing.

Field 2 can be simply =

Field 1 * Rate

Rates tend to change from time to time, so I suggest you keep the current rate in a global field or a preferences table, and look it up into the Rate field of your record.

[Sticks1977]

Thanks for your reply, where exactly do I place this calculation to get the end result?

Let's say the field I am looking to change is "Artwork".

I go into File > Define Fields... > Artwork (time field), then I click on the 'Options...' button.

Do I place the calculation in the Auto-Enter > Calculated value, or the Validation > validated by calculation ??

Also what calculation should I be looking to enter into the field?

I have entered "Artwork / 60" but it does not give me the result in a time format...

Any more help and assistance would be welcome...

Regards, Shaun (sticks1977)

[comment]

The field Artwork must be a Time field. Enter the formula Artwork/60 in Auto-Enter > Calculated value. Uncheck the "Do not replace..." option.

[Sticks1977]

I have entered those details into the calculation field and this is the alert that is displayed on screen (refer picture). The field has been made a Time field, the only thing I can think of is that I am running FileMaker Pro 5.5 - this function may not be available in this version?

Regards, Shaun (sticks1977)

[comment]

Sorry, I completely missed your version. This won't work for you, then. You need to use two fields: a number field for entry, and a calculation field for display. You can format the calculation field to NOT allow entry, and stack in on top of the number field - that way users will see them as one field.

[Sticks1977]

Bummer... at least there is a solution though - just a quick question if I may... would this feature that you spoke of be available in version 6 or 7? I'm thinking I may be able to track down an old version through eBay if I'm lucky...

[comment]

Version 7 and above.