Hi Guys,

I'm confused by time calculations! I've looked through the forums for past posts and can find similar questions although the answers elude me.

I'm working on a timeslip layout. My fields are: Date, StartTime, EndTime, HoursWorked, HoursBilled, HourlyRate and a SubTotal. Hours Worked is the difference between the EndTime and Starttime (End_Time - Start_Time). The Subtotal is HoursBilled * HourlyRate.

What do I need to add to this calculation for it to return the correct result? If hours billed are 1.3, with an hourly rate of $40 the result is going to be $52, where it should be $60.

Should the HoursBilled be a time field, or number field? StartTime and EndTime are time fields.

Any help would be great, or just even a point in the right direction of similar posts to help me come to some clarity over Time in FMP...

Thank you

Try this:

I used timestamp fields in case of working over day changes.

hoursworked is a time calculation. The subtotal is a number field.

starttime = timestamp

endtime = timestamp

hoursworked = endtime-starttime (time field)

hourlyrate = number field

subtotal = (hoursworked/3600) * hourlyrate

timecalc.zip

Well, 1.3 * 4 = 52, so I don't see why "it should be $60".

HoursBilled can be be a Time field or a Number field - that's entirely up to you. If it's a Time field, a time of 90 minutes will be displayed as "1:30:00". If it's a Number field, it will be "1.5".

It's not clear how HoursBilled are derived from HoursWorked.

Well, 1.3 * 4 = 52, so I don't see why "it should be $60".

It should be $60, because it's 1 1/2 hours of billable time. If I work it out on 1.5 hours then I'm billing for 1hour and 50minutes, but 1.3 equals 1hour and 30minutes.

But that's just my limited understanding of time in FMP.

Ahh ok... This was were I was going wrong. I knew I had to divide something by 3600, and was dividing the entire calculation by 3600, rather than the time worked. It makes sense now, thank you very much.

I left the times as time fields rather than timestamps, as my work rarely crosses over a 24hour period. Fingers crossed it stays that way!!!

I enter HoursBilled myself... I don't always Invoice for the total hours I've actually worked, depending on what I'm doing, and I bill for 15 minute blocks minimum, sometimes it may be a five minute job... I thought about rounding the totals off to reflect that I work in 15min increments but it wont always give me a true reflection of what I do.

Cheers :(

This is not really a FileMaker issue. 90 minutes is 1.5 hours (one and half hours) in plain talk as well. If you don't want to use decimal notation, make the field a Time field - but then you have to enter the hours in time notation, i.e. "1:30".

I left the times as time fields rather than timestamps, as my work rarely crosses over a 24hour period. Fingers crossed it stays that way!!!

I would go ahead and use the timestamp just in case your work changes. That's what happened to me when we went from 1 shift to 3.

Or see here:

http://www.fmforums.com/forum/showpost.php?post/247287/

I only suggested the Timestamp method because I use this type of calculation for job starts and stops, not just employees. That way I'm covered even if a job continues non-stop for several days.

Using timestamps is preferable, but not always convenient in terms of user interface. That's why I pointed to an alternative. As you correctly noted, it only works with a single midnight crossing. If that's not a safe assumption, you HAVE to use timestamps - or at least note the ending date separately.

Ok, cool.

Timestamps then it is!

Best to build it for any and all future eventualities and save potential headaches.

Thanks heaps

Sorry, I realize that I posted this in the wrong forum index too...

For future reference, can it be moved to another forum after it's been posted?

R