Date Calculations Killing me!

[Our Town]

I have deadlines for filing contracts.

Every month deadline changes, so it isn't always on the 15th of the month or on the second Wednesday of the month.

When a contract comes in and the date is entered, I need to know if it is past the current deadline and how many days past the deadline.

Have deadlines in a separate Table.

Contract dates are basic date fields.

The catch comes when my boss tells me not to charge for days on the weekend!

Obviously the deadlines occur on different days of the week, as do the contract dates.

Nothing I have tried works. Any ideas would be greatly appreciated! I am ready to cry!

Bev

[BobWeaver]

I've posted a couple of files on working days calculations in the samples forum, but they do a number of extra things you may not need. So, here is a link to Filemaker's knowledge base that gives a simple method:

http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=5281

[Our Town]

Thank you very much! I hope I can figure it out! -)

Bev

[Our Town]

What a miracle! After two days of trying to figure out how to do this, the solution was easy. Not sure if I understand WHY it works but it does work.

I added two If statements. One to check if the EndDate was before the StartDate...there would be no need to calculate days after StartDate... and one to check if the StartDate was blank. Which can happen if the account is not active.

StartDate (date)

EndDate (date)

Define a calculation field (WorkDays) with a number result with this formula:

If(StartDate="";"";

If(EndDate

Int((EndDate - StartDate)/7) * 5 + If(DayofWeek(EndDate) < DayofWeek(StartDate),

Min(5, DayofWeek(EndDate) - 1) + Max(0, 6-DayofWeek(StartDate)),

If(DayofWeek(StartDate) < 7, Min(6,DayofWeek(EndDate)) - DayofWeek(StartDate), 0))

))

It works perfectly. Wish I had posted this two days ago!

Bev