Time Differnece Formula

skmittal1979
Posts: 7
Joined: Wed Sep 03, 2014 6:29 am

Time Differnece Formula

Hi,

Please suggest which formula should be used. Example if
In Time : 8:10 IST
Out Time: 18:20 IST
Shift Timing : 9:00 Hrs

Formula We can use: =Mod(Out Time-In Time,1) but it gives only positive Hrs means if a person have more then 9 hrs shift. I want to know the formula for which I can know if one person have less then 9 hrs shift in a day. Mode function gives only positive number if shift time is more then 9 hrs but not less then.

I hope i will get the resolution ASAP of this query.

Now I want to calculate OT hrs so please suggest how to calculate.

edupristine
Finance Junkie
Posts: 944
Joined: Wed Apr 09, 2014 6:28 am

Time Differnece Formula

First you need to convert it into time format by using this formula ==HOUR(Cell1)+MINUTE(Cell1)/60.
After doing this please change the cell format by pressing Ctrl+Shift+1.
Example:- (10:00-14.20-9)*60= -280 minutes.
Here is your minimum login time is 9:00 HRS and this time you are short of 28 minutes.

Hope this helps.