Need help in excel for calculating time diff and if function

May 31, 2017 at 05:07:59
Specs: Windows 7, 2GB
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.


See More: Need help in excel for calculating time diff and if function

Report •

#1
May 31, 2017 at 05:46:36
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


Report •

#2
May 31, 2017 at 16:38:59
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        Y

In 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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#3
May 31, 2017 at 20:19:38
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:00

S.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 Nivesh

I 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.


Report •

Related Solutions

#4
June 1, 2017 at 04:08:10
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

http://www.skeptic.com/


Report •

#5
June 1, 2017 at 05:04:52
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:00

Is this correct?

These are the Overtime values, correct?

MIKE

http://www.skeptic.com/


Report •

Ask Question