Get total hours between date + time fields

[Seanjuan]

Need to calculate the total number of hours between two points in time based on four fields, StartDate, StartTime, EndDate, Endtime.

I've tried, in shorthand:

Hour ( Timestamp(date;time) - Timestamp(date:time) )

Also tried:

Hour (DateEnd - Datestart) +

Hour (TimeEnd - TimeStart)

Tried all sorts of things, and I keep getting a freaked out number. Is there a simpler way of getting this data?

Thanks very much!

Edited December 5, 2007 by Guest
Need hours not horus...

[Seanjuan]

Here is my example:

4PM, Dec 4 - 3PM, Dec 6. The hours should be 47.

I'll add that this gives me the correct number of hours between the dates:

Hour (DateEnd) - Hour (DateStart)

This gives me a result of 48.

I can't figure out how to subtract the difference in times.

[comment]

Well, you could do:

Hour ( Timestamp ( DateEnd ; TimeEnd ) - Timestamp ( DateStart ; TimeStart ) )

but that will only give you the number of fully elapsed hours. This would be more accurate:

( Timestamp ( DateEnd ; TimeEnd ) - Timestamp ( DateStart ; TimeStart ) ) / 3600

The result should be Number for both.

[Seanjuan]

Ok, my next step that I did not forsee was to exclude any hours in that date range that fall on Saturdays. We are a non-profit television center and checkout our gear to members over the weekends but we do not open on Saturdays for users to return gear. For this reason, we give them a "free" day on Saturdays since they couldn't return the gear even if they wanted to.

In shorthand I might write this problem this way.

(End Timestamp - Start Timestamp) - 24hours for each Saturday.

But I don't know how to write this in Filemaker... any thoughts?

[comment]

The problem is not how to write this in Filemaker, but how to construct the algorithm of such calculation, using any tool (such as paper and pencil).

Although it could be done directly with hours, I think you may find it simpler to calculate the number of elapsed days, excluding any Saturdays, first. Then add the hours difference (which may be negative). Something like:

Timestamp ( DateStart + elapsedDays ; TimeEnd ) - Timestamp ( DateStart ; TimeStart )

This should help you with the first part:

http://www.fmforums.com/forum/showtopic.php?tid/155958/post/156042/#156042