Display value for multiple If conditions

[mvillanueva14]

Hi,

I have received help from all of you before and I hope you can still help this time. Thank you in advance. I am trying to create an attendance tracker system for a small start-up account until our web team is able to develop a program for us.
We have two shifts, 700-1600 and 1100-2000. I wanted the STATUS column to display "LATE" if I input a time later than 700 and 1100 (701 = 1 minute late, 1101 = 1 minute late). When I use my own formula though, anything larger than 700 is displaying late...but technically those who are coming in at 1100 are on time.

Also, I wanted the number of minutes to be displayed in Column E. I cannot enter a value larger than 1600 and 2000 in the log out column since that will affect the actual number of minutes the employee was late. (I only used conditional formatting and made a rule that the cell will turn red if I enter a value between 1601-1800, and any value larger than 2000 - sort of a visual reminder only).

As for the "Undertime" column, I just want it to display UT if the employee logs out before his time (1559 and earlier, also 1959 and earlier). As for the number of undertime minutes, I also want it displayed in Column E. Basically, Column F is for the number of minutes lost during working hours.

I hope you can all help me, I appreciate it.

This is how I want my file to look like.

A B C D E F
1NAME LOG IN LOG OUT STATUS # OF MINUTES UNDERTIME
2EMP1 700 1600
3EMP2 701 1600 LATE 1
4EMP3 701 1559 LATE 2 UT
5EMP4 1100 2000
6EMP5 1101 2000 LATE 1
7EMP6 1101 1959 LATE 2 UT

 

[etaf]

thats going to be a bit difficult based just on the time
say the starttime was 12:00 - you have no way of knowing if this someone on the 7-4pm shift 5 hours late
or someone on the 11-8pm shift 1 hour late
equally
if someone starts at 10:30
you have no idea if that person is 30 minutes early for the 11-8pm shift or if they are 3.5hrs late for the 7-4pm shift

all you know is that if someone starts after 4pm then they would be on the later shift

you could possibly use that with the logout time and make some assumptions and test both login and logout
so if logout is say +or- 15mins of 16:00 - then you can assumes its the early shift
BUT what are the norms for login and logout ?

 

[mvillanueva14]

Thank you for your reply. Yes, I figured as much...it will be difficult...No one really comes in too early or too late. Say for the 700-1600 shift, the latest time an employee will come is around 830. Same with the 1100 shift, the latest en employee will come is around 1230.

I was thinking of using an extra cell/column/row to display 700, 1100, 1600, and 2000 so I can use a formula that will use this extra cell, instead of including everything in the same cell. But I don't really know how.

 

[etaf]

you could probably use a countifs() in a IF statement

countifs ( starttime , >6:30 , starttime, < 08:00 , endtime , >15:30 , endtime , < 16:30 ) if that =1 then its true and so its an early shift

then you could calculate the time -
BUT again you need to know those thresholds

i guess its more likely , they will leave at the shift time
so perhaps just test the endtime
if its between 15:30 - 16:30 - then early shift
if its between 19:30 - 20:30 - then late shift
then depending if that time is meet , you could test the starttime against the expected starttime
if that makes sense

would that work?