1 2 Previous Next 28 Replies Latest reply on Nov 4, 2019 12:20 PM by Joseph Hall Go to original post
      • 15. Re: DateDiff for Working days only
        chandra shekhar banerjee

        Hi All,

         

        I have found a way to exclude weekends and have only working days between two days.

         

        1. Create a set of date [Order date] where write a formula condition:

         

        DATENAME('weekday',[Order Date])!="Saturday"

        and

        DATENAME('weekday',[Order Date])!="Sunday"

         

        2. Use the set to create a calculated field "calculated date" :

         

        if [Set 1] THEN [Order Date] END

         

        It will create a calculated calendar field where weekends are not present.

         

        3.Create to parameter "From Date" & "To Date".

         

        4. Another calculated field

         

        countd(if dateturnc('day',"From Date" >)= dateturnc('day',"Calculated date") and dateturnc('day',"To Date" )

        <= dateturnc('day',"Calculated date" >) then "Calculated date" end)

         

        it will serve the purpose.

         

        Thanks,

        Chandra Shekhar

        • 16. Re: DateDiff for Working days only
          Tim Weber

          Just a thought, would the below formula work?

           

          DATEDIFF( 'weekday', [Start Date], [Finish Date], 'monday') - (DATEDIFF( 'week', [Start Date], [Finish Date], 'monday') * 2 )

          • 17. Re: DateDiff for Working days only
            Benjamin Rubattel

            Hello,

             

            I am trying to use this formula to calculate the Number of Days between two dates, either positive or negative.

             

            It doesn't work for the case below, - I am calculating the difference between the Statistic Delivery Date and the posting date

             

            In the line 20 it should factually be -1, as the posting date was made earlier,  but it comes up at 4..

             

            Any Ideas?

             

            thanks

            Benjamin

             

            • 18. Re: DateDiff for Working days only
              Robin Foster

              The formula requires creation of 3 Calculated fields: Weekday Begin, Weekday Closed, and Networkdays

               

              [WEEKDAY BEGIN]

                   //Determine the day of the week that your process began

                   datepart('weekday',[Process Begin Date],'Sunday')

               

              [WEEKDAY CLOSED]

                   //Determine the day of the week that your process closed

                   datepart('weekday',[Process Close Date],'Sunday')

               

              [NETWORKDAYS]

                   //Calculate total days for full weeks past, relative to the Process Begin Date

                        int(([Process Close Date]-[Process Begin Date]-1)/7) * 5 +

                   //Calculate remainder days (over and above full weeks), relative to the day of the week that your process began

                        case [Weekday Begin]

                             when 1 then

                                 if [Weekday Closed]=1 then 0

                                 elseif [Weekday Closed]=7 then 4

                                 else [Weekday Closed]-([Weekday Begin]+1) end

                             when 2 then

                                 if [Weekday Closed]=1 or [Weekday Closed]=7 then 4

                                 else [Weekday Closed]-[Weekday Begin] end

                             when 3 then

                                 if [Weekday Closed]=1 or [Weekday Closed]=7 then 3

                                 elseif [Weekday Closed]>[Weekday Begin] then [Weekday Closed]-[Weekday Begin]

                                 else [Weekday Closed]-[Weekday Begin]+5 end

                             when 4 then

                                 if [Weekday Closed]=1 or [Weekday Closed]=7 then 2

                                 elseif [Weekday Closed]>[Weekday Begin] then [Weekday Closed]-[Weekday Begin]

                                 else [Weekday Closed]-[Weekday Begin]+5 end

                             when 5 then

                                 if [Weekday Closed]=1 or [Weekday Closed]=7 then 1

                                 elseif [Weekday Closed]>[Weekday Begin] then [Weekday Closed]-[Weekday Begin]

                                 else [Weekday Closed]-[Weekday Begin]+5 end

                             when 6 then

                                 if [Weekday Closed]=1 or [Weekday Closed]=7 then 0

                                 elseif [Weekday Closed]>[Weekday Begin] then [Weekday Closed]-[Weekday Begin]

                                 else [Weekday Closed]-[Weekday Begin]+5 end

                             when 7 then

                                 if [Weekday Closed]=1 or [Weekday Closed]=7 then 0

                                 else [Weekday Closed]-[Weekday Begin]+5 end

                        end

              • 20. Re: DateDiff for Working days only
                James Rizkallah

                I found it cleaner to set monday to the start of the week so that the weekdays are 1 - 5

                 

                [Todays Day Number]

                date(NOW())-datetrunc('week',date(NOW()),'Monday')+1

                 

                [Last Day of Month Day Number] //date dimension includes all dates from the current month

                date(max([date]))-datetrunc('week',max([date]),'Monday')+1

                 

                 

                // (1) Calculate number of whole weeks between dates, then convert to # of Work Days

                    INT((MAX([date])-NOW())/7)*5

                 

                +

                 

                // (3) Add # of Work Days for last week

                if [Last Day of Month Day Number] >= [Todays Day Number] //start and end is in the same week (mon to sun)

                    THEN

                        IF [Last Day of Month Day Number] = 7 //if ends on sunday removes an extra day

                            then max([Last Day of Month Day Number] - [Todays Day Number] - 1,0)

                        ELSEIF [Last Day of Month Day Number] = 6

                            then [Last Day of Month Day Number] - [Todays Day Number]

                        ELSE [Last Day of Month Day Number] - [Todays Day Number] + 1

                        end

                ELSE //if the start and end date fall into separte weeks need to break up the weeks in two

                    //the first section treat 7 as the last day

                    max(7 - [Todays Day Number] - 1,0)

                 

                    +

                    //the second section treats the start date as 1 so its just the day number or 5, whichever is less

                    min([Last Day of Month Day Number],5)

                end

                1 of 1 people found this helpful
                • 21. Re: DateDiff for Working days only
                  DANIEL GUZMAN

                  This one works OK, the only problem is when both dates are on the same week and the end date is Saturday or Sunday.

                   

                  I solved it like this. ID-Initial Date FD-Final Date

                   

                  In BOLD is the original formula, added only some conditions for the case when both dates are on the same week.

                   

                  IF DATEPART('week', [FD],'monday')<>DATEPART('week', [ID],'monday') THEN

                     DATEDIFF("weekday",[ID],[FD],'monday')-2*(DATEPART('week', [FD],'monday') -DATEPART('week', [ID],'monday'))

                  ELSEIF DATEPART('day', [FD],'monday')=6 THEN

                      DATEDIFF("weekday",[ID],[FD],'monday')-1

                  ELSEIF DATEPART('day', [FD],'monday')=7 THEN

                      DATEDIFF("weekday",[ID],[FD],'monday')-2

                  ELSE

                      DATEDIFF("weekday",[ID],[FD],'monday')

                  END

                  • 22. Re: DateDiff for Working days only
                    Mike Kiel

                    Thanks for posting this Lynn and Alex. This will be very useful!

                    • 25. Re: DateDiff for Working days only
                      Shinichiro Murakami

                      You are welcome.

                       

                      Shin

                      • 26. Re: DateDiff for Working days only
                        Mike Kiel

                        Sina,

                         

                        When I have issues with blending, I use a left join on the two data sources.

                         

                        Hope this helps (7 years later.....)

                        • 27. Re: DateDiff for Working days only
                          PlanOmatic Admin

                          This thread had the answer I was looking for related to this topic: Re: Number of weekend days between two dates (or business days)

                          • 28. Re: DateDiff for Working days only
                            Joseph Hall

                            I use the following to calculate workdays (does not address holidays):

                             

                            // Calculate work days - 5-day work-week (change the 2 to a 1 for 6-day work-week)

                             

                            ([Completed Date] - [Created Date]) - ((DATEDIFF('week',[Created Date],[Completed Date])) * 2)

                            1 2 Previous Next