Calculation of 72 hours using Date&Time

[qlikview979]

Hi Experts,

In file i  have four columns like Event start date,Event End date,Event start time,Event status time.

Here i want  to exclude the Weekends&Public holidays (Date and time)and i want to calculate the difference between the working days .

Event state date- Event end date & Event start time-Event status time.

for example:-

Event state date   - 7/4/2017(July)

Event Status Date - 8/16/2017(Aug)

Event start time- 7/4/2017 6:00:00 AM

Event status time - 8/16/2017 10:09:20 AM

Three Scenarios:

1. If Event start Date is an holiday (7/4/2017 is an holiday)

Expression:

Event start time-Event status time = 7/4/2017 6:00:00 AM -  8/16/2017 10:09:20 AM

Actual Output :

Working Hrs = 726 Hrs

Expected Output:

Working Hrs = 730 Hrs

2. If Event start date and Status date are working days

Expression:

Event start time-Event status time = 1/3/2017 1:00:00 PM -  2/23/2017 4:38:06 PM

Actual Output :

Working Hrs = 877 Hrs

Expected Output:

Working Hrs = 867 Hrs

3. If Event End Date is an holiday, exclude Event Status Date & Event Status time ( However there is no such values in the data currently but in future we might get it. Hence need this logic as well)

Kindly help to get the above three scenarios.

mto‌loveisfail‌avinashelite‌stevedark‌QlikView App Development‌@

[Anonymous]

i guess , you should start with Networkdays() function.

[qlikview979]

Yes i am using what you suggested,but  i am not getting correct output.

Can you help me on this?

Regards

Mahesh

[sunny_talwar]

Can you explain why should Event start time-Event status time = 7/4/2017 6:00:00 AM -  8/16/2017 10:09:20 AM be 730 hrs?

[YoussefBelloum]

I was calculating this also, i didn't find that number...

[qlikview979]

For example

the Person  have to speak on stage on  8/16/2017 for that he has  trained on 7/4/2017 6:00:00.A.M.  but unfortunately he has given the update on 8/16/2017  10:09:20 like (not able to attend the meeting ) .

so here we need to exclude Weekends&Public holidays and who are given the update before 72 hours.

regards

mahesh

[qlikview979]

days we need to change hours

[sunny_talwar]

That is fine, but why is the number of hours 730 and not 726 the way you are already getting it?

[qlikview979]

Hi bro,

I have attached my files please look at that  ,we doesn't have any calendar .we have date&time columns and holidays list.

[qlikview979]

Hi Bro,

7/4/2017 6:00:00 AM -  8/16/2017 10:09:20 AM

the difference between these days is 31 days so here (31*24=744(744-14=730) hours  and start date  is holiday so need to consider from next day   and status date we are considering 24 hours but need to consider till 10 AM

regards

mahesh