Hi Everyone, I have been looking to find a formula that can be used to show result between login and logout time difference and if function.

Example: If employee A logins at 8:00 hrs and logsout at 18:00 hrs, formula should show time difference and also should also show x if time difference is less than 12hr or Y if time difference is less than 10 hr or z if time diffrence is less than 8 hrs or p if less than 6 hrs.

with these you should be able to figure it out enter them in a worksheet

A1= login

A2= 08:00

A3= diffrence

A4= =C2-A2

C1= Logout

C2= 18:00

C3= 12 hours or more

C4= =IF(A4>=TIMEVALUE("12:00");"yes";"no")

C5= 10 hours or more

C6= =IF(A4>=TIMEVALUE("10:00");"yes";"no")

C7= 8 hours or more

C8= =IF(A4>=TIMEVALUE("08:00");"yes";"no")

C9= 6 hours or more

C10= =IF(A4>=TIMEVALUE("06:00");"yes";"no")

Simple solutions are often the best

message edited by hidde663

Here is an alternative solution. With your data like:

A B C D 1) Start Time End Time Total Hrs Code 2) 08:00 18:00 10.00 YIn cell C2 enter the formula:

=IF(A2>B2,B2+1-A2,B2-A2)*24

In cell D2 enter the formula:

=IF(C2<=6,"P",IF(C2<=8,"Z",IF(C2<=10,"Y",IF(C2<=12,"X",""))))

See how that works for you.

MIKE

message edited by mmcconaghy

Dear mmcconaghy , Thanks for the quick revert on this matter. However i tried as per below table but could come to the output. Attaching Excel sheet . Need to calculate employee total working hours and denoting it by X,Y,Z,P as per their overtime where:

x denotes time diff b/w login and logout >=12:00

y denotes time diff b/w login and logout >=11:00

P denotes time diff b/w login and logout >=10:00

PP denotes time diff b/w login and logout >=9:00

P denotes time diff b/w login and logout =8:00S.NoE-Code Name InTime OutTime Difference Rating

1 50123 Ram 08:00 18:00 10:00 P

2 50124 Shyam 18:00 08:00

3 50125 Ganshyam

4 50126 Naresh

5 50127 Ramesh

6 50128 Lokesh

7 50129 Ganesh

8 50130 Devesh

9 50131 NiveshI was trying if formula like: if (time diff>= 12:00,"x",if(timediff>=11:00,"y",if......)

Also need to calculate time diff when login time is 18:45 and logout is 9:00

Any help will be highly appreciated.

If I understand you correctly, the Code or Rating letters, X,Y,Z,P denote the

number of hours greater then 8 or the Overtime hours. Correct?MIKE

On your new code list you have two values that are the same: x denotes time diff b/w login and logout >=12:00 y denotes time diff b/w login and logout >=11:00 >> P denotes time diff b/w login and logout >=10:00 PP denotes time diff b/w login and logout >=9:00 >> P denotes time diff b/w login and logout =8:00Is this correct?

These are the Overtime values, correct?

MIKE

Ask Your Question

Weekly Poll