Date time punch formua

New Contributor

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
5 Replies

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

  1. Create a name in Name Manager, Data (Assume 300 records)
    =Sheet1!$A$1:$A$301
  2. Select cell A2, create a name in Name Manger, FirstRow
    =AGGREGATE(15,3,MATCH(Data,Sheet1!$A2,0)*ROW(Data),1)
  3. Select cell A2, create a name in Name Manger, LastRow
    =AGGREGATE(14,3,MATCH(Data,Sheet1!$A2,0)*ROW(Data),1)
  4. Select cell A2, create a name in Name Manger, DateData
    =OFFSET(Sheet1!$A$2, FirstRow - 2, 0, LastRow - FirstRow + 1)
  5. Select A2, use the following formula for Conditional Formatting Rule
    =SUMPRODUCT(N(DateData=OFFSET(DateData,1,0)),N(OFFSET(DateData,0,2)<>OFFSET(DateData,1,1)))
  6.  Change the Applies To of the CF rule to (Assume 300 records)
    =$A$2:$C$301

 

 If you have many records, the above approach will be slow.  You may need to use helper columns.

  1. In cell D2, type 
    =IF($A1=$A2,$D1,ROW($A2))
  2. Copy D2, paste to D3:D301 (Assume 300 records)
  3. In cell E2, type
    =IF($A2=$A3,$E3,ROW($A2))
  4. Copy E2, paste to E3:E301 (Assume 300 records)
  5. Select D2, create a name in Name Manager, DateData2
    =OFFSET(Sheet1!$A$2, Sheet1!$D2-2, 0, Sheet1!$E2-Sheet1!$D2+1)
  6. Select A2, use the following formula for Conditional Formatting Rule
    =SUMPRODUCT(N(DateData2=OFFSET(DateData2,1,0)),N(OFFSET(DateData2,0,2)<>OFFSET(DateData2,1,1)))
  7.  Change the Applies To of the CF rule to (Assume 300 records)
    =$A$2:$C$301
  8.  Hide column D and E 

If highlight the cells conditional formatting rules for data structured as

Date Time Punch.JPG

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

Thanks Sergei.  I thought it is required to highlight in a block and missed 2 cases.  

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!

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.