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

K

kirbyfez

Board Regular
Joined
May 15, 2012
Messages
51
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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?
 
Last edited:
Upvote 0
Try this formula IF you have date and time in start and stop time and no one works more than 24h


Excel 2010
ABC
1STETTime Difference
212/03/2014 08:0012/03/2014 17:009.0
312/03/2014 14:0013/03/2014 03:0013.0
412/03/2014 08:3012/03/2014 16:007.5
Sheet1
Cell Formulas
RangeFormula
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)
 
Upvote 0
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!
 
Upvote 0
Yea, sorry should have mentioned. Friday Start is 2pm and we end at 3am on the saturday morning.
 
Upvote 0
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>
 
Upvote 0
skorpionkz said:
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
Click to expand...

For example.

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

Still the same working hours as always.
 
Upvote 0
EDIT:

Sorry doesn't work correctly... will try to fix
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Elapsed day, hours, minutes minus weekends
Replies
6
Views
408
Scott Huish
Scott Huish
A
  • Solved
Production time calculation within the working day accounting for breaks
2
Replies
16
Views
688
felixstraube
F
F
  • Solved
Take date/time and calculate the time in between excluding weekends and including partial days (looking at full 24 hour days)
Replies
4
Views
255
freeb1893
F
J
  • Question
Formula for calculating business hours between 2 date/times
Replies
1
Views
1K
vladimiratanasiu
V
B
  • Solved
Sumifs Hours Worked Between Two Dates and Times with Multiple Criteria
2 3
Replies
24
Views
649
Fluff
Fluff

Forum statistics

Threads
1,215,011
Messages
6,122,677
Members
449,092
Latest member
tayo4dgacorbanget

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top