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

 

[kirbyfez]

Been testing now and it seems to work great! thanks guy. Really appreciate the help and enjoyed seeing you guys explain to each other.. Geniuses at work for sure!
I probably would have been able to write a macro for it but meh would have been crap i think.. I'm pretty decent at VBA but I truly suck at formulas.. Thanks again guys!

 

[kirbyfez]

Bah, annoying.. Well, started using it today and all well.. then the others say... What if a ticket comes in outside our working hours?.. that wont work with the current formula.. Any idea what can be done? So if it comes in at say... 5am.. it should still work but not start counting until starting hours.

 

[barry houdini]

Hello Christian,

The very first formula I suggested will work, whether the start/end times are within the working hours or not, i.e. this one

=(NETWORKDAYS(A2-"3:00",B2-"3:00")-1)*MOD("3:00"-"14:00",1)+IF(NETWORKDAYS(B2-"3:00",B2-"3:00"),MAX(MOD(B2-"3:00",1),"11:00"),1)-MAX(NETWORKDAYS(A2-"3:00",A2-"3:00")*MOD(A2-"3:00",1),"11:00")