Here is what I am thinking....
First, create a calculated field where you Convert Actual Login to DATETIME. We are going to use this in another calc, and converting it makes it easier to work with. I made the assumption that Actual Login is the time they are expected to arrive.
Actual Login Date Time =>
Next Create a calc that determines if the Employee was late more than 2 in a given month
Here is the Table Calc version. I could use an LoD, but it appears you have left out Employee data for confidentiality.
IF WINDOW_SUM(SUM(IF DATEPART('hour', [First In]) >= DATEPART('hour', [Actual Login DateTime])
AND DATEPART('minute', [First In]) > DATEPART('minute', [Actual Login DateTime])
ELSEIF ISNULL([First In])
END)) >= 2 THEN 'Deduct Pay' ELSE 'Good Attendance' END
You may need to play around with how this is being computed (Table Down/Pane down/etc) depending on your Viz Level of Detail. But this should get you started.
Let me know if this is on the right track, or if this is different that what you needed.
I approached this problem a little differently. I assume when you say subtract 2 strings, you are trying to see how many hours an employee worked on a specific day? And I feel like, when you say you want to bucket them categorically if they came in late twice in a month. Meaning if they logged on late, correct? I am not sure how you are determining that part though, because the login times are very different for different days in a month. A good example is February 2015. Some days they logged on at 9:30 PM, some days, 12 PM and some days 6 AM. So I am not sure how you are determining if someone came in late more than twice a month unless there is another assumption that you did not mention here.
That said, I made the strings of date datatype by doing exactly what Rody suggested.
Login Modified - DATETIME([Actual Login]) and another one for
Logout Modified - DATETIME([Actual L Logout])
You will see that Tableau defaults the date to 1/1/1900. Provided that the data source string input of login times and logout times are correct, I created a formula to add 1 day to the logout datetime if login datetime is > than the logout datetime. I did this because if the login time is 1/1/1900 9:30 PM and logout time is 1/1/1900 6:00 AM Tableau will give a difference of 15 hours because these times are on the same day. To avoid that issue, the formula:
Dateadd - Logout Time - IF datepart('hour', [Login Modified]) > datepart('hour', [Logout Modified]) then dateadd('day', 1, [Logout Modified]) else [Logout Modified] end
will add another day to the logout time to accurately get the difference in hours. So the logout time for 1/1/1900 6:00:00 AM becomes 1/2/1900 6:00:00 AM
Then you create the datediff formula between Dateadd - Logout Time and Login Modified
Datediff - datediff('hour', [Login Modified], [Dateadd - Logout Time])
attendence data t5wbx fie - PG.twbx 111.7 KB