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

Do you think SpaceX laying off some of its workforce will help it succeed?

Discuss in The Lounge

Poll History