Working hours between two date/times excluding Lunch and weekends, diff time on Friday

Taul

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
767
Office Version
  1. 2019
Platform
  1. Windows
Hi,
I need some assistance with calculating working hours between two date/time cells. The working hours are 8:30 to 17:30 and the lunch break is 12:30 to 1:45

The date/time value could be outside the working hours but I only want to count the period within the working hours.

I have an existing formula (from Barry Houdini) that calculates the working hours minus the lunch breaks and excludes weekends and it works with date times outside the working hours.

The formula in cell M2 is:-
Code: 
=(NETWORKDAYS(F2,G2,holiday)-1)*(EndTime-StartTime+StartLun-EndLun)+IF(NETWORKDAYS(G2,G2,holiday),MEDIAN(MOD(G2,1), EndTime,StartTime)-MEDIAN(MOD(G2,1), EndLun,StartLun),EndTime-EndLun)-IF(NETWORKDAYS(F2,F2,holiday),MEDIAN(MOD(F2,1), EndTime,StartTime)-MEDIAN(MOD(F2,1), EndLun,StartLun),StartTime-StartLun)

The date/time stamps are in cells:-
Start Date F2
End Date G2

I have Named Ranges for the date times and holidays used for the standard working week:-
StartTime
StartLun
EndLun
EndTime
Holiday (Range of 15 cells)

BUT … the working hours are fixed for Monday to Friday.
I would like to incorporate different working hours for Friday (8:30 to 15:00) with the same lunch break as Monday to Thursday. Is this possible?

Would anyone be able to assist in modifying the existing formula or perhaps come up with a different approach that would allow different working hours on a Friday?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
[TABLE="width: 995"]
<colgroup><col><col><col span="5"><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row 5[/TD]
[TD="align: right"]15/01/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]7.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16/01/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]14.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17/01/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]21.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/01/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19/01/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]5.25[/TD]
[TD="align: right"]34.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22/01/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]41.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23/01/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]48.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]24/01/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]56[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25/01/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]63.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26/01/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]5.25[/TD]
[TD="align: right"]68.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29/01/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]75.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30/01/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]83[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31/01/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]90.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/02/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]97.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/02/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]5.25[/TD]
[TD="align: right"]102.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05/02/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]110[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]06/02/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]117.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07/02/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]124.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/02/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]131.75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]09/02/2019[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]5.25[/TD]
[TD="align: right"]137[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]working hours between[/TD]
[TD="align: right"]17/01/2019[/TD]
[TD][/TD]
[TD]row 33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]and[/TD]
[TD="align: right"]07/02/2019[/TD]
[TD][/TD]
[TD]row 34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]110.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 9"]=OFFSET($H$4,MATCH($I$34,$H$5:$H$24,0),4)-OFFSET($H$4,MATCH($I$33,$H$5:$H$24,0)-1,4)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi Oldbrewer,
Thank you for the reply, it looks like you have spent some time on this one.
Your formula accounts for different hours on a Friday but it does not account for lunch breaks or holidays. Also, it would (I think) require a very long list of dates from which to reference the formula.
Many thanks for the reply
Paul.
 
Upvote 0
the lunch break is already built in, check the working hours each day.........sat and sun are also accounted for....an employees holidays would need to be logged in another table and any "hours of holiday" would be deducted.......the dates them selves are generated from a formula - I could take it to 2020 in 5 seconds
 
Upvote 0
Thank you Oldbrewer,
I did not really grasp the full solution you offered, I think my brain must have been worn out as I spent the last few days looking for a work around to the problem. You are right, I could auto-generate the date list.

But there is still a problem, the cells containing the dates would be “date & time”.

So if the period I wanted to measure was quite short like:-
From [ 7/1/19 08:00] to [7/1/19 11:00] the formula would still deduct a lunch break for that period of time.

Your formula would work very well if the period of time crossed several whole days which included the lunch period.
I will keep playing with it and see if it can be adapted but I think this is a really difficult one to solve.
 
Upvote 0
Hi Oldbrewer,
Just to explain a little more, I’m trying to create a generic formula to handle working periods of time that would be better than the existing formula that I mentioned in the original post. The only flaw (that I can see) with the original formula is it cannot handle a shorter working day on a Friday.

The formula is used in a variety of workbooks for different uses, some workbooks measure absenteeism, and some measure holiday periods, some measure project working periods for KPI measurement and will probably be used for other projects in the future where time measurement is needed.
So I'm trying to imagine if someone is likely to log-off during a break time, probably not.

I will use your suggestions later today (RL is getting in the way at the moment) and see what I can come up with.
Many thanks
Paul.
 
Upvote 0
Hi,
Just for the sake of completeness and in case anyone else is looking for a similar solution. I have found an alternative approach. So I thought I would share it.

Basic solution is here (there is a downloadable spreadsheet available on the site)
https://pedrowave.blogspot.com/2013/03/hours-between-dates.html
The basic formula uses a range:- 0000111, seven numbers to represent the days of the week.
1= not active
0 = Active
So I have use three ranges
Range1 = Mon to Thursday (0000111)
Range2 = Fri (1111011)
Range3 = Lunch Break, Mon to Fri (0000011)

Each range is calculated in a separate column and then sum(Range1 + Range2 – Range3)
Holidays are in a separate named range and it works for date times within or outside of the working hours but only measures the time spent within the working hours.

Cheers
Paul.
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

J
  • Solved
Hours Between 2 Dates Excluding Non-Business Hours ONLY
Replies
7
Views
732
felixstraube
F
R
  • Solved
Adding Hours & Minutes to Dates & Times w/in Working Hours
2
Replies
11
Views
670
Asbestos_Jen
Asbestos_Jen
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
446
myall_blues
myall_blues
K
  • Question Question
Net work Hours Calculation
Replies
5
Views
392
Keyan_rrr
K
B
  • Solved
Issue with finding time difference taking account of weekends and workdays.
2
Replies
15
Views
463
Joe4
Joe4

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

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