Help with a formula

[Excelexcel86]

Hi guys someone very kindly managed to help me with the following formula =IF(MEDIAN(MOD(A1,1),TIME(6,0,0),TIME(14,30,0))=MOD(A1,1),"Y","N")

Now this works to an extent but my problem is instead of yes no I’ve I’ve changed the yes to B2-A2 this is because my cells contains dates and times in that are always different so if the two times in cell A and b are in between 06.00 and 14.30 it works but if cell b contains a different date the time formula don’t work as it goes to whatever time is in that cell and outputs the value below is an example of what is happening .so is there an And command I can use with this formula so it also looks to see if both dates are the same as well this would then work as both would be true

01/03/2023 06:30	01/03/2023 13.30	6.00	works fine
01/03/2023	01/03/2023 15:30	Over work hours	Works fine
02/03/2023 08:00	03/03/2023 07:00	23.00	Don’t work because it’s gone over to 06:00 in the morning it’s calculate over the day

 

[Excelexcel86]

I'm afraid "it didn't work" does not give me very much to go on.

Here is what it looks like at my end.

20230305 Time worked in Work Hours Excelexcel86.xlsx
	A	B	C	D	E
1	time and date entered	time exited	total item inside warehous hours	Total time outside of warehouse hours	Total Elapsed time calc For Checking
2	1/02/2023 7:00	1/02/2023 15:30	7.5	1	8.5
3	2/02/2023 8:30	3/02/2023 6:30	6.5	15.5	22
4	2/02/2023 8:30	5/02/2023 6:30	23.5	46.5	70
5	3/03/2023 7:30	4/03/2023 8:30	9.5	15.5	25
Time Between Work Hrs Expanded

Cell Formulas
Range	Formula
C2:C5	C2	=24*IF(INT(B2)=INT(A2), MEDIAN(MOD(B2,1),TIME(6,0,0),TIME(14,30,0)) -MEDIAN(MOD(A2,1),TIME(6,0,0),TIME(14,30,0)), IF(INT(B2)-INT(A2)>=1, MEDIAN(MOD(B2,1),TIME(6,0,0),TIME(14,30,0))-TIME(6,0,0) +TIME(14,30,0)-MEDIAN(MOD(A2,1),TIME(6,0,0),TIME(14,30,0)) +(( INT(B2)-INT(A2)-1)*(TIME(14,30,0)-TIME(6,0,0))),""))
D2:D5	D2	=(B2-A2)*24-C2
E2:E5	E2	=(B2-A2)*24

On the same day between 07.30 and 23.20 it is giving me 12.45 hrs instead of 7hrs

Hi sorry it does work I’ve ran into a problem tho one of the days the work time hours are only until 11.30 not 14.30 I really appreciate your help so far

 

[Alex Blakenburg]

I think that changes the question significantly and it would be better if you started a new thread. If you only ever spanned 2 days it wouldn't be too bad but we are allowing for it to span several days.
You might need a UDF (macro) to do the calculation.
I am assuming its a particular day of the week that is affected.

 

[Excelexcel86]

Hi yes it’s a Friday so that formula works but if it goes to 14.30 on a Friday it calculates as 4hrs if it came in at 10.30

 

[Alex Blakenburg]

I think you will need a new thread and be open to possibility of using a macro.