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

 

[awoohaw]

Is your question if the person has worked overtime/how much overtime?
or just whether the person has started between 0600 and 1430?
My suggestion would be to take a comparison between start day and end day and add 1 to the end time component and subtract the start time component.
or just make sure that the data is an accurate date/time value, then the subtraction of start from end takes days into consideration.

 

[Excelexcel86]

What you mean correct date/time value

 

[awoohaw]

Date and time would be an integer and the decimal portion of the day consistent in BOTH columns.
Either Pure dates (no decimal) in one column with and a pure time (no integer) in another column for both start and end dates (so 4 columns).

 

[awoohaw]

Cells can be formated to look like just a time or just a date, but are in fact a date/time value.
This may help:

Book1
	A	B	C	D
5	SAMEVALUE	SAMEVALUE	Difference
6	44989.65417	44990	0.345833333
7	2023-03-04 15:42	2023-03-05 00:00	1900-01-00 08:18
8	2023-03-04	2023-03-05	1900-01-00	It may look like a one day difference by looking at the dates, but you see that it is not
9	15:42:00	00:00:00	08:18:00
Sheet7

Cell Formulas
Range	Formula
C6:C9	C6	=B6-A6
A7:B9	A7	=A$6

 

[Excelexcel86]

Yes but how would that formula work above for 4 columns if it’s just looking at A and B ? You mean like this in the columns

03/03/2023

06:30

03/03/2023

16:30

 

[awoohaw]

this is a mixed reference of date/time and time only. the last row is why it is so dangerous:

Book1
	A	B	C	D
12	44989.65417	0.65417
13	2023-03-04 15:42	1900-01-00 15:42
14	2023-03-04	1900-01-00
15	15:42:00	15:42:00	#############	<<< the difference is 123 years in the past and excel doesn't like that
16			-44989	the general format of the calculation in row 15
Sheet7

Cell Formulas
Range	Formula
A13:B15	A13	=A$12
C15	C15	=B15-A15
C16	C16	=B15-A15

 

[Excelexcel86]

Hi sorry but you have lost me I’m new to excel my cells are like in the first two cells that’s what I’m asking is there a formula for my cells to check if the dates are the same that goes with my formula at the top of my question

 

[awoohaw]

OK. just whenever you see dates acting funny change the format of the cells to GENERAL and see if they have both integers and decimals, only decimals,, or only integers. As long as your cells are consistent you will be good to go.

 

[awoohaw]

Yes but how would that formula work above for 4 columns if it’s just looking at A and B ? You mean like this in the columns

03/03/2023

06:30

03/03/2023

16:30

i did not see this. As long as columns A & C are both date values (no decimal portion) and B & D are time values (no integer portion) you have the foundation for accurate formulas.