1 Reply Latest reply on Jun 26, 2012 8:45 AM by philmodjunk

    Calculating Dates

    JackieBusch

      Title

      Calculating Dates

      Post

      In our quote file, we have the following fields:

      Approval Date (date field)

      Number of days from approval date to project start (number field)

      Project Start Date (calculated field - GetAsNumber ( Date Approved ) + Number of Days from Approval Date to Project Start

      This works fine and the project start date is correctly calculated.  However, is there a way to calculate the date to account for weekends and (if possible) holidays?

      Thanks.

      Jackie

        • 1. Re: Calculating Dates
          philmodjunk

          Your given calculation can be simplified to:

          Project Start Date (calculated field - Date Approved + Number of Days from Approval Date to Project Start

          Date fields, when you "pop the hood", turn out to be number fields so you can use them in calculations as though they are number fields and thus, you don't have to use getasNumber() here.

          There are several calculation methods for dropping out weekends. See this link: http://help.filemaker.com/app/answers/detail/a_id/3926/kw/weekend

          The challenge to adjusting for Holidays is that the dates your organization will be closed will vary every year and can't simply be made part of a calculated expression. Instead, you can set up a table where you can: a) make a record for each holiday date  b) make a record for each day you will be open or c) make a record for every day of the year with the days you will be open "marked" to show that they are open.

          You can then set up a relationship that matches by a range of dates to adjust for the number of holiday dates that fall on that range.

          In one of our solutions, I've set up a calendar layout where the boss can simply click a radio button to mark a date as "closed" and which automatically marks weekend dates (just Sundays in our business) as closed. A server scheduled script updates this calendar by adding a few new records every night after midnight.