6 Replies Latest reply: May 31, 2011 6:48 AM by Patrick Laredo RSS

    Remove weekend days

    Sravan Puppala

      Hi Guys,

       

       

      I have two days in a table..

      For ex:

       

      TableX:

      Load

      PlannedGoodsIssueDate,

      ActualGoodsIssueDate

      From X;

       

      From this table I need to find the delay in Service Performance. That means the

       

      Difference =  ActualGoodsIssueDate - PlannedGoodsIssueDate

      This is easy. Now my problem..

      I need to remove the weekends from the dates which are between ActualGoodsIssueDate and PlannedGoodsIssueDate because they are not counted.

      Any Idea how to get that?

       

      Thanks

      Sravan

        • Remove weekend days
          Patrick Laredo


          hi,

           

          have you tried the function Networkdays()?

           

          from the help

           

          networkdays ( start:date, end_date {, holiday} )
          Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

          Examples:
          networkdays ('2007-02-19', '2007-03-01')   returns 9

          networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26')   returns 8

           

          hope this helps

            • Remove weekend days
              Sravan Puppala

              Hi Pat,

               

              Networkdays gives the No of Holidays but it does not help in my case. When I am subtracting the dates, I need to find whether it is a weekend day and ignore if it is saturday or sunday..

              I hope you understand what I am after...

                • Remove weekend days
                  Nagaian Krishnamoorthy

                  networkdays ( start_date, end_date {, holiday} )

                  Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

                   

                  If you want to check if your start_date and/or end_date falls on a weekend and want to use previous or later workday, you  may try the functions firstworkdate and/or lastworkdate with your dates.

                   

                  e.g. networkdays(firstworkdate(start_date,1) - lastworkdate(end_date,1))

                   

                  Hope this helps.

                • Remove weekend days
                  Patrick Laredo

                  hi,

                   

                  networkdays definitely looks like the answer. Check this example:

                   

                  networkdays.png

                    • Remove weekend days
                      Sravan Puppala

                      Thanks Pat and Krishna

                       

                      Hi Pat,

                       

                      Thanks for the answer. I was not clear. sorry for that..

                       

                      What I am trying to do is.

                      GIDates.jpg

                      I have two Dates like this..

                       

                       

                      I need now to remove the Dates which are Holidays or weekends, when calculating the differences.

                       

                      For example.

                       

                      05-05-2011(DD.MM.YYYY) is the PlannedGoodsIssueDate and 09-05-2011 is the ActualGoodsIssueDate


                      it means, the difference is only 2 days(06-05-2011 and 09-05-2011) as 08.05.2011 and 09.05.2011 are Saturday and Sunday.

                       

                      It shows 3 Days when implemented.

                       

                      Am I understanding something wrong??

                       

                      Regards

                      Sravan

                        • Remove weekend days
                          Patrick Laredo

                          hi Sravan,

                           

                          taking your example you want to exclude the first day if I understand correctly. For example if the Planned GoodsIssue and the ActualGoodsIssue happen on the same day then the answer you want is zero. networkdays() on its own would show thsi as 1.

                           

                          Can't you simply then subtract 1 from the answer delivered by sung the function?

                           

                          =networkdays(date( PlannedGoodsIssueDate),date(ActualGoodsIssueDate))-1

                           

                          how does that look?