Date and Time Calculation

[Paul Washburn]

Forgive me if this is a simple question but I am brand new to File Maker Pro. How do I calculate either early, on-time or late, time (in hms) based on 4 fields. 2 are date fields labeled "Proof Due Date" and "Proof Delivered Date", the other 2 are time fields labeled "proof Due Time" and "Proof Delivered Time".

The results in the calculation filed are based on the following:

Int((Proof Delivered Date - Proof Due Date)/7) * 5 + (Proof Delivered Time - Proof Due Time) + If(DayofWeek(Proof Delivered Date) < DayofWeek(Proof Due Date),

Min(5, DayofWeek(Proof Delivered Date) - 1) + Max(0, 6-DayofWeek(Proof Due Date)),

If(DayofWeek(Proof Due Date) < 7, Min(6,DayofWeek(Proof Delivered Date)) -

DayofWeek(Proof Due Date), 0)) *86400

We are on a five day work week excluding Sat and Sun. Most of the calculations will be within a day or 2 of each other and where I am getting improper results is when the Proof Delivered Date is earlier than the Proof Due Date. I am expecting negative results but I am getting positive instead. Any help fixing this calculation will be appreciated.

Paul Washburn

[Lee Smith]

Hi Paul,

Check out this Tech article by FileMaker

Calculating Number of Weekdays (Work Days) Between Dates

http://www.filemaker.com/ti/101268.html

Lee

[Paul Washburn]

Thanks for the link.

Here is more specific info on what does not calculate correctly. We are setting an estimated date and time(Proof Due Date and Proof Due Time). For example, the due date will be set as 5-23-2003 at 11:00AM. Many of the proof delivered date and time entries will be the same date with slight time variance. This works fine as does a day or 2 late. Where I see incorrect results is when the date and time is entered as 5-22-2003 at 11:00AM for delivered date and time. This should result in a negative 240000 entry. Instead I get 960000 and that is where I am having difficulty figuring out what is wrong?

[Ugo DI LUCA]

Well,

If the Due Date is 6-22-2003 at Due Hour 11:00AM and that the Delivry Date is 5-22-2003 at Delivery Hour 11:00AM, the result should be -86400.

Why would you like it to be -240000 ?

Try a simple calc

(Delivery Date - Due Date)*86400)+ (Delivery Hour - Due Hour)

[Paul Washburn]

The -240000 (hms -24:00:00) represents 24 hours early on an end date (delivered) of 5-22-2003 and the start date (due) of 5-23-2003. At issue is the end date being BEFORE the start date.

The simple calc, I believe, will have a problem with Saturday and Sunday since we are on a 5 day work week.

[cjaeger]

time calcs are in seconds

60 [secs] * 60 [mins] * 24 [hours] make a day => 86400

[Ugo DI LUCA]

OK,

Now that you understood the tip about seconds in date calcs, let's have a try with this one.

To determine if the delivery date is after a week-end, by deduction, we need to know which week the good was delivered and compares it with the due date.

If due date = week 2 and delivery date = week 3, then there are 2 days to be substracted from the calc. If due date = week 2 and delivery date = week 5, then (5-2) = 3 *2 days = 6 days to be substracted.

Let split the calc with an intermediate calc for this calculation

nbofweeksfromdue = weekofYear(DeliveryDate)-weekofYear(DueDate)

So, (I didn't tried it though), this should work.

Case(weekofYear(Deliverydate)#weekofYear(DueDate), (Delivery Date - Due Date - (nbofweksfromdue *2))*86400+ (Delivery Hour - Due Hour);

(Delivery Date - Due Date)*86400+ (Delivery Hour - Due Hour))

[Paul Washburn]

Thanks Ugo,

I am getting closer. I have a question about the Case. What is the # sign before weekofYear(DueDate)? Should that be either a < or > sign?

[Paul Washburn]

I think I have answered my own question. The case should be testing for "not equal to" which is "<>" or on a Mac "=" with a slash through it

[Ugo DI LUCA]

Yep!

Let me know if it doesn't work

[Paul Washburn]

Ugo,

I can now track on-time calculations for our proofs! Thank you very much for sharing your knowledge. I appreciate that you took pity on a newbie like me and offered to help.

Paul W.

[Ugo DI LUCA]

Glad it worked !

And thanks for the info that the # character doesn' exist on Windows.