Forum Discussion

Jerry Pinto's avatar
Jerry Pinto
Copper Contributor
Feb 20, 2018

Date time punch formua

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
Formulas & Functions
Need Help

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 21, 2018

    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

    • Willy Lau's avatar
      Willy Lau
      Steel Contributor
      Feb 21, 2018

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

      • Jerry Pinto's avatar
        Jerry Pinto
        Copper Contributor
        Feb 22, 2018

        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!

  • Willy Lau's avatar
    Willy Lau
    Steel Contributor
    Feb 21, 2018

    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