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‌@

[qlikview979]

i am excluding here 14 hours from status  date  it means need to consider from 8/16/2017 12:00:00 A M to

8/16/2017 10:00:00 AM (not full days that's why  24 hrs-10 hrs =10 hrs)

Regards

mahesh

[qlikview979]

sorry

24-10 =14 hours(excluding)

[qlikview979]

if you have any doubts  please let me know.

Regards

Mahesh

[qlikview979]

please check my updated files

[Anonymous]

Re: Calculate hours between two Date/Time strings

[qlikview979]

please find my updated files

[sunny_talwar]

Try the attached and see if this works

[qlikview979]

Hi Bro,

its not giving correct output

for example:-

Event start date:-5/18/2017 9:30:00 AM ( this start date we need consider only 9 AM to end of the day it means include (15 hours ))   and Exclude first (9 hours)

Event Status Date:- 6/23/2017 1:50:04 PM(this date and  we need consider till 1 PM  it means 13 hours )))

and exclude remaining time(11 hours )

so this difference between this two dates is 27 days

here my calculation is 27*24=648

calculation=((648-9)-11)

result:-628

exclude 9 hours from start time

exclude 11 hours from status time

[sunny_talwar]

I think your status date if 6/23/2017 so in order to get the right hour time difference, you should be adding 13 hours instead of subtracting 11 hours. If you were finding difference between 6/24/2017 and 5/18/2017 then it would have been subtracting 11 hours. But if you don't agree then, try this

(((NetWorkDays([Event Start Date],[Event Status Date],$(vholidaylist))))*24)-(If(Exists(Nationalholidays, date(Floor([Event Start Date]))), 0, Hour([Event Start Time])))-(24-If(Exists(Nationalholidays, date(Floor([Event Status Date]))), 0, Hour([Event Status Time]))) as WorkingHrs,

[qlikview979]

Hi Bro,

Awesome ,exactly its working fine.

Regards

Mahesh