4 Replies Latest reply on Aug 26, 2016 10:25 AM by Simon Runc

# Late Punch Count

Hi Tableau Community,

I had this question a few months ago and need additional help - My objective is to find the number of days an employee punches in after a value in a parameter.

I was advised to use this calculation to determine the number of times an employee punched in after the value in the parameter.

IF

DATEDIFF('minute'

,[In Punch]

,DATETIME(STR(DATE([In Punch]))+' '+STR([Punch In Hour Limit])+':'+STR([Punch In Minute Limit]))

) < 0 THEN 'Late'

ELSE 'Early'

END

My issues is: When I replace the 'In punch' values with 'Out Punch' my results show that all punch outs are late.

IF

DATEDIFF('minute'

,[Out Punch]

,DATETIME(STR(DATE([Out Punch]))+' '+STR([Punch Out Hour Limit])+':'+STR([Punch Out Minute Limit]))

) < 0 THEN 'Late'

ELSE 'Early'

END

Out Punch is a date time dimension

Punch Out Hour Limit/Minute Limit are integer parameters

Please let me know if you have any ideas as to what I'm doing wrong.

Thank you!

Elizabeth

• ###### 1. Re: Late Punch Count

hi Elizabeth,

I've re-built your formula, so I can see what it was doing (and used the nifty drag and drop to check what each part is doing)...but it is just that you need the parameter hour in 24 hour format

When I set the time to 16 (so not 4) and 40...it shows me those who punched in before and after 16:40 fine.

...this seems too simple to be the solution, so if you let me know what behavior you are expecting I'm happy to take a look.

• ###### 2. Re: Late Punch Count

I think your parameter is assuming a.m.

So when you put 4:30 it thinks the morning.

Try adding 12 to the hour limit...

IF

DATEDIFF('minute'

,[Out Punch]

,DATETIME(STR(DATE([Out Punch]))+' '+STR([Punch Out Hour Limit] + 12)+':'+STR([Punch Out Minute Limit]))

) < 0 THEN 'Late'

ELSE 'Early'

END

• ###### 3. Re: Late Punch Count

Oh wow! I'm embarrassed I didn't catch on to that. Thank you both for your help

• ###### 4. Re: Late Punch Count

No problem...sometime you are just too close to a problem!! (done it myself many times!)...and you'd done the really complicated bit (breaking out and rebuilding the date/time from parameter) so well...so often the way!