How to find time difference between two rows of one column in excel sheet?

6,276

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)
Share:
6,276

Related videos on Youtube

Mrutyunjaya Mohapatra
Author by

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, 2022

Comments

  • Mrutyunjaya Mohapatra
    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
      DavidPostill almost 9 years
      What have you tried so far? Please read How do I ask a good question?.
    • Dave
      Dave almost 9 years
      Edit 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
      Mrutyunjaya Mohapatra almost 9 years
      Please find the more clear picture of the question. The snapshot is a screenshot for excel sheet.