how to count sundays

[kunkumnaveen]

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

[kunkumnaveen]

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

[kunkumnaveen]

any suggestion plz

[swuehl]

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

];

[kunkumnaveen]

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

[swuehl]

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)

[rahulpawarb]

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