Urgent: calculating working hours from 2 date fields

[sayadutt]

Hi All,

I have 2 date fields [Issue Open Time] and [Issue Closed Time] which are of the following format: 1/27/2015 12:00 AM

My Requirement is:

To calculate the hour difference between the two fields based on working hours.

Working hours: 10 AM to 7 PM

Lets say 1 ticket is opened at 11 AM and closed at 2 PM same day. The difference will be 3 hours (simple one)

But lets say, another ticket was opened on 1/27/2015 5 PM and was closed next day 1/28/2015 11.30 AM.

So the period for which the ticket was opened would be:

2 hours from 1/27 ( 5 PM to 7 PM)

and 1.30 hours from 1/28  (10 AM to 11.30 AM) = TOTAL 3.30 hours

Can you please let me know how I can get this value (duration for which the ticket was opened)

Thanks

[sayadutt]

Thanks Michael, it works.

For 1 more updation I need for help.

My DateEnd can be blank. (when the ticket is still in open state). For this scenario we need the count the downtime based on system reload time. From the time ticket is opened till now (when reloaded).

How can we do that.

Sample data:

LOAD * Inline [

  Project, Release, DateInitial, DateEnd

  1, b, 10/12/2013 9:00:00 AM, 10/14/2013 9:00:00 AM

  1, b, 10/11/2013 5:00:00 PM, 10/12/2013 8:40:25 AM

  2, c, 10/11/2013 8:41:03 AM, 10/14/2013 8:43:11 AM

  2, c, 10/14/2013 8:39:09 AM, 10/14/2013 6:59:11 PM

  1, c, 10/14/2013 8:39:09 AM, 10/14/2013 10:39:11 AM

  1, b, 10/14/2013 8:39:09 AM,                                   

];

last row ticket is still open

[Anonymous]

You need to replace the missing DateEnd with the current time using function now().  In case of inline load, you can do it like this:

Data:
LOAD
RowNo() as Key,
Project,
Release,
DateInitial,
timestamp(if(len(trim(DateEnd))=0, now(), DateEnd)) as DateEnd
Inline [
Project, Release,  DateInitial,  DateEnd
1,  b,    10/12/2013 9:00:00 AM, 10/14/2013 9:00:00 AM
1,  b,  10/11/2013 5:00:00 PM, 10/12/2013 8:40:25 AM
2,  c,  10/11/2013 8:41:03 AM, 10/14/2013 8:43:11 AM
2,  c,  10/14/2013 8:39:09 AM, 10/14/2013 6:59:11 PM
1,  c,    10/14/2013 8:39:09 AM, 10/14/2013 10:39:11 AM
1,  b,    10/14/2013 8:39:09 AM, 10/15/2013 8:39:11 AM
3,  a,  4/10/2015 12:05:00 PM
];

...

More important, in your situation the time intervals could be longer than one working day, which was not supported by the expressions in the chart.  I've made changes in the expressions to support this - see attached.
And, a minor change - you prevously asked about the "14" - to make clearer I repalced with a variable that holds the amount of non-working hours per day: WHperDay.