Days Late

[Ricardo]

Wondering if anyone can help me!?.......

i need to be able to caluclate how late a job is but need to exclude weekends and workdays.

i have already been able to caluclate when the next stage of a job is due but the end calc is by date. e.g. job started 16/4/2004 with a 2 day lead time is due 20/4/2004 (17th and 18th are weekend days).

i need to be able to show a number value for days late.

Anyone know a calculation on how to do this? e.g. Job Sent 20/4/2004 was due to be sent 16/4/2004 so therfore is 2 days late excluding weekend days!

Thanks in Advance

[Fitch]

Here's one way to do it:

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

[Ricardo]

Fitch said:

Here's one way to do it:

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

not what i want! I actually need number of days late not an actual end date!

any more ideas?

[-Queue-]

This seems to work, assuming that no one will be entering a weekend date for either StartDate or EndDate:

EndDate - StartDate - 2 * Int((EndDate - StartDate)/7) - (WeekofYear(StartDate) <> WeekofYear(EndDate) and DayofWeek(StartDate) <> DayofWeek(EndDate)) * 2

[Ricardo]

-Queue- said:

This seems to work, assuming that no one will be entering a weekend date for either StartDate or EndDate:

EndDate - StartDate - 2 * Int((EndDate - StartDate)/7) - (WeekofYear(StartDate) <> WeekofYear(EndDate) and DayofWeek(StartDate) <> DayofWeek(EndDate)) * 2

Thanks very much, seems to work. Could you explain how the calculation works?

i just need to find a way now of taking off any public holidays which, i have recorded all public holidays in a db!?

Thanks again

[-Queue-]

Sure thing. One tweak first--the <> after DayofWeek(StartDate) should be a > only.

A = EndDate - StartDate => pretty much self-explanatory

B = 2 * Int((EndDate - StartDate)/7) => 2 times Int(number of weeks in date range) => 2 times the number of whole weeks in the date range => 2 times the number of whole weekends in the range => the number of weekend days for whole weeks in the range

C = (WeekofYear(StartDate) <> WeekofYear(EndDate) and DayofWeek(StartDate) > DayofWeek(EndDate)) * 2 => If StartDate and EndDate are in two different weeks and StartDate falls later in the week than EndDate, then there is a partial week (i.e. less than 7 days) for which to account => there is an extra weekend to exclude => 2 more days should be excluded.

So, simplified, the formula is Range of dates - number of weekend days for whole weeks within the range - 2 days if there is a partial week spanning more than one week of the year after the whole weeks have been removed.

Does that help?

[-Queue-]

To answer your second question, here's one possible method I think would work.

Create a calculation field equal to 1 in both the Main and Holiday files. Create a relationship from Main to Holiday (I'll denote it as const) based on these calculation fields. Create two global date fields in Holiday: gdStart and gdEnd. Create another calculation field in Holiday flagHoliday equal to dHoliday >= gdStart and dHoliday <= gdEnd. When you enter the StartDate and EndDate in the Main file, have a button to evaluate the days late and attach this button to a script that does the following:

Set Field [const::gdStart, StartDate]

Set Field [const::gdEnd, EndDate]

Set Field [daysLate, EndDate - StartDate - 2 * Int((EndDate - StartDate)/7) - (WeekofYear(StartDate) <> WeekofYear(EndDate) and DayofWeek(StartDate) <> DayofWeek(EndDate)) * 2 - Sum(const::flagHoliday)]

Let me know how this idea works for you.

[Ricardo]

Im mow using the calculation and works fine for days late.

but if the job is early it wont exclude the weekends.

e.g. if job due on 17th May (monday) and it was sent on the 14th May (friday) then the job is 1 woking day early but the calculation isnt excluding weekends and puts the result as -3 where as it should be -1

can you help?

[-Queue-]

There are two ways to do this relatively easily. One is to change the calculation to a test that determines which date is greater and 'flips' the calculation accordingly:

Set Field[daysLate, If( EndDate >= StartDate, EndDate - StartDate - 2 * Int((EndDate - StartDate)/7) - (WeekofYear(StartDate) <> WeekofYear(EndDate) and DayofWeek(StartDate) > DayofWeek(EndDate)) * 2,

StartDate - EndDate - 2 * Int((StartDate - EndDate)/7) - (WeekofYear(EndDate) <> WeekofYear(StartDate) and DayofWeek(EndDate) > DayofWeek(StartDate)) * 2 )- Sum(const::flagHoliday)

You would first

Set Field [const::gdStart, If(StartDate < EndDate, StartDate, EndDate)]

Set Field [const::gdEnd, If(StartDate < EndDate, EndDate, StartDate)]

Another option is to create two additional fields, cStartDate and cEndDate

cStartDate: If( StartDate < EndDate, StartDate, EndDate )

cEndDate: If( StartDate < EndDate, EndDate, StartDate )

then use

Set Field [const::gdStart, cStartDate]

Set Field [const::gdEnd, cEndDate]

Set Field [daysLate, cEndDate - cStartDate - 2 * Int((cEndDate - cStartDate)/7) - (WeekofYear(cStartDate) <> WeekofYear(cEndDate) and DayofWeek(cStartDate) <> DayofWeek(cEndDate)) * 2 - Sum(Const::flagHoliday)]

[Ricardo]

is there a way this can be done without a script and just used through a calculation field, as with the original calculation used for calculating days late?

thanks for your help.