Count working hours between 2 dates excl weekends either with formula or function

[kirbyfez]

Hey all,

I know I can find the answer to my question on quite a few places but I've tried most I find and they just don't work.
Don't mind if its a formula or function though.

example of what I tried for example is:
=IF(NETWORKDAYS(A1,B1)=1,(MIN(Z1,MOD(B1,1))-MAX(Y1,MOD(A1,1))),(NETWORKDAYS(A1,B1)-2)*(600/1440)+(Z1-MAX(MOD($A$1,1),Y1))+MIN(MOD($B$1,1),Z1)-Y1)
A1 is start time
B1 is stop time
Y1 is start of work day (8:00)
Z1 is end of work day (17:00)
(Thinking it does not work because of the starttime and endtime is difference, that it covers 2 days in a way, 2pm-3am)

My issue is that our starting time is 2PM and end time is 3AM and the solutions I've found just do not work at all.

I was thinking either a function which does =TimeDiff(Date/timeStart, Date/timeEnd) or just link to a start date and end date in a formula.

I need the code/formula to exclude weekends completely.
Any ideas guys?

Hoping someone can help me out here!

Best regards
Christian

 

[skorpionkz]

could you clarify what time format are you using? as you saying in example start and stop time is in 24h format but then you saying some workers starting 2PM and finish 3AM and this is the 12h format


Alse is the Start Time and stop time are in format "DD/MM/YYYY hh:mm:ss"? Why you trying to use NETWORKDAYS?

 

[WarPigl3t]

I don't understand what you want.

 

[skorpionkz]

Try this formula IF you have date and time in start and stop time and no one works more than 24h

Excel 2010
	A	B	C
1	ST	ET	Time Difference
2	12/03/2014 08:00	12/03/2014 17:00	9.0
3	12/03/2014 14:00	13/03/2014 03:00	13.0
4	12/03/2014 08:30	12/03/2014 16:00	7.5
Sheet1

Cell Formulas
Range	Formula
C2	=IF(MOD(B2,1)>MOD(A2,1),MOD(B2,1)*24-MOD(A2,1)*24,(MOD(B2,1)+"24:00")*24-MOD(A2,1)*24)

 

[kirbyfez]

Hey,

Sorry I will explain it better.

I am trying to calculate an Response Time in our Incident Tracker and would like to see how long it takes for us to resolve the tickets.
At the moment we are logging the start time and end time and it just calculates how many hours difference it is.

I want to have it calculate the actual response time taking only our business hours into account which is Monday-Friday 2PM-3AM(No hours counted between 3am Saturday morning and 2pm Monday afternoon)

I tried the formula that posted but hmm, would that require that I post the starttime and endtime with the today's date in every row?

The way I have it setup now is that:
A1 B1 Response Time
Ticket Start Ticket End RESULT

I don't mind having 2pm and 3am somewhere hidden in the worksheet and link to that though, but would want to avoid the need of having it in every row.
I don't really care what format it would end up in but (H):mm:ss would probably be the best if possible.. If not a simple integer would work fine.

So far, thanks for the quick responses!

 

[skorpionkz]

so you actually </SPAN></SPAN>works until 3AM on saturday...

 

[kirbyfez]

Yea, sorry should have mentioned. Friday Start is 2pm and we end at 3am on the saturday morning.

 

[skorpionkz]

then NETWORKDAYS will not count time from 00:00 to 03:00 on Saturday... this makes it all more complex, give me couple hours, will try to make some formula</SPAN>

 

[kirbyfez]

then NETWORKDAYS will not count time from 00:00 to 03:00 on Saturday... this makes it all more complex, give me couple hours, will try to make some formula

For example.

StartTime
9/25/2014 4PM
EndTime
9/26/2014 6PM

Still the same working hours as always.

 

[skorpionkz]

EDIT:

Sorry doesn't work correctly... will try to fix