Stuck on a Time/Calculation

[kenmoskowitz]

OK, I have spent months and am going in a circular pattern. I need to achieve the following.

Total Hours Needed should = XXX hours.

The fields available to create this CALCULATED number of hours is Event Start (time). Event Length (number), Setup Time (time), Pick Up Time (time).

To get total hours needed I am taking Event Start plus Event Length and assuming it can add 4 hours to 12:00PM and come up with 4:00PM, is that a safe assumption?

Then how do I get the calculation to add the Pick Up Time, lets say for example 10:30 AM.

My total should be 6.5 hours but I cannot get this to work. I must be an idiot. Now don't all agree at once OK?

Edited February 19, 2006 by Guest

[LaRetta]

Because you are overlapping days, you will need to include a date. If you are sure that you will never want to display the date, you can use the current date. FileMaker will then carry the date/time forward to produce the correct time no matter what the date ends up to be. It will be straight math.

What does the Setup Time have to do with the result?

[LaRetta]

You might try this, Ken ... Pickup Time (calculation, result of time)

If(

Event Start ;

Timestamp(Get(CurrentDate) ; Event Start ) + Event Length * 3600

)

Event Length would list total hours (even if 48 or 96 etc). I used the current date because you didn't have one. But that shouldn't be a problem because you don't want the date included in the final result. And times are consistent, regardless of the date. If you formatted that calc as TimeStamp(), you would get the correct extended date as well as the correct time. If you wish to include the Pickup Date, you would somehow need to capture the Event Start Date to replace Get(CurrentDate) in the calc.

But then wanting the Total Hours Needed seems circular. If you HAVE the Pickup Time and want to calculate the hours, we can do that as well ... but not without a real date to work with.

LaRetta :wink2:

[comment]

This is quite confusing.

I understand Event Start (time). Event Length (number) - is number of what? Hours? OK, let's say it's hours. So if the event starts at noon and lasts 4 hours, we know it ends at 4pm. If pick-up time is 10:30, then the time needed - from pick-up till end of the event - is 5.5 hours. But you say it should be 6.5 hours. OK, let's say that's a mistake. What about setup time? It doesn't come in at all. Should we assume pickup time is already calculated to account for that?

So under these assumptions, you can try this (set the result to type Time)

EventStart + Time ( EventLength ; 0 ; 0 ) - PickUpTime

As long as PickUpTime and EventStart fall on the same day, you don't need to worry about crossing midnight.

[kenmoskowitz]

Yes I should have said 5.5 hours.

So, the real simple answer is to get from pickup time (which is always early enough to include setup time) to the end of the event.

Am I making it too difficult having some fields be a time and the total hours being hours? I can't think of an easier way.

The application is fairly direct. The DJ needs to pick up his equipment at X time of day, the event starts at X time of day and lasts X many hours. For accounting purposes, we need to get the total hours from pickup to end time.

I feel like such a dweeb!

[comment]

You can have fields as time or hours or whatever - whatever is convenient for data entry. So, why don't you use the formula I gave you above?