# 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

#1
May 31, 2017 at 05:46:36
 with these you should be able to figure it outenter them in a worksheetA1= loginA2= 08:00A3= diffrenceA4= =C2-A2C1= LogoutC2= 18:00C3= 12 hours or moreC4= =IF(A4>=TIMEVALUE("12:00");"yes";"no")C5= 10 hours or moreC6= =IF(A4>=TIMEVALUE("10:00");"yes";"no")C7= 8 hours or moreC8= =IF(A4>=TIMEVALUE("08:00");"yes";"no")C9= 6 hours or moreC10= =IF(A4>=TIMEVALUE("06:00");"yes";"no")Simple solutions are often the bestmessage 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)*24In 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.MIKEmessage 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:00y denotes time diff b/w login and logout >=11:00P denotes time diff b/w login and logout >=10:00PP denotes time diff b/w login and logout >=9:00P denotes time diff b/w login and logout =8:00S.NoE-Code Name InTime OutTime Difference Rating1 50123 Ram 08:00 18:00 10:00 P2 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:00Any 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?MIKEhttp://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?MIKEhttp://www.skeptic.com/

Report • 