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 id...
Formulas & Functions
Need Help
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 22, 2018

      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.