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]

That’s great to know but how would the top formula work for all 4 columns do I need a if and command ?

 

[awoohaw]

Okay, lets not worry about date/time philosophy right at this moment. Maybe it will be more clear when you see the final result.

So, to help me get there... can you please tell me what you want as the end result. Try not to use excel formula language. Just say with the start date/time and the end date/time I want to see ___________________________. and give me some examples where the end time of day is before the start time of day (end is the next day). Additionally, if there are hour restrictions, like only between 6 am and 2 pm? lunch periods (are a minimum hours of work required before a lunch can be included?).

 

[Excelexcel86]

Ok the hours are between 06:00 and 14:30 no breaks needed or anything so I need to see if the time is between 06.30 and 14:30 and the date is the same see the the amount of hours in work ?

time and date entered	time exited	hours in area	Total hours
01/02/2023 06:00	01/02/2023 15:30	Should be 7.5 hours
02/02/2023 08:30	03/02/2023 09:30	should be should be 6 hours

 

[Alex Blakenburg]

I had a look at your earlier thread where you got the formula and I think everyone is struggling a bit with working out what you are trying to achieve.
Have a play with the below and see if is captures all your scenarios.
Note: the only difference between D and E is that D give you the result as a time value while E gives you the result in hours and decimal fraction of hours.

20230305 Time worked in Work Hours Excelexcel86.xlsx
	A	B	C	D	E
1	time and date entered	time exited	hours in area	Hours during work hours [h]:mm	Hours Decimal
2	1/02/2023 7:00	1/02/2023 15:30	Should be 7.5 hours	7:30	7.5
3	2/02/2023 8:30	3/02/2023 9:30	should be should be 6 hours	6:00	6
Time Between Work Hrs

Cell Formulas
Range	Formula
D2:D3	D2	=MEDIAN(B2, INT(A2)+TIME(6,0,0), INT(A2)+TIME(14,30,0)) -MEDIAN(A2, INT(A2)+TIME(6,0,0), INT(A2)+TIME(14,30,0))
E2:E3	E2	=(MEDIAN(B2, INT(A2)+TIME(6,0,0), INT(A2)+TIME(14,30,0)) -MEDIAN(A2, INT(A2)+TIME(6,0,0), INT(A2)+TIME(14,30,0))) *24

 

[Excelexcel86]

Hi thanks for your reply no this is still not working I just need a formula to do the following

Date and time entered warehouse	date and time exited warehouse	total item inside warehous hours	Total time outside of warehouse hours
02/03/2023 07:30	02/03/2023 13:30	Formula to work out if time inside warehoushours	Formula if was outside warehouse Hours
02/03/2023 08:30	03/03/2023 06.30	The formula you gave doesn’t work as it sees 6:30 As in time scale so gives 22 hours it should give 5 hours as I want the formula to see if time in column B is no greater than 14:30 and also the date the same between the 2 columns if both are true can then do B2 - A2

 

[Alex Blakenburg]

The formula I gave you calculates the 2nd scenario as 6 hrs.
The logic being that the Start date > the 6am minimum and less than the 14:30 maximum so use that start date.
The End Date is past the maximum of 14:30 so use the 14:30.
8:30-14:30 is 6 hours.

What do you want it to do ?
Do you want to add in the extra 30 mins on the next day ?

 

[awoohaw]

I'd like to try to say in words what I think you want.
1. the only hours that you want to consider in the count are the hours between 6am and 2:30 pm.
So, in order to count this there are three steps:
Assuming the difference between subsequent day(s) and current day is determined:
1. Count the hours on the first day. Subtract start time from (14:30 or End time) whichever is less.
2. Count the number of full days 6:00 am to 14:30, and multiply by 8.5 hours.
3. Count the hours on the last day from 6:00 am to the end time.
Sum (1,2,3) up.
is that correct?

 

[Excelexcel86]

Hi yes that is correct in what u have asked so would like to sum up all 3 also you was correct in adding the 30 mins onto the next day thanks for your help so far

 

[awoohaw]

Hi yes that is correct in what u have asked so would like to sum up all 3 also you was correct in adding the 30 mins onto the next day thanks for your help so far

was asleep. @Excelexcel86, You've had comments from more than one people here. Do i (@awoohaw ) have to do anything with the 30 miniutes you mention in the last post? I do not think that matters in my 3 step process, do you?

 

[awoohaw]

@Excelexcel86 , what do you want to do with the hours on the first day before 6 am? Are they not counted at all?
Or should that not happen since you only want to count the hours between 6am and 2:30pm?