Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Need help with calculating implementation date


Carlos F. Martinez
 Share

Recommended Posts

I need some help on a report.  I am trying to find out how to calculate an implementation date which is calculated as start date plus 20 working days.  In my company, working days are considered as the standard work week of Monday thru Friday.  See attached screen shot.  I would greatly appreciate any help on this.  Thank you.

NCR On Time date help.png

Link to comment
Share on other sites

If you have FileMaker Pro Advanced, you can use a custom function . (Please complete your profile so we can help better)

If not, a script would be required. The script would use a loop with the algorithm 

IF the day is a weekday (assuming no weekends) AND the day is not a holiday, THEN it is a workday so increment workday count.

You would use the DayOfWeek() function to determine weekdays, and a holiday table to determine holidays. 

(A calculation cannot be used because the solution requires recursion for a CF or a loop for a script.)

I'm sorry I don't have time right now to develop the script for you.

Link to comment
Share on other sites

Unfortunately,  I am using Filemaker Pro 12 Standard Edition so a custom function is not an option.  I've heard about the scripts to do this but I am unable to figure out how I would integrate it into the database I'm working on.  If anybody else has any ideas I would greatly appreciate it.

Link to comment
Share on other sites

Try this calculation:

Let ([     thedate = startDate;
           delay = 28;
           weekend = Choose ( DayOfWeek (thedate); 0;-2;0;0;0;0;0;-1 )
    ];     
           thedate + delay + weekend 
    )

Ask if you need any explanations.

  • Like 1
Link to comment
Share on other sites

Thanks for your help.  I am not very familiar with the Let function.  Where would I place this calculation?  As a calculated field or a script?

Link to comment
Share on other sites

You need to make the field for that date a calculation. Based on what I can tell from your screen shot, you should be able to make the Implementation Date a stored calculation, and just insert @David Head's calculation. When you save/close the Manage Database dialog, the dates should populate if you have a start date.

Link to comment
Share on other sites

Got it.  Initially, I put in the calculation as suggested by David but was getting blanks.  I found out I incorrectly set the field as a date when it should have been a calculation with the result being a date.  Once I corrected this the dates populated the column in the report.  Thanks to all for your help.

  • Like 1
Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use