Hi,
I have been reading some stuff here and tried it myself too but it seems that I can't get the logic. So my problem is that I am being asked to get 2 statuses for attendance. P - if a person logs in earlier,equal to, or within 30 minutes of his/her shift considering that he/she extended equal to the x minutes late within 30 minutes grace period otherwise it will be "L" for late.
Last edited by owen21; 06-22-2018 at 10:28 AM. Reason: attachements
OK 1st, your times are not really times, they are text looking like time. So excel cannot actually do calcs on them until you convert them.
2nd, is that 30 mins either way of schedule?
3rd, if they clock in 30 mins early, can they clock out 30 mins early without penalty?
4th, if they clock in 30 mins early, can they clock out 30 mins LATE without penalty?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Originally Posted by FDibbins
Hi FDbbins,
1.I should format them as time . I will read some threads to fix this format issue.
2. I am just computing the late of the person from their scheduled Time in
3. If they clock in early they cant log out early.
It's like a grace period of 30 minutes after their schedule(example, 9PM and I arrived at 9:25, if i extend my shift equals to the minutes that I am late that does not exceed the grace period of 30 minutes then I wouldn't be tag as late. If I log out less than the late minutes then I will be late. I hope I have made it clearer.
Hi, These formulas use a combination of LEFT() and RIGHT() which you have already used to extract the time elements from the text string and TIME() to convert them into and excel readable time format. They are not user friendly but do work. Also K column needs to be formatted as time.
PASTE IN J2 and drag down
PASTE IN K2 and drag down
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by Be77amy; 06-24-2018 at 01:48 PM. Reason: additional info
I tried both of those formulas and they came back blank?...PASTE IN J2 and drag down
PASTE IN K2 and drag downPlease Login or Register to view this content.
Please Login or Register to view this content.
This my 1st start on this, let me know if Im going in the tight direction?
=IF(H2>TIMEVALUE(F2)+"0:30","L","P")
Did you drag the formulas down the column. They should only show something in the ones where there was a late. Have a look at the workbook I attached
Yup, copied them both all the way down
Hi,
If I understand correctly,
For Column J2 copied down:
=IF(F2+"0:30">=H2+0,IF(24-(F2-G2)<=24-(H2-I2),"P","L"),"L")
For Column K2 copied down:
=IF(J2="P","",(24-(F2-G2))-(24-(H2-I2)))
Column K2 down, Format Cells as Time, 24 hour format.
This works! I didn't thought of using the 24 hour period on the formula. Thank you to everyone who helped. I will dig deeper to have a better understanding when working with "TIME".
Sorry Owen, I've neglected this condition, please use the following updated formulas instead:
For Column J2 copied down:
=IF(AND(F2+"0:30">=H2+0,I2+0>=G2+0),IF(24-(F2-G2)<=24-(H2-I2),"P","L"),"L")
For Column K2 copied down:
=IF(J2="P","",IF(OR(H2+0>F2+"0:30",AND(H2+0<=F2+0,I2+0<G2+0)),ABS(G2-I2),ABS((24-(F2-G2))-(24-(H2-I2)))))
In the attached file, I changed I2 to show attendee left 1 minute early, so even he arrived 8 minutes early, he's still tagged "L" for 1 minute.
I've also changed random attendee's times to show different results under different circumstances for Column K (i.e. arrive late/left early, arrive early/left early, etc.)
See if this gives you the intended results for Column K.
What you need to understand about dates and times in excel is…
a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Sun 24 Jun 2018) is actually 43275
Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks