1

I am working on attendance in a google sheet where I maintain hourly attendance. I record attendance for 5 hours everyday. In column B9:B40 I have a list of names. In Row F3:3 I have the dates. From F3:J3, 19-Jun-2024, from K3:O3, 20-Jun-2024 and so on. In F9:DZ I have marked 'A' for absent and 'P'. I am looking to filter the names with date and hours where 'A' is marked. I am also alright to enter a date manually and get the names with total number of hours and / or the hour number. I have uploaded an image for easy understanding.

enter image description here

Thank you for any help.

=FILTER(B9:B,F3:3=DATE(),F9:DZ="A")
Improve this question
0

2 Answers 2

Reset to default
0

use:

=INDEX(SPLIT(TOCOL(IF(F9:DZ40="A"; B9:B40&"×"&F3:DZ3&"×"&F4:DZ4; ); 1); "×"))

enter image description here

and format 2nd column as Date

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Just as the previous one, this one also works just as fine. Thank you so much.
0

You may try this for a list of Roll + date + day who are marked A within the range F9:DZ40:

=query(arrayformula(split(tocol(if(F9:DZ40="A",B9:B40&"|"&F3:DZ3&"|"&F4:DZ4,),1),"|")),"format Col2 'dd-mmm-yyy'")
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.