Jump to content

Date Calculations Killing me!


This topic is 5654 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

Link to comment
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:

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

Link to comment
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

Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.