1 Reply Latest reply on May 19, 2015 6:12 AM by philmodjunk

    Date Calculation for the non-weekend/non-holiday day 2 days prior

    bokenrosie

      Title

      Date Calculation for the non-weekend/non-holiday day 2 days prior

      Post

      I have a field called SaleDate.

      I need to calculate the day that is two days before that date but is not a weekend or holiday (ie go back further if there are weekend or holidays in the way).

      Is there a function for this?

      TIA

        • 1. Re: Date Calculation for the non-weekend/non-holiday day 2 days prior
          philmodjunk

          A function by itself won't cut it as you have build in a way to track holidays and that requires checking dates against a list of holiday dates stored in a table in your database. (I had to calculate a date 5 business days in the future from the transaction date.)

          What I did was set up a calendar table with one record for each day of the year and a field that marked that record as open or closed. This was a calculation field that marked all weekend days as closed in addition to dates that were specifically marked as a holiday closure. cOpenStatus:  If ( Holiday or DayOfWeek ( date ) = 1 or DayOfWeek ( date ) = 7 ; "Closed" ; "Open" )

          A relationship can then be set up to match to all records marked "open" that have a date < the SalesDate:

          YourTable::SalesDate > HolidayClosures::Date AND
          YourTabe::constOpen = HolidayClosures::cOpenStatus

          This relationship to HolidayClosures can be sorted in descending order by double clicking the relationship line and specifying that sort order for HolidayClosures. Then the following calculation will get you your date:

          GetNthRecord ( 2 ; HolidayClosures::Date )