Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: Calculate hours between two Date/Time strings

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2012-08-24
06:44 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Calculate hours between two Date/Time strings

Hi,

I was wondering if someone would be able to help me with this query. I am new to Qlikview and have created a straight chart where all my information goes into. I have created a new column which I called **Time (log-close).**

In that new column I want to be able to calculate in hours, the difference between a field called **closedate(which is a date/time field 01/01/2010 09:00:00) **and a field called **logdate (which is also a date time field 01/01/2010 09:00:00).**

To calculate the difference I used the following expression: **Interval (closedate - logdate)**, which gave me the correct hours:minutes:seconds.

However our business hours are only between 08:00 - 18:00. Therefore if we had the **example 1** below, at the moment the hours would show as **49:00:00. **However as we are not interested in time before 08:00 and after 18:00, the correct time should show as **20:00:00.**

To make matters more complicated we are also **not** interested in weekends, therefore in **example 2**, the way we currently have it the hours would show as **66:00:00 **(as the dates are over the weekend), however the correct time should show as **04:00:00 (as this will ignore times after 18:00 and also the whole of Saturday and Sunday)**.

Is there a way this can be done?

Example 1:

**Logdate **- 01/08/2012 09:00:00

**Closedate **- 03/8/2012 10:00:00

Example 2:

**Logdate **- 03/08/2012 15:00:00

**Closedate **- 06/08/2012 09:00:00

31 Replies

Not applicable

2012-08-30
05:07 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks for you help on this one Rob.

1,365 Views

Not applicable

2014-07-02
06:21 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi!

How can i adapt this script adding a interval to lunch in 12:00 for 13:00?

*Interval(*

*rangesum(*

*NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(10) *

*// 10 hours per workday, for all day inbetween the period, excluding bounderies*

*,if(NetWorkDays(DT2,DT2,$(vHol)),Rangemin(rangemax(frac(DT2),maketime(8)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day*

*,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(8)),0) // working hours first day*

*,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(10)) // correct for first equals last day *

*)*

*)*

*as DurationStefan*

Thanks!

1,365 Views

swuehl

MVP

2014-07-06
12:56 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Maybe like in this post:

1,362 Views

Not applicable

2014-07-07
12:45 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Tkanks for your answer!

I used this script like below, but it have a some problem.

In case de dates are for example DT1 28/05/2014 08:49:18 DT2 28/05/2014 09:09:09 the answer is 01:19:51 instead 19:51. Any idea?

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(9) //8 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)), Rangemin(rangemax(frac(DT2),maketime(13)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(13)),maketime(13))

+Rangemin(rangemax(frac(DT2),maketime(8)),maketime(12))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(12))

+Rangemin(rangemax(frac(DT1),maketime(12)),maketime(12))-Rangemax(rangemin(frac(DT1),maketime(12)),maketime(8)),0) // working first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(9)) // correct for first equals last day

))

Thanks!

1,362 Views

swuehl

MVP

2014-07-11
01:38 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

There is a small error in the calculation of working hours first day:

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(9) //9 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)), Rangemin(rangemax(frac(DT2),maketime(13)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(13)),maketime(13))

+Rangemin(rangemax(frac(DT2),maketime(8)),maketime(12))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(** 13**))

+Rangemin(rangemax(frac(DT1),maketime(12)),maketime(12))-Rangemax(rangemin(frac(DT1),maketime(12)),maketime(8)),0) // working first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(9)) // correct for first equals last day

)) as DurationBreak

Not applicable

2014-09-09
08:12 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Stefan,

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,7/6/12 4:38 PM

6/29/12 4:45 PM, 7/6/12 4:19 PM

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

];

TMP:

LOAD ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP where WeekDay(Date)<5 and not match(Date,$(vHol)) group by ID;

1,362 Views

Anonymous

Not applicable

2015-09-25
06:47 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I have the near about the same requirement.

I have gone through your suggestion but i am not able to implement. I am very sorry.

Can you help me on that?

1,362 Views

Anonymous

Not applicable

2015-10-01
03:08 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Stefan,

Your Solution (the one without Lunch Breaks) works well but I have a small bug that I cannot seem to solve. The working hours is 08:00 to 17:00 so it works with most but I have for example a ticket that came in 17:11 and closed 17:28. It shows a 01:00 in my results. I have pulled apart the calc but it still does not work.

Any idea? attached is the script snippt and a screenshot of the timestamp and results

Interval(

rangesum(

NetWorkDays(TicketSubmittedDate+1,TicketAcceptedDate-1,** $(vHol)**) * MakeTime(8)

,if(NetWorkDays(TicketAcceptedDate,TicketAcceptedDate,

,if(NetWorkDays(TicketSubmittedDate,TicketSubmittedDate,

,if(NetWorkDays(TicketSubmittedDate,TicketSubmittedDate,

)) as ResponseTime

Thanx

1,362 Views

swuehl

MVP

2015-10-01
09:57 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

8:00 to 17:00 are 9 hours, right?

Interval(

rangesum(

NetWorkDays(TicketSubmittedDate+1,TicketAcceptedDate-1,** $(vHol)**) * MakeTime(9)

,if(NetWorkDays(TicketAcceptedDate,TicketAcceptedDate,

,if(NetWorkDays(TicketSubmittedDate,TicketSubmittedDate,

,if(NetWorkDays(TicketSubmittedDate,TicketSubmittedDate,

)) as ResponseTime

1,362 Views

sunilkumarqv

Specialist II

2016-03-18
07:08 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

HI Jonathan ,

we are facing similar kind of format text but want us a 24 hours with excluding weekends instead of working hours.

please help me

1,362 Views