Feb 20 2018
12:12 PM
- last edited on
Jul 25 2018
11:07 AM
by
TechCommunityAP
Feb 20 2018
12:12 PM
- last edited on
Jul 25 2018
11:07 AM
by
TechCommunityAP
Good afternoon all,
Looking for assistance with building a formula. In short, i want it to search for any gaps between Column "B" and column "C". Additionally i would like to build a rule so identify "Starting before a specific time" and past a specific time. Below is an example of a download in its Basic from. As you can see i want to compare the date, to ensure there is no time gaps between clock out, and the following lines Clock in. This is one example of one employees time punches. Hoping to build a formula, so we can pull the data and apply the formula to highlight cells where the gap is, also highlight dates that start before 7:30 (for example) and after 4:00 in separate colors. Some of our staff have 5-10 punches on a single day, others have upto 30. Thank you for your time and efforts!
DATED | CLOCK_IN | CLOCK_OUT |
2018/02/12 | 07:30 AM | 09:00 AM |
2018/02/12 | 09:00 AM | 12:00 PM |
2018/02/12 | 12:00 PM | 12:30 PM |
2018/02/12 | 01:00 PM | 02:18 PM |
2018/02/12 | 02:18 PM | 04:00 PM |
2018/02/13 | 07:30 AM | 03:30 PM |
2018/02/14 | 07:24 AM | 03:24 PM |
2018/02/15 | 07:24 AM | 03:24 PM |
2018/02/15 | 03:24 PM | 04:12 PM |
2018/02/16 | 07:12 AM | 03:12 PM |
Feb 21 2018 12:21 AM - edited Feb 21 2018 12:34 AM
Assume your table starting from A1 (Header "DATED" at A1).
I am not sure how many rows you have in the worksheet. If the size is small, try
=Sheet1!$A$1:$A$301
=AGGREGATE(15,3,MATCH(Data,Sheet1!$A2,0)*ROW(Data),1)
=AGGREGATE(14,3,MATCH(Data,Sheet1!$A2,0)*ROW(Data),1)
=OFFSET(Sheet1!$A$2, FirstRow - 2, 0, LastRow - FirstRow + 1)
=SUMPRODUCT(N(DateData=OFFSET(DateData,1,0)),N(OFFSET(DateData,0,2)<>OFFSET(DateData,1,1)))
=$A$2:$C$301
If you have many records, the above approach will be slow. You may need to use helper columns.
=IF($A1=$A2,$D1,ROW($A2))
=IF($A2=$A3,$E3,ROW($A2))
=OFFSET(Sheet1!$A$2, Sheet1!$D2-2, 0, Sheet1!$E2-Sheet1!$D2+1)
=SUMPRODUCT(N(DateData2=OFFSET(DateData2,1,0)),N(OFFSET(DateData2,0,2)<>OFFSET(DateData2,1,1)))
=$A$2:$C$301
Feb 21 2018 12:49 AM
If highlight the cells conditional formatting rules for data structured as
could be
red (time gap)
=(OFFSET($B4,-1,0)=$B4)*(OFFSET($B4,-1,2)<>OFFSET($B4,0,1))
yellow (start before 07:30 AM)
=(OFFSET($B4,-1,0)<>$B4)*(OFFSET($B4,0,1)<$C$1)
and blue (start after 04:00 PM)
=(OFFSET($B4,-1,0)<>$B4)*(OFFSET($B4,0,1)>$D$1)
sample is attached
Feb 21 2018 01:00 AM
Thanks Sergei. I thought it is required to highlight in a block and missed 2 cases.
Feb 22 2018 07:18 AM
Thanks to both of you!
I will work with both these very useful tips and see how it goes, appreciate it. There are many column and rows as we pull the data per department per branch, for example, i pulled last weeks time and one department (6 employees) had 220 rows, and 46 columns. Your input will give a good starting point for sure! Eventual goal is to be able to pull the data, week by week to audit time and ensure consistency, will keep you posted and thanks again!
Feb 22 2018 08:11 AM
Sergei's solution can highlight those 3 cases that you mentioned, and it doesn't need any helper columns and quick. One thing is great that if there is a day with 1) earily check in, 2) some gaps in between of the records, and 3) late check in, Sergei's solution can highlight them at once.