Help with time calculations on a timeslip

[Roxy201]

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

[jstaphse]

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

[comment]

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.

[Roxy201]

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.

[Roxy201]

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

[Roxy201]

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 :(

[comment]

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

[jstaphse]

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.

[comment]

Or see here:

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

[jstaphse]

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.

[comment]

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.

[Roxy201]

Ok, cool.

Timestamps then it is!

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

Thanks heaps

[Roxy201]

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