How to find time difference between two rows of one column in excel sheet?
Add a column (G in my case) with this formula
in cell G2 (same for lower cell of the column and relative to their row)
=IF(C2="exit";G1+B2-B1;0)
Just sum the total (a pivot table will easily sum per date/user)
Assuming
- first date is on A2
- entry and exit are on the same day (if not, use a sum of date + entry as value in place of B2 and B1)
Related videos on Youtube
Mrutyunjaya Mohapatra
■ Currently working on .Net framework 3.5 ASP.Net, ADO.Net Entity framework, MS BizTalk Server 2006/2010, ASP.NET MVC 4.0, MS SQL Server 2008 R2/2012, LINQ, jQuery. ■ Hands on experience on ASP.Net, BizTalk, ADO.Net, C#, HTML, CSS JavaScript and SQL Server. ■ Academically astute individual with B. Tech. (Electronics & Communication Engineering) and MBA (HR-Marketing), possess deep passion and high energy level to pursue a successful career by blending skills & knowledge using creative instincts to attain goals of the organization. ■ Self-motivated, hardworking and goal-oriented with a high degree of flexibility, creativity, resourcefulness, commitment and optimism. ■ Focused & goal driven with a strong work ethic, continuously striving for improvement coupled with excellent administrative aptitude with an eye for detail & commitment to offer quality work. ■ Excellent time management skills with proven ability to work accurately and quickly prioritize, coordinate and consolidate tasks, whilst simultaneously managing the diverse range of function from multiple sources. Responsible & able individual ready to take up any challenge & go out of the way to surpass expectations.
Updated on September 18, 2022Comments
-
Mrutyunjaya Mohapatra about 1 year
I have an excel sheet containing the Entry and Exit time for users from bio metric software. I want to find how much time the user had stayed i.e. sum of difference between entry and exit times, but some time the user has to puch twice as the bio metric software doesn't accepts its login(like line 3 & 4 and 9 & 10 in the below shown table). In that case the difference should be done taking the last repeting Entry and last repeating Exit.
Please refer the snapshot below:
Date/Time Entry/Exit Badge Reader 7/10/2014 09:36:46 Entry 773821 BAN-IOS-2F-IBS-TS-IN NEW 7/10/2014 11:22:42 Exit 773821 BAN-IOS-2F-IBS-TS-OUT NEW 7/10/2014 11:27:34 Entry 773821 BAN-IOS-2F-IBS-TS-IN NEW 7/10/2014 11:27:42 Entry 773821 BAN-IOS-2F-IBS-TS-IN NEW 7/10/2014 12:59:24 Exit 773821 BAN-IOS-2F-IBS-TS-OUT NEW 7/10/2014 13:57:10 Entry 773821 BAN-IOS-2F-IBS-TS-IN NEW 7/10/2014 15:56:42 Exit 773821 BAN-IOS-2F-IBS-TS-OUT NEW 7/10/2014 16:24:19 Entry 773821 BAN-IOS-2F-IBS-TS-IN NEW 7/10/2014 17:25:56 Exit 773821 BAN-IOS-2F-IBS-TS-OUT NEW 7/10/2014 17:26:56 Exit 773821 BAN-IOS-2F-IBS-TS-OUT NEW
Please help in calculating the time the user has stayed in his desk from the data in the excel sheet.
-
DavidPostill almost 9 yearsWhat have you tried so far? Please read How do I ask a good question?.
-
Dave almost 9 yearsEdit your post. Highlight the data (using the mouse) then click on the code icon to format it. This will make it easier for us to read and help :) Please also provide the expected result from the above sample :)
-
Mrutyunjaya Mohapatra almost 9 yearsPlease find the more clear picture of the question. The snapshot is a screenshot for excel sheet.
-