Total time a ticket is open incluiding work hours but excluding weekends

**Geminiproject** · 04-03-2013, 05:31 AM

At first

Hi and in advance many thanks for help. I have been searching the forum for a solution but could not get it going

am using Excel 2010

Raw data

Created Time Modified Time Open time
01/03/2013 2:49 01/03/2013 7:08 0 days 4 hours 18 mins
01/03/2013 8:01 01/03/2013 18:55 0 days 10 hours 54 mins
01/03/2013 9:13 04/03/2013 13:36 3 days 4 hours 22 mins
01/03/2013 11:24 04/03/2013 18:16 3 days 6 hours 51 mins
01/03/2013 11:40 04/03/2013 14:29 3 days 2 hours 49 mins
01/03/2013 11:52 04/03/2013 10:49 2 days 22 hours 56 mins

I defined open time cell to be days/hours/minutes

Formula used: =(C2-B2)

This simply substracts open time from close time but the numbers dont make sense when a ticket is open several days and when there is weekends inbetween

help request:

I need a formula that calculates the correct time days/hours/minutes a ticket was open incluiding work hours but excluding weekends

Info:

Excel 2010
In Spain we use 24hr time scheme
Support coverage work time monday to friday 09:00 to 01:00 (in the morning) so 16 hours coverage by support daily

I attached an excel that shows my data

thanks for help

GP

**arlu1201** · 04-04-2013, 02:30 AM

Is this solved?

If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.

**Geminiproject** · 04-04-2013, 02:35 AM

Hi

i have to admit that this was my fault. Not solved i am afraid. Any chance you could give a helping hand?

Cheers

Alex

**dilipandey** · 04-04-2013, 05:00 AM

Hi Alex,

Try using below formula:-

Formula:

Please Login or Register  to view this content.

see attached:- Attachment 225608

Note:- you'll see some issue where weekends are falling in - between... check that and let me know your opinion.

Regards,
DILIPandey
<click on below * if this helps>

**Geminiproject** · 04-04-2013, 01:50 PM

Hi

at first many thanks for your help. In the majority of tickets there are weekends in between and we have 16 business hours monday to friday that should be reflected in the calculation. Any chance you could assist?

Regards

Alex

**daddylonglegs** · 04-04-2013, 02:23 PM

Hello Alex,

looking at your data I see that some tickets are created and then modified outside work hours, so that would be zero would it? You can use this formula to get total open hours within your 09:00 to 01:00 Monday to Friday window:

=(NETWORKDAYS(B2-"1:00",C2-"1:00")-1)*"16:00"+IF(NETWORKDAYS(C2-"1:00",C2-"1:00"),MAX(MOD(C2-"1:00",1),"8:00"),1)-MAX(NETWORKDAYS(B2-"1:00",B2-"1:00")*MOD(B2-"1:00",1),"8:00")

custom format result cell as [h]:mm

I could probably do it to show working days and hours, so row 15, instead of 38:09 working hours would be "2 days 6:09" (1 day being equal to 16 work hours) but that would have to be a text value, so I recommend the former, especially if you want to do further calculations (like summing, averaging etc.)