# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Contributor

## Calculate time difference between 2 time stamps excluding weekends and only working hours

Hi Qlikview Community,

I am trying to calculate the time difference in minutes between 2 time stamps?I would like to exclude weekends, holidays and consider work hours to be from 06:00AM to 11:00PM (Monday to Friday)?

How do i achieve this?

Original Data:

 OrderNumber Event EventTime 1129280 CreatedAssignment 2018-05-07T07:08:21.32 1129280 FinishedAssignment 2018-05-07T07:31:09.917 1129281 CreatedAssignment 2018-05-07T07:08:23.193 1129281 FinishedAssignment 2018-05-08T10:00:30.753 1128368 CreatedAssignment 2018-05-04T13:08:39.627 1128368 FinishedAssignment 2018-05-07T08:57:17.447

I have used the following expression to calculate the time difference:

'=Num(Interval(Only({<Event={'FinishedAssignment'}>}Timestamp(EventTime))-Only({<Event={'CreatedAssignment'}>}Timestamp(EventTime)),'mm'))*24*60'

and this gave me the following table:

 OrderNumber Lead Time(minutes) 1128368 4,069 1129280 23 1129281 1,612

However, it is correct only when the event time for started and finished assignment falls in the same day. However, i would like to exclude the weekends and including working hours for the day?

Desired Result:

 OrderNumber Desired Lead time(Minutes) Comments 1128368 769 Considering weekdays and work timings only 1129280 23 correct already 1129281 1,193 considering only work timings from 06 am to 11 pm

I am aware of network days. However, i am not able to write an expression to get what i need?

I have attached qvw file for your reference.

Thanks,
Vidya

@sunny_talwar  @swuehl  can you help me with this?

Labels (1)
• ### TimeDifferencebetween2timestamps

1 Solution

Accepted Solutions
MVP

## Re: Calculate time difference between 2 time stamps excluding weekends and only working hours

You can do it like shown in
https://community.qlik.com/t5/QlikView-App-Development/Calculate-hours-between-two-Date-Time-strings...

This code uses input data that shows the start and end timestamps in the same record line.

You can transform your data that shows the timestamps in different lines using e.g. a Group by LOAD statement.

Hope this helps.

Stefan

2 Replies
MVP

## Re: Calculate time difference between 2 time stamps excluding weekends and only working hours

You can do it like shown in
https://community.qlik.com/t5/QlikView-App-Development/Calculate-hours-between-two-Date-Time-strings...

This code uses input data that shows the start and end timestamps in the same record line.

You can transform your data that shows the timestamps in different lines using e.g. a Group by LOAD statement.

Hope this helps.

Stefan

Contributor

## Re: Calculate time difference between 2 time stamps excluding weekends and only working hours

Hi Swuehl,

Thanks for the reply.

This post helps.

I did not want to load the data by grouping in to each line for each event. Because there are so many exceptions and inconsistencies. I have been able to capture that in the front end.

I have referenced the following expression described in the post. It worked perfectly fine for me.

"=Interval(

(RangeMin(frac(Closedate), MakeTime(18))

- RangeMax(frac(Logdate), MakeTime(8)))

+ (NetWorkDays(Logdate, Closedate-1) * MakeTime(10)) // Only 10 hours per whole day

)"

I have attached the file if anyone wants to use.

Thanks,

Vidya