Calculating number of days and hours worked within business hours

[Donigen]

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!

 

[konew1]

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

 

[Donigen]

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

 

[Canapone]

Hi,

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

	A	B	C	D
1	start	end	excel <2010	excel >2010
2	8/1/15 3:46 PM	12/1/15 5:09 PM	4	4

<tbody>
</tbody>

Foglio1​

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

<tbody>
</tbody>

Hope it helps

 

[Donigen]

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

 

[Canapone]

Hi,

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

Book1
	A	B	C
1	start	end
2	8/1/15 3:46 PM	12/1/15 5:09 PM	31:23
3	8/1/15 5:48 PM	8/1/15 7:00 PM	01:12
4	10/1/15 10:30 AM	17/1/15 10:30 AM	60:00
Foglio1

Cell Formulas
Range	Formula
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