Formula to Determine Datetime With 3 Criteria Based on Same Name

[muhammad susanto]

hi all..

how to solve this problem:
this criteria:
Clock in:
06.00.00 till 07.15.00
Clockout
17.00.00 till 24.00.00
with option
"ok" or "late" or "overtime"

nov 2023 (version 1).xlsb
	A	B	C
1	date-time	expected result	name
2	01/11/2023 07:04:15	ok	Kiki
3	01/11/2023 17:19:41	ok	Kiki
4	02/11/2023 05:58:26	late	Sisca
5	02/11/2023 17:34:42	ok	Sisca
6	01/11/2023 07:07:25	ok	Darwin
7	01/11/2023 16:43:44	overtime	Darwin
8	02/11/2023 07:05:45		Jack
9	02/11/2023 17:23:44		Jack
10	04/11/2023 07:06:27		Milla
11	04/11/2023 17:36:17		Milla
Sheet1 (2)

any one help me out, greately appreciated..
my target in column B2 down

susant0

 

[etaf]

just need to check those time
so 6:00 till 7:15 is OK - but before 06:00 - its classed as late ??? , as shown 05:58:26 - Sisca
and then for
overtime - between 17:00 and 24:00 -

Darwin
16:43:44 - is shown as overtime - but thats before 17:00

do you treat as pairs - so 1st entry is clockin and 2nd entry clock out
what happens if they clockin on 1 day and clock out the next day - is that possible

no expected result for jack or milla - ????

 

[muhammad susanto]

hi..
if clock in before time 06.00.00 or after 07.15.00 is shown "late"
if clock out before time 17.00.00 or after 24.00 is shown "overtime"
if they clock in on 1 day and clock out the 1 day , if the next day is not possible

for they jack & milla (clock in or clock out) are shown "ok"

 

[etaf]

Ok, i think i understand

but how do they clock out over 24:00 , if they cannot work more than 1 day ???

so mayneed to rethink

will they always be sorted in pairs , clockin - always followed by clock out for same person

anytime before 06:00 - is late
anytime between 7:15 and 17:00 is overtime

so
=IF((A2-INT(A2))<TIMEVALUE("06:00:00"),"late",IF(AND((A2-INT(A2))>TIMEVALUE("07:15:00"),(A2-INT(A2))<TIMEVALUE("17:00:00")),"overtime","ok"))
should work

Book3
	A	B	C	D
1	date-time	expected result	name
2	07:04:15	ok	Kiki	ok
3	17:19:41	ok	Kiki	ok
4	05:58:26	late	Sisca	late
5	17:34:42	ok	Sisca	ok
6	07:07:25	ok	Darwin	ok
7	16:43:44	overtime	Darwin	overtime
8	07:05:45	ok	Jack	ok
9	17:23:44	ok	Jack	ok
10	07:06:27	ok	Milla	ok
11	17:36:17	ok	Milla	ok
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=IF((A2-INT(A2))<TIMEVALUE("06:00:00"),"late",IF(AND((A2-INT(A2))>TIMEVALUE("07:15:00"),(A2-INT(A2))<TIMEVALUE("17:00:00")),"overtime","ok"))

 

[muhammad susanto]

hi etaf...working well, thank you so much.

 

[etaf]

you are welcome

 

[muhammad susanto]

hi etaf, i think i missing about this:
sometimes person forget to clock in or clock out, so not always in pairs..
if the line/row only one row maybe clock in or clock out, the shown "late"
how to fix this problem?

 

[etaf]

can you provide a sample
i did flag

will they always be sorted in pairs , clockin - always followed by clock out for same person

so really need a sample of all possibilities and also the expected results

I also asked if the person would only appear once in the list - or is this an ongoing list with mutliple days and people

What do you want to show , if only in once - again give FULL examples please

 

[muhammad susanto]

hi etaf

nov 2023 dumai.xlsx
	A	B	C
1	date-time	expected result	name
2	03/11/2023 07:09	Late	Kiki
3	06/11/2023 07:10	ok	Sisca
4	06/11/2023 17:12	ok	Sisca
5	07/11/2023 17:36	Late	Jack
6	08/11/2023 07:10	Late	Milla
Sheet4

red font as a singe row/line with different person. the shown is "late"

 

[etaf]

ok, so what do you want we can use a countif - if they only ever supposed to appear twice in list