3 Replies Latest reply on Dec 8, 2011 1:32 PM by philmodjunk

    Removing Sunday from total days of the month

    nickodm

      Title

      Removing Sunday from total days of the month

      Post

      Hello all,

      I thought this would be faily easy but somehow I am stuck on it. Our production department used to work Mon - Sun. So for the report their number of working days would be the current days in the month. Thus I was able to handle it with this function:

      Day ( Get ( CurrentDate) ) in an unstored field

      Now they will be closed on Sundays. So the report needs to remove any Sundays from the working days number.

      All the examples I was able to find need a start and an end date. But I am trying to do this on the fly. The goal is that at any given time when the end-user runs the report the number work working days are automatically calculated to be the number of days so far in the month minus any Sundays. 

      I hope that makes sense.

        • 1. Re: Removing Sunday from total days of the month
          philmodjunk

          Will they be closed for any other dates, such as holidays? I've seen a calculation that computes the weekdays that might be adjustable to include Saturdays, but there's no point in trying that if you also need to not count holiday closure dates in your total day count. I know of and use a different method that adjusts for holiday closures as well as weekend closure dates.

          • 2. Re: Removing Sunday from total days of the month
            nickodm

            Yeah I guess they will be closed for holidays too. I figured I'd start with just auto-removing Sundays and drew a complete blank. :)

            • 3. Re: Removing Sunday from total days of the month
              philmodjunk

              I asked because the week day example cannot be extended to include holiday closures that are subject to change each year so this is a case where starting simple can paint you in a corner.

              We have to be able to print a notice that customers can return for their money 3 business days after the day they sold us some non-ferrous scrap metal. (It's legal requirement intended to deter metal thefts.) Since we close on weekends and also a few holidays, the method I used had to account for both.

              I set up a table of "days" with one record for each day in the calendar and a date field that stores the date for that day. A status field marks each record as "open" or "closed". By counting the number of records in such a table that are "open" for a specified month, you'd get your count of days open.

              For your purposes, I'd add a calculation field, cMonth to this days table defined as: Datefield - day ( datefield ) + 1 and set to return date as its data type. This field returns the date of the first day of the month for that date's month so all records for the same month have the same value in this field. I'd also add a summary field named sTotalOpen and define it as the "count of" the date field.

              Set up a relationship based on cMonth like this:

              YourTable::cMonth = Days::cMonth

              or this:

              YourTable::cMonth = Days::cMonth AND
              YourTable::constOpen = Days::Status

              With the first relationship, you can place sTotalOpen in a one row filtered portal that filters out all dates that aren't "open" to show the total days open.

              With the second relationship, you can refer to sTotalOpen in a calculation field and get the total days open. (constOpen is a calculation field that returns "open" to match only to records with that same exact value in the days::Status field.)

              I used a variation of this Calendar solution to manage what days we are open and closed: http://www.4shared.com/file/6UAOV_RZ/Calendar.html