Greetings,

I am trying to find the total time to resolution for HEAT tickets.
The information is extracted from the database via Crystal Reports and
into .xls format. I need a formula that will only calculate the time
to resolution between M-F 0700 (7AM) to 1800 (6PM) and excludes 1800
(6PM) to 0700 (7AM), weekends 1800 (6PM) Friday to 0700 (7AM) Monday
and holidays. I listed an example below.

Date Received Time Received Date Resolved
Time Resolved
2006-12-01 14:57:13 2006-12-04 10:25:20

Basically, the total time to resolution in the example above was
6:29:07 after excluding the weekend hours. Please let me know if
someone has a formula for this situation.

Thanks in advance.

Jason

wrote in message ups.com...
Greetings,

I am trying to find the total time to resolution for HEAT tickets.
The information is extracted from the database via Crystal Reports and
into .xls format. I need a formula that will only calculate the time
to resolution between M-F 0700 (7AM) to 1800 (6PM) and excludes 1800
(6PM) to 0700 (7AM), weekends 1800 (6PM) Friday to 0700 (7AM) Monday
and holidays. I listed an example below.

Date Received Time Received Date Resolved
Time Resolved
2006-12-01 14:57:13 2006-12-04 10:25:20

Basically, the total time to resolution in the example above was
6:29:07 after excluding the weekend hours. Please let me know if
someone has a formula for this situation.

Thanks in advance.

Jason


Jason, if we can assume that the Received, Resolved times are in the open period,
7:00-18:00, it's simple. But that's an important if.
With Date Received Time Received Date Resolved Time Resolved in A2:D2, try

=(NETWORKDAYS(A2,C2,)-1)*11/24+D2-B2

You can put a reference to extra holiday list as the 3. argument of NETWORKDAYS.

Result for your example is 6:28:07, are you sure of 6:29:07?

Hans.

On Jan 31, 7:38 pm, "Hans Terkelsen" <dk wrote:
wrote in oglegroups.com...
Greetings,

I am trying to find the total time to resolution for HEAT tickets.
The information is extracted from the database via Crystal Reports and
into .xls format. I need a formula that will only calculate the time
to resolution between M-F 0700 (7AM) to 1800 (6PM) and excludes 1800
(6PM) to 0700 (7AM), weekends 1800 (6PM) Friday to 0700 (7AM) Monday
and holidays. I listed an example below.

Date Received Time Received Date Resolved
Time Resolved
2006-12-01 14:57:13 2006-12-04 10:25:20

Basically, the total time to resolution in the example above was
6:29:07 after excluding the weekend hours. Please let me know if
someone has a formula for this situation.

Thanks in advance.

Jason

Jason, if we can assume that the Received, Resolved times are in the open period,
7:00-18:00, it's simple. But that's an important if.
With Date Received Time Received Date Resolved Time Resolved in A2:D2, try

=(NETWORKDAYS(A2,C2,)-1)*11/24+D2-B2

You can put a reference to extra holiday list as the 3. argument of NETWORKDAYS.

Result for your example is 6:28:07, are you sure of 6:29:07?

Hans.- Hide quoted text -

- Show quoted text -

Thanks Hans. But, how do I insert the holiday argument? Could you
please provide an example?

Thanks again, Jason.

wrote in message ups.com...
On Jan 31, 7:38 pm, "Hans Terkelsen" <dk wrote:
wrote in oglegroups.com...
Greetings,

I am trying to find the total time to resolution for HEAT tickets.
The information is extracted from the database via Crystal Reports and
into .xls format. I need a formula that will only calculate the time
to resolution between M-F 0700 (7AM) to 1800 (6PM) and excludes 1800
(6PM) to 0700 (7AM), weekends 1800 (6PM) Friday to 0700 (7AM) Monday
and holidays. I listed an example below.

Date Received Time Received Date Resolved
Time Resolved
2006-12-01 14:57:13 2006-12-04 10:25:20

Basically, the total time to resolution in the example above was
6:29:07 after excluding the weekend hours. Please let me know if
someone has a formula for this situation.

Thanks in advance.

Jason

Jason, if we can assume that the Received, Resolved times are in the open period,
7:00-18:00, it's simple. But that's an important if.
With Date Received Time Received Date Resolved Time Resolved in A2:D2, try

=(NETWORKDAYS(A2,C2,)-1)*11/24+D2-B2

You can put a reference to extra holiday list as the 3. argument of NETWORKDAYS.

Result for your example is 6:28:07, are you sure of 6:29:07?

Hans.- Hide quoted text -

- Show quoted text -

Thanks Hans. But, how do I insert the holiday argument? Could you
please provide an example?

Thanks again, Jason.


Jason, about the holidays, you could write the extra holidays, Easter and so on, on Sheet2, A1 and down.
Then the formula would be
=(NETWORKDAYS(A2,C2,Sheet2!A1:A100)-1)*11/24+D2-B2

If you don't want to write the holidays by hand, there should be a list on your pc if you have Outlook installed.
Find outlook.txt or outlook.hol, open with Notepad, locate the relevant dates, and copy to the Excel sheet.
Use Data/TextToColumns to split the date names from the dates.
But you still have to write special company holidays by hand.

Hans.