Calculating number of days and hours worked within business hours

Donigen

New Member
Hi! I need help on formula for excel on how to calculate total hours and days worked within the office hours (M-f 7:30am to 7:30PM) and Sat(7:30am to 1:30PM). computation will be based on start date&time and end date&time e.e Start = 1/8/15 3:46 PM and End = 1/12/15 5:09 PM. I tried so many formulas but none is working right. Thanks in advance!

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
If the start and end times are in the same day, then you make formula =(end_time - start_time) *24 you will get number of hours as the interger and the decimal part of the result will be part of an hour (Multiply that by 60 to get minutes

the problem is that the duration is not within the same day mostly data ranges to 2 or more days.

Hi,

in order to get number of working days you could use different formulas depending on Excel version you are using

ABCD
1startendexcel <2010excel >2010
28/1/15 3:46 PM12/1/15 5:09 PM44

<tbody>
</tbody>
Foglio1

Worksheet Formulas
CellFormula
C2=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(A2)&":"&INT(B2))),2)<7))
D2=NETWORKDAYS.INTL(A2,B2,11)

<tbody>
</tbody>

<tbody>
</tbody>

Hope it helps

Thank you for your help Canapone but I also need to count the totalk hours worked during the office hours given above

Hi,

the following formulas are a just first attempt, they're not for sure the best approach:

Book1
ABC
1startend
28/1/15 3:46 PM12/1/15 5:09 PM31:23
38/1/15 5:48 PM8/1/15 7:00 PM01:12
410/1/15 10:30 AM17/1/15 10:30 AM60:00
Foglio1
Cell Formulas
RangeFormula
C2=IF(INT(B2)>(A2),(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(A2+1)&":"&INT(B2-1))),2)<6))*"12:00"+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(A2+1)&":"&INT(B2-1))),2)=6))*"6:00")*(INT(B2)-INT(A2)>1)+IFERROR(("19:30"*(WEEKDAY(A2,2)<6)+"13:30"*(WEEKDAY(A2,2)=6))-MOD(A2,1),0)+IFERROR(MOD(B2,1)-"7:30",0)-"6:00"*(WEEKDAY(B2,2)=6),B2-A2)
C4=IF(INT(B4)>(A4),(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(A4+1)&":"&INT(B4-1))),2)<6))*"12:00"+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(A4+1)&":"&INT(B4-1))),2)=6))*"6:00")*(INT(B4)-INT(A4)>1)+IFERROR(("19:30"*(WEEKDAY(A4,2)<6)+"13:30"*(WEEKDAY(A4,2)=6))-MOD(A4,1),0)+IFERROR(MOD(B4,1)-"7:30",0)-"6:00"*(WEEKDAY(B4,2)=6),B4-A4)
B4=+A4+7

Waiting for better answers (using MEDIAN for istance).

I think it would be helpful to know which version you are using.

Regards

Last edited:

Replies
1
Views
207
Replies
1
Views
565
Replies
13
Views
564
Replies
1
Views
133
Replies
13
Views
639

1,203,203
Messages
6,054,099
Members
444,702
Latest member
patrickmg17

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.

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