# Difference between Time Stamps

#### Spoorthi

##### New Member
I have found the difference between the below two datetime stamps and formatted in time format "37:30:55". I have excluded the non working hours and weekends and holidays in it. By using the below formula I got the result as 17:20:39, but when I calculate it manually, I get a different result 16:51:00 which is the correct one. Can anyone help me arrive at the correct result.. Your help will be highly appreciated.

Start Date=H13= 9/14/11 11:09 AM
End date=I13= 9/16/11 10:30 AM
Working hrs start time=M2= 9:30 AM
Working hrs end time=M3= 6:30 PM
Holiday List = N2:N4
Formula used: (NETWORKDAYS(H13,I13,\$N\$2:\$N\$4)-1)*(\$M\$3-\$M\$2)+IF(NETWORKDAYS(I13,I13,\$N\$2:\$N\$4),MEDIAN(MOD(I13,1),\$M\$3,\$M\$2),\$M\$3)-MEDIAN(NETWORKDAYS(H13,H13,\$N\$2:\$N\$4)*MOD(H13,1),\$M\$3,\$M\$2)
Result = 17:20:39

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
How do you get 16:51?

11:09 until 18:30 on 14th = 7:21
whole of 15th = 9:00
9:30 to 10:30 on 16th = 1:00

Total = 17:21

...give or take some seconds which i can't calculate because you haven't shown them.....

You are right Barry I was calculating it wrongly. But now I realised that I want to exclude the lunch hours which is 1 Hr a day. Will you please help me with it.

Your current formula allows start and end dates/times to be any time - is that what you need? If start and end times/dates can only be within business hours then you could simplify the formula.

To exclude luch breaks you also need to specify the time of the break.....would it also be possible for the start/end times/dates to be within lunch break?

Yes Barry, the start and end time can be anytime and they can be within the lunch break too.. which is from 1 PM to 2 PM

If you put the lunch break start and end times in M4 and M5 respectively then this formula should work for you

=(NETWORKDAYS(H13,I13,N\$2:N\$4)-1)*(M\$3-M\$2+M\$4-M\$5)+IF(NETWORKDAYS(I13,I13,N\$2:N\$4),MEDIAN(MOD(I13,1), M\$3,M\$2)-MEDIAN(MOD(I13,1), M\$5,M\$4),M\$3-M\$5)-IF(NETWORKDAYS(H13,H13,N\$2:N\$4),MEDIAN(MOD(H13,1), M\$3,M\$2)-MEDIAN(MOD(H13,1), M\$5,M\$4),M\$2-M\$4)

It works.... Thanks a lot for your help. You rock!!

Replies
2
Views
170
Replies
1
Views
581
Replies
6
Views
162
Replies
2
Views
114
Replies
0
Views
461

### Forum statistics

1,203,489
Messages
6,055,723
Members
444,814
Latest member
AutomateDifficulty

### 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.

### Which adblocker are you using?

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

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