Get work hours between dates and times

[osalman]

Dear all,
Can you please help me to calculate the working house for the below example?
1.Saturday & Sunday off.
2.Working hours from 07:30 AM to 15:30 PM (Monday to Thursday).
3.Friday from 07:30 AM to 12:00 PM.
4.Excluding holidays.
Many thanks in advance.

Start date (DD-MM-YYYY HH:MM)	End date (DD-MM-YYYY HH:MM)	Working hours
09-03-2022 09:45 AM	14-03-2022 10:25 AM

 

[jtakw]

Hi,

You have provided no information regarding Holidays, you can add them in the formula.
Assuming Start and End Dates will Not be on Saturday, Sunday or Holiday.

This is what I came up with:

Book3.xlsx
	A	B	C	D
1	Start date (DD-MM-YYYY HH:MM)	End date (DD-MM-YYYY HH:MM)	Working hours
2	3/9/2022 9:45	3/14/2022 10:25	23.33
3			23 Hours 20 Mins	<Custom Format [h] "Hours" mm "Mins"
Sheet3

Cell Formulas
Range	Formula
C2	C2	=IF(WEEKDAY(A2,2)<5,15.5/24-MOD(A2,1),0.5-MOD(A2,1))*24+NETWORKDAYS.INTL(A2+1,B2-1,"0000111")*8+NETWORKDAYS.INTL(A2+1,B2-1,"1111011")*4.5+IF(WEEKDAY(B2,2)<5,15.5/24-MOD(B2,1),0.5-MOD(B2,1))*24
C3	C3	=(IF(WEEKDAY(A2,2)<5,15.5/24-MOD(A2,1),0.5-MOD(A2,1))*24+NETWORKDAYS.INTL(A2+1,B2-1,"0000111")*8+NETWORKDAYS.INTL(A2+1,B2-1,"1111011")*4.5+IF(WEEKDAY(B2,2)<5,15.5/24-MOD(B2,1),0.5-MOD(B2,1))*24)/24

 

[osalman]

Hi jtakw,

Thank you so much for the support.
please what will be the cell format for C2.
Many thanks in advance.

You have provided no information regarding Holidays, you can add them in the formula.
Assuming Start and End Dates will Not be on Saturday, Sunday or Holiday.

This is what I came up with:

Book3.xlsx
	A	B	C	D
1	Start date (DD-MM-YYYY HH:MM)	End date (DD-MM-YYYY HH:MM)	Working hours
2	3/9/2022 9:45	3/14/2022 10:25	23.33
3			23 Hours 20 Mins	<Custom Format [h] "Hours" mm "Mins"
Sheet3

Cell Formulas
Range	Formula
C2	C2	=IF(WEEKDAY(A2,2)<5,15.5/24-MOD(A2,1),0.5-MOD(A2,1))*24+NETWORKDAYS.INTL(A2+1,B2-1,"0000111")*8+NETWORKDAYS.INTL(A2+1,B2-1,"1111011")*4.5+IF(WEEKDAY(B2,2)<5,15.5/24-MOD(B2,1),0.5-MOD(B2,1))*24
C3	C3	=(IF(WEEKDAY(A2,2)<5,15.5/24-MOD(A2,1),0.5-MOD(A2,1))*24+NETWORKDAYS.INTL(A2+1,B2-1,"0000111")*8+NETWORKDAYS.INTL(A2+1,B2-1,"1111011")*4.5+IF(WEEKDAY(B2,2)<5,15.5/24-MOD(B2,1),0.5-MOD(B2,1))*24)/24

 

[jtakw]

Thank you so much for the support.
please what will be the cell format for C2.
Many thanks in advance.

No custom format for C2, just use either C2 formula (as-is) or C3 formula (with Custom format).

 

[osalman]

Assuming Start and End Dates will Not be on Saturday, Sunday or Holiday.

Hi,
Please we have to consider in the formulas that customer can use the system during the weekends , holidays and after the working hours.
Holidays are in the attached excel sheet with some data.
Many thanks in advance.

Test.xlsx
	D	E	F
1	Created On dd/mm/yyyy hh:mm dddd	End on dd/mm/yyyy hh:mm dddd	Working Hours
2	11/03/2022 20:02 Friday	12/03/2022 21:40 Saturday
3	11/03/2022 11:50 Friday	12/03/2022 21:40 Saturday
4	11/03/2022 11:18 Friday	12/03/2022 21:40 Saturday
5	10/03/2022 15:08 Thursday	12/03/2022 21:40 Saturday
6	10/03/2022 14:24 Thursday	12/03/2022 21:40 Saturday
7	09/03/2022 17:27 Wednesday	12/03/2022 21:40 Saturday
8	09/03/2022 13:33 Wednesday	12/03/2022 21:40 Saturday
9	09/03/2022 7:57 Wednesday	12/03/2022 21:40 Saturday
10	08/03/2022 15:07 Tuesday	12/03/2022 21:40 Saturday
11	08/03/2022 11:30 Tuesday	12/03/2022 21:40 Saturday
12	07/03/2022 9:49 Monday	12/03/2022 21:40 Saturday
13	04/03/2022 9:52 Friday	12/03/2022 21:40 Saturday
14	01/03/2022 8:57 Tuesday	12/03/2022 21:40 Saturday
15	25/02/2022 10:09 Friday	12/03/2022 21:40 Saturday
16	22/02/2022 15:04 Tuesday	12/03/2022 21:40 Saturday
17	10/02/2022 10:36 Thursday	12/03/2022 21:40 Saturday
18
19	Holidays dd/mm/yyyy
20	Dates
21	01/01/2022
22	30/04/2022
23	01/05/2022
24	02/05/2022
25	09/07/2022
26	10/07/2022
27	11/07/2022
28	31/07/2022
29	09/10/2022
30	01/12/2022
31	02/12/2022
Test

Cells with Data Validation
Cell	Allow	Criteria
D2:D31	Date	>=01/01/1900

 

[jtakw]

Hi,
Please we have to consider in the formulas that customer can use the system during the weekends , holidays and after the working hours.
Holidays are in the attached excel sheet with some data.
Many thanks in advance.

Please confirm what happens if your customer uses the system "during the weekends, holidays and after the working hours", and also Before working hours.
Do those hours get counted?
Or Free, not counted?

 

[osalman]

Please confirm what happens if your customer uses the system "during the weekends, holidays and after the working hours", and also Before working hours.
Do those hours get counted?
Or Free, not counted?

Not counted.
We need to count only the working hours (8 hours from Monday to Thursday and 4.5 hours in Friday) even if the customer use the system during the weekends, holidays and after the working hours.
Regards,