Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Trying to set Status based on Punch Clock Times

I have a table showing Punch In Times and Punch Out Times and the related Shift Start Times.

 

I have added custom columns to calculate the difference between when an employee punches in vs their start time.

I'm trying to create a column to give the status based on the time difference - Early, On Time or Late.

 

Table Example:

The Punch In Status column is a custom column with the following DAX

(if([Punch In Time Diff]>=0 and [Punch In Time Diff]<=1) then "OnTime" else if([Punch In Time Diff]>=2) then "Early" else if([Punch In Time Diff]<=-2) then "Late" else ""))

 

And it only produces Error, as you can see. It should show me that any employee that clocks in more than 1 minute before shift start is Early, any after shift start is Late and anyone clocking in within 1 minute of shift start is OnTime. Obviously I'm doing something wrong, but I'm not certain what. Any help would be appreciated. I can then create a second custom column for the same results for End of Shift status.

Need Help
  • lkalawski's avatar
    lkalawski
    5 years ago

    Hi Anonymous 

    I think that problem is with the last parenthesis. Should be:

     

    (if([Punch In Time Diff]>=0 and [Punch In Time Diff]<=1) then "OnTime" else if([Punch In Time Diff]>=2) then "Early" else if([Punch In Time Diff]<=-2) then "Late" else "")

     

    Proud to be a Super User.
    If I helped, please accept the solution and give kudos! 
    Linkedin

     

4 Replies

  • lkalawski's avatar
    lkalawski
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    Hi Anonymous 

    I think that problem is with the last parenthesis. Should be:

     

    (if([Punch In Time Diff]>=0 and [Punch In Time Diff]<=1) then "OnTime" else if([Punch In Time Diff]>=2) then "Early" else if([Punch In Time Diff]<=-2) then "Late" else "")

     

    Proud to be a Super User.
    If I helped, please accept the solution and give kudos! 
    Linkedin

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks lkalawski . When I wrote it, it wanted that second closing paranthesis. However, simply deleting and recreating the custom column with your formula worked. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Anonymous 

    Use the below code

    SWITCH (TRUE(),
        [Punch In Time Diff]>=0 && [Punch In Time Diff]<=1, "OnTime",
        [Punch In Time Diff]>=2, "Early",
        [Punch In Time Diff]<=-2, "Late",
        blank())
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous I tried this in a new custom column, but got the error that SWITCH wasn't recognized? Not certain what I'm doing wrong with that. lkalawski 's solution worked, so I'm good. Thank you for the suggestion.