6 Replies Latest reply: Apr 12, 2017 12:35 AM by Rahul Pawar RSS

    how to count sundays

    naveen kumar

      Hello every one i need a  suggestion on how to calculate actual days required for vehicles to reach at destination point including SUNDAY's.

        

      if Vehicle-1 requires 14 days to reached at destination and in-between 14 days if 2 sundays count is there so 

      actual days required for vehicle-1 to reached at destination point is 16 Days.

       

       

       

      Vehicle No.    Gate Out Date         Require Days to Reach at Destination

      A               02-01-2015                              5

      B               07-01-2015                             23

      C                  10-01-2015                          12

      D                  22-01-2015                            7

        • Re: how to count sundays
          naveen kumar

          Hello,

          what i am looking is

          let say vehicle-A required 5 days to reach destination  which means

          Gate Out Date  of vehicle-A is on 02-01-2015 and it will take 5 days to reach destination

          but when i show count for actual days for vehicle -A  then it will be 6 days because there is a Sunday in between

          so how to count Sundays

          • Re: how to count sundays
            naveen kumar

            any suggestion plz

              • Re: how to count sundays
                Stefan Wühl

                Maybe something like this?

                 

                 

                 

                Set DateFormat = 'DD-MM-YYYY';

                 

                 

                LOAD *,

                [Gate Out Date]+[Require Days to Reach at Destination]+Div([Require Days to Reach at Destination]+WeekDay([Gate Out Date]),7) as Est.Arrival;

                LOAD * INLINE [

                Vehicle No.,    Gate Out Date,         Require Days to Reach at Destination

                A,               02-01-2015,                              5

                B,               07-01-2015,                             23

                C,                  10-01-2015,                          12

                D,                  22-01-2015,                            7

                ];

                  • Re: how to count sundays
                    naveen kumar

                    HI,

                    [Gate Out Date]+[Require Days to Reach at Destination]+Div([Require Days to Reach at Destination]+WeekDay([Gate Out Date]),7) as Est.Arrival;


                    can u explain me what exactly this one is doing plzzz

                    thanks

                      • Re: how to count sundays
                        Stefan Wühl

                        It's supposed to calculate the date when the vehicle reaches destination, taking sundays into account.

                         

                        You may want to enclose above in a Dayname() function to format the result of the calculation as date.

                         

                        The first two operands are pretty simple, [Get Out Date] + [Require Days to Reach at Destination], that should return the arrival date not taking sundays into account.

                         

                        Div([Require Days to Reach at Destination]+WeekDay([Gate Out Date]),7)

                         

                        tries to calculate the number of sundays you need to consider. Have a look at Weekday() and Div() function, this should make it clearer.

                         

                        edit:

                        Just noticed that you may need to add +1, like

                         

                        Div([Require Days to Reach at Destination]+WeekDay([Gate Out Date])+1,7)

                  • Re: how to count sundays
                    Rahul Pawar

                    Hello Naveen,

                     

                    Trust that you are doing good!

                     

                    Please refer below given sample script:

                     

                    Data:
                    LOAD *,
                         [Require Days to Reach at Destination] + Ceil(([Require Days to Reach at Destination] - NetWorkDays)/2) As [Actual Days to Reach at Destination];
                    LOAD *,
                         NetWorkDays([Gate Out Date], [Reached at Destination]) AS NetWorkDays;
                    LOAD *,
                         Date([Gate Out Date] + [Require Days to Reach at Destination], 'DD-MM-YYYY') AS [Reached at Destination];
                    LOAD
                         [Vehicle No.],
                         Date#([Gate Out Date], 'DD-MM-YYYY') AS [Gate Out Date],
                         [Require Days to Reach at Destination];
                    LOAD * INLINE [
                    Vehicle No., Gate Out Date, Require Days to Reach at Destination
                    A, 02-01-2015, 5
                    B, 07-01-2015, 23
                    C, 10-01-2015, 12
                    D, 22-01-2015, 7
                    E, 01-01-2017, 45
                    ];
                    

                     

                    Also refer the sample application attached herewith.

                     

                    Regards!

                    Rahul