I have a massive list of employees who worked more than ten hours in a single shift.
A2 = John Smith
B2 = 7/15/18
C2 = 11:57 AM
D2 = 4:03 AM
My problem is that we use ADP and in our system, you can clock in 7 minutes before or after the start of your shift and still get credit for starting at that time. For example, if I am supposed to start at 8:00 AM, I can clock in anywhere between 7:53 AM and 8:07 AM to be given credit for starting at 8:00 AM. If I clock in at 7:52 AM, I will get paid starting at 7:45 AM. If I clock in at 8:08 AM, I will get paid starting at 8:15 AM.
What I am trying to do is create a formula that converts the times in C2 and D2 to their proper start times using the following:
1) If someone clocks in 7 minutes before or after the beginning of the hour (e.g., 7:53 AM - 8:07 AM) they get credit for starting at the beginning of the hour
A2 = John Smith
B2 = 7/15/18
C2 = 11:57 AM
D2 = 4:03 AM
My problem is that we use ADP and in our system, you can clock in 7 minutes before or after the start of your shift and still get credit for starting at that time. For example, if I am supposed to start at 8:00 AM, I can clock in anywhere between 7:53 AM and 8:07 AM to be given credit for starting at 8:00 AM. If I clock in at 7:52 AM, I will get paid starting at 7:45 AM. If I clock in at 8:08 AM, I will get paid starting at 8:15 AM.
What I am trying to do is create a formula that converts the times in C2 and D2 to their proper start times using the following:
1) If someone clocks in 7 minutes before or after the beginning of the hour (e.g., 7:53 AM - 8:07 AM) they get credit for starting at the beginning of the hour
2) If someone clocks in 7 minutes before or after quarter past the hour (e.g., 8:08 AM - 8:22 AM), they get credit for starting at quarter past the hour
3) If someone clocks in 7 minutes before or after the half hour (e.g., 8:23 AM - 8:37 AM) they get credit for starting at the half hour
3) If someone clocks in 7 minutes before or after the half hour (e.g., 8:23 AM - 8:37 AM) they get credit for starting at the half hour
4) If someone clocks in 7 minutes before or after 3 quarters past the hour (e.g., 8:38 AM - 8:52 AM) they get credit for starting at 3 quarters past the hour
How can I create a formula that converts all times to their proper start and stops predicated on the four rules above?<strike>
</strike>
How can I create a formula that converts all times to their proper start and stops predicated on the four rules above?<strike>
</strike>
Last edited:
