# Date Calculations Killing me!

This topic is 6088 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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

##### Share on other sites

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:

##### Share on other sites

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

Bev

##### Share on other sites

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

##### Share on other sites

This topic is 6088 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account