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

 

[Alex Blakenburg]

@awoohaw, I think the OP has confirmed that you are spot on with your 3 step process and the comment relating to the 30 mins refers to my post since your 3 step process will pick it up.
As to your reference to the time before 6 am on the first day, see the OPs post #15 where an additional column has come to light which will cover off the extra hours outside of the work hours (Column Heading - "Total time outside of warehouse hours", presumably total time difference minus inside warehouse hours)

 

[Excelexcel86]

If start time on the first day is before 6:00 don’t include them but if they go into the next day count them like below example

Start time	end time	Time inside warehouse hours	outside warehous hours
07.30 03/03/2023	08:30 04/03/2023	So here the time should be the 6 hours up until 14.30 then it goes into the next morning so 06.00 till 08.30 is an extra 3.5 hrs so total should be 9.30 hours	Here should have the total amount of hours including work hours so would be 25 hours

 

[Alex Blakenburg]

Give this a try.

20230305 Time worked in Work Hours Excelexcel86.xlsx
	A	B	C
1	time and date entered	time exited	Hours In Area Decimal
2	1/02/2023 7:00	1/02/2023 15:30	7.5
3	2/02/2023 8:30	3/02/2023 6:30	6.5
4	2/02/2023 8:30	5/02/2023 6:30	23.5
5	3/03/2023 7:30	4/03/2023 8:30	9.5
Time Between Work Hrs

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))),""))

 

[Excelexcel86]

Hi are all these separate formulas or if and commands as I was hoping to highlight all cells and go down each one and the formula covers all basis?

 

[Alex Blakenburg]

I don't understand the question.
It is a single formula that should cover the start and end dates being: on the same day or on the next day or several days later.
You put it on Row 2 and copy it down to all rows.

 

[Excelexcel86]

Ok but I needed a formula to calculate the total time including the time outside of work areas as well that’s y I was say is there a and command to use with this formula

 

[Alex Blakenburg]

I have logged off for the night.
Total time is (end datetime - start datetime)*24
Outside = Total time - Inside time as calculated by the formula I gave you.

 

[Excelexcel86]

Ok thanks for you help but I put all of that formula in and it didn’t work

 

[Alex Blakenburg]

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

 

[Excelexcel86]

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