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

 

[muhammad susanto]

my target still in col B2 down with this rules
anytime before 06:00 - is late
anytime between 7:15 and 17:00 is overtime
and
if a single rows/line is late

note:
the rows not always couple/pairs

 

[etaf]

=IF(COUNTIF($C$2:$C$100,C2)=1,"Late",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")))

names need to be unique

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

Cell Formulas
Range	Formula
D2:D9	D2	=IF(COUNTIF($C$2:$C$100,C2)=1,"Late",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, thank you very much.

 

[etaf]

you are welcome