Multiple IF Condition and return value

[Anonymous]

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:40
Ariana 06:58 15:09 8:11

 hello so i have this table, i want an if condition for employees as it goes,

ryan and jerome are my only 2 supervisors so if they arrive before 6:45, it should always be 6:45 but if they arrive after 6:45, let it be the time they arrived, similar for all the rest of my employees in this example, its ariana and katie,, but instead of 6:45 its 7:00,

so i want something like : if(name = "Ryan" and "Jerome" AND clock in is <=6:45 ) let it be 6:45, ELSE (time they arrive)) AND if(name = "all other employees" AND clock in is<= 7:00) let it be 7:00, ELSE(time they arrive)

[tamerj1 · Accepted solution]

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/Dd02knQPHCaqUbRY

You 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]

@Anonymous in this case you have a problem if you had added new employees in first table that you wouldn't have added in table "Working hours".

Be careful!

[technolog]

Also you can create this table in Google Spreadsheets and connect it with Power Apps.

Then add Power Apps to your dashboard.

[technolog]

IF( 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" )

)