Complexed Time Difference Formula

[mahmed1]

Hi All

I have column where i have a start date time and another column with End Date Time

What i want to do is calculate the difference but only the difference between my start shift excluding Sundays

Weekday 08:00 - 21:00
Saturday 09:00 - 15:00

So i need to look at the difference between the 2 values factoring in those shift pattern

eg

Start Date Time…..End Time…..Diff (expected)
02/10/2023 09:00….02/10/2023 10:00…01:00
02/10/2023 18:00….03/10/2022 10:00…04:00
20/10/2023 19:00…23/10/2023. 11:00.. 11:00

20/10 breakdown
Friday 02:00
Sat 06:00
Mon 03:00

 

[duggie33]

Maybe something like this will help you with your solution...

Get work hours between dates and times

To calculate total work hours between two dates and times, you can use a formula based on the NETWORKDAYS function. In the example shown, E5 contains this formula: =(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),upper,lower),upper)...

exceljet.net

Doug

 

[mahmed1]

Thank you - i can give this a go
The only thing in my case id need to factor in is that i need to exclude sundays and have another upper and lower limit on a Saturday

I dont know how to add that in

 

[duggie33]

You may need to use the NETWORKDAYS.INTL for the weekend flexibility. I have not spent much time looking at it so I am not sure how the working hours calculations are done but I see them in the examples. Below is another link that may be helpful...

How to use the Excel NETWORKDAYS.INTL function - ExcelFind

The Excel NETWORKDAYS.INTL function returns the number of working days between two given dates. It can exclude custom weekend days and holidays.

excelfind.com

I also saw a link to this on the Exceljet site...

Calculate only working hours between two dates excluding weekends

How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends between the dates. Preferably the function...

www.mrexcel.com

If you come up with a solution, please share as this may help others.

Doug

 

[mahmed1]

Thank you

Ill try and see if Barrys Formula works

 

[mahmed1]

Hi

I tried to get it to work but for some reason i get unexpected results

I followed Barrys steps but cant seen to get right hours

=SUMPRODUCT(INT((WEEKDAY(B6-W$2:W$8)+INT(C6)-INT(B6))/7),Z$2:Z$8)-SUMPRODUCT((J$2:J$10>B6)*(J$2:J$10< C6),LOOKUP(WEEKDAY(J$2:J$10),W$2:Z$8))+MOD(C6,1)-MOD(B6,1)+LOOKUP(WEEKDAY(B6),W$2:X$8)-LOOKUP(WEEKDAY(C6),W$2:Y$8)

I tried using the networkdays.itl function and that worked only for a set start and end time

 

[duggie33]

I got it part way there with NETWORKDAYS.INTL put in the formula from Exceljet. Admittedly, I have not figured out how that works yet

I only have it with the weekday hours and not sure how to implement the Saturday hours. I am out of time for looking at this right now...

Book1
	A	B	C	D	E	F	G	H
1	Start	End	Diff	Part Way There			upper	21:00
2	2/10/2023 9:00	2/10/2023 10:00	01:00	01:00			lower	8:00
3	2/10/2023 18:00	3/10/2023 10:00	04:00	05:00
4	20/10/2023 19:00	23/10/2023 11:00	11:00	18:00
Sheet2

Cell Formulas
Range	Formula
D2:D4	D2	=(NETWORKDAYS.INTL(A2,B2,11)-1)*(upper-lower)+IF(NETWORKDAYS.INTL(B2,B2,11),MEDIAN(MOD(B2,1),upper,lower),upper)-MEDIAN(NETWORKDAYS.INTL(A2,A2,11)*MOD(A2,1),upper,lower)

Named Ranges
Name	Refers To	Cells
lower	=Sheet2!$H$2	D2:D4
upper	=Sheet2!$H$1	D2:D4

Hope this helps,

Doug

 

[mahmed1]

Thank you - hopefully someone can help to include Saturday hours too

 

[duggie33]

I have this working with the data you supplied. If the time difference exceeds 24 hours, it will return the number of hours over 24. For example, if result is 34 hours, it will show 10 hours. It probably does the same over 48 hours. I am tired and cannot think of a way to overcome this.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Start	End	Diff	Calculated			Day	Weekday	Workday	start	end
2	2/10/2023 9:00	2/10/2023 10:00	01:00	01:00			Sunday	1	FALSE	09:00	15:00
3	2/10/2023 18:00	3/10/2023 10:00	05:00	05:00			Monday	2	TRUE	08:00	21:00
4	20/10/2023 19:00	23/10/2023 11:00	11:00	11:00			Tuesday	3	TRUE	08:00	21:00
5							Wednesday	4	TRUE	08:00	21:00
6							Thursday	5	TRUE	08:00	21:00
7							Friday	6	TRUE	08:00	21:00
8							Saturday	7	TRUE	09:00	15:00
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=LET(start,A2,end,B2,sched,$H$2:$K$8,startday,ROUNDDOWN(start,0),endday,ROUNDDOWN(end,0),seq,SEQUENCE(endday-startday+1,,startday),wdays,WEEKDAY(seq),wrkdays,VLOOKUP(wdays,sched,2,0),starttimes,IFS(seq=MIN(seq),MOD(start,1),1,VLOOKUP(wdays,sched,3,0)),endtimes,IFS(seq=MAX(seq),MOD(end,1),1,VLOOKUP(wdays,sched,4,0)),SUM((endtimes-starttimes)*wrkdays))

You will have to create the work schedule table showing which days are workdays and the hours for each day.

Hope it works for you,

Doug

 

[mahmed1]

Thank you for all your help on this