Formula for attendance sheet

[mayoub]

Hi Guys,

I have the following attendance sheet report, i want to add in the comments section a display error when someone punch in and doesn't punch out, the issue is that i have multiple columns with time in and time out since they go for breaks during the working hours, so i just want to display the message "Missing punch out" whenever an employee forgets to punch out during anytime from a single day

Emp No.

AC-No.

No.

Date

Clock In 1

Clock Out 1

Work Time 1

Clock In 2

Clock Out 2

Work Time 2

Clock In 3

Clock Out 3

Work Time 3

Clock In 4

Clock Out 4

Work Time 4

Clock In 5

Clock Out 5

Work Time 5

Break Time

Total in time

OT

Under Time

Absent

Comments

0:00​

0:00​

0:00​

16:00​

17:44​

1:43​

17:59​

22:00​

4:00​

00:17:00

5:44​

1:15​

0:00​

0:00​

0:00​

9:30​

13:03​

3:33​

00:00:00

3:33​

3:26​

0:00​

0:00​

0:00​

15:58​

19:13​

3:15​

19:19​

22:00​

2:40​

00:07:00

5:56​

1:03​

0:00​

0:00​

0:00​

15:53​

22:00​

6:06​

00:01:00

6:06​

0:53​

0:00​

0:00​

0:00​

14:08​

15:08​

1:00​

15:10​

00:02:00

TRUE​

What formula should i use in Comments?

Thanks a lot,

 

[gaz_chops]

Maybe

=IF(COUNT(E2,H2,K2,N2,Q2)=COUNT(F2,I2,L2,O2,R2),"", "Missing punch out" )

 

[Trevor G]

Assuming the table starts in Cell A1. Then in the comments cell add in this formula:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	p No.	AC-No.	No.	Date	Clock In 1	Clock Out 1	Work Time 1	Clock In 2	Clock Out 2	Work Time 2	Clock In 3	Clock Out 3	Work Time 3	Clock In 4	Clock Out 4	Work Time 4	Clock In 5	Clock Out 5	Work Time 5	Break Time	Total in time	OT	Under Time	Absent	Comments
2	0:00	0:00		0:00	16:00	17:44	1:43	17:59	22:00	4:00		1			1			1		00:17:00	5:44		1:15
3	0:00	0:00		0:00	9:30	13:03	3:33													00:00:00	3:33		3:26
4	0:00	0:00		0:00	15:58	19:13	3:15	19:19	22:00	2:40										00:07:00	5:56		1:03
5	0:00	0:00		0:00	15:53	22:00	6:06													00:01:00	6:06		0:53
6	0:00	0:00		0:00	14:08	15:08	1:00	15:10												00:02:00				TRUE
Sheet1

Cell Formulas
Range	Formula
Y2	Y2	=IF(F2="","Missing punch out",IF(I2="","Missing punch out",IF(L2="","Missing punch out",IF(O2="","Missing punch out",IF(R2="","Missing punch out","")))))

 

[mayoub]

Thanks a lot, works perfectly

 

[gaz_chops]

Assuming the table starts in Cell A1. Then in the comments cell add in this formula:

This will return "Missing Punch Out" regardless of there being no punch in time!

 

[Trevor G]

The op has asked for Punch Out not Punch In. P.S I like your formula.

 

[gaz_chops]

The op has asked for Punch Out not Punch In. P.S I like your formula.

Thanks.

You might of missed this bit "when someone punch in and doesn't punch out"

 

[Trevor G]

Ah yes