0

im trying to make an automatic attendance sheet here are the details: -Data will be imported in the excel sheet (left side) -I want to automatically mark "p" or "a" a specific cell based on the data imported

enter image description here

PeterT
  • 8,232
  • 1
  • 17
  • 38
Jester Magdaraog
  • 1

1 Answers1

0

since you're trying to check several conditions at the same time (date, time, id) in your source data I'd recommend normalizing the time to the start of the hour,

START OF HOUR FUNCTION

then adding a unique value for each you could concatenate "DATE", "TIME", "ID" with some type of separator for example "-"

CONCAT FUNCTION

and then you could try with using a lookup function to look for that specific value,

LOOKUP FUNCTION

with an IF function so that if the value is returned you print p or if false print a.

IF FUNCTION

ROIZENGH
  • 31
  • 3
  • Actually, I have done a few developments regarding this question, i already formulated a the command for marking "p" and "a", Now the problem is the time constraints, another mark "l" for late. the problem im facing right now is how do i evaluate wether the time is within a certain range. Here is the link to the file im working on (https://docs.google.com/spreadsheets/d/1edOB2Jcxt0NCywZNj4YLiCcgG0LKnGFP/edit?usp=sharing&ouid=117782945819933885815&rtpof=true&sd=true) – Jester Magdaraog May 05 '22 at 14:57