Forum Discussion
Multiple IF Condition and return value
Name ClockIn ClockOut HoursWorked Ryan 06:44 14:44 7:59 Ryan 07:04 14:21 7:17 Jerome 6:40 13:10 6:30 Jerome 7:05 15:35 8:30 Katie 07:20 14:23 7:03 Katie 6:46 13:11 6:25 Ariana 07:15 12:55 5...
Hi Anonymous
Another approach which I find it more feasible is to have a dimension table of the standard working times. Here is a sample file https://www.dropbox.com/t/Dd02knQPHCaqUbRYYou can also apply the same to the duty finish time. I guess your ultimate goal is to calculate the hours worked ignoring early clockin and late clockout. You can calculate both values and store them in variables and then calculate the difference directly without having to create unnecessary new columns.
Both measure and calculated column options are provided in the solution.
technolog
Super User
Super UserIF( OR( [Name] = "Ryan", [Name] = "Jerome" ),
IF( [ClockIn] < TIME( 6, 45 , 0) , TIME (6, 45, 0) , "AFTER 6:45" ),
IF ( [ClockIn] < TIME( 7, 0 , 0) , TIME (7, 0, 0) , "AFTER 7:00" )
)