+ Reply to Thread
Results 1 to 6 of 6

Calculate Elapsed Time Between Two Times

  1. #1
    Forum Contributor
    Join Date
    02-20-2011
    Location
    Candada
    MS-Off Ver
    Microsoft Office 365
    Posts
    174

    Calculate Elapsed Time Between Two Times

    Thank you every one in advance for your help and contribution to Excel Forum. I am trying to find out if there is a formulate that looks at column A (TTime) and checks to see if there are more than tow occurrences of the same date and to calculate elapsed time (in column I) between to times in a particular date only if there are more than two clock ins and clock outs. For example at the attached example workbook, on August 14, 2017 there are two clock ins and clock outs. The only way I can calculate the time elapsed from the first clock out and and second clock in is by subtracting the second clock in time from first clock out. Cell reference: G28-G27, which gives me a total of 0:19:37. Then add the total elapsed time at the bottom of column I. I am only interested in calculating the elapsed times if there are more than 2 occurrences of clock ins and clock outs. Also you will notice that on 11/13/2017, there are 3 clock ins and 3 clock outs. I'd like to calculate the elapsed time between these times and add them up.
    I would greatly appreciate your help and time.
    Attached Files Attached Files
    Last edited by Kimston; 03-07-2018 at 02:24 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,388

    Re: Calculate Elapsed Time Between Two Times

    Maybe this in I2 copied down:

    =IF(COUNTIF($A$2:A2,[@Tdate])=3,[@Ttime]-G1,"")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    02-20-2011
    Location
    Candada
    MS-Off Ver
    Microsoft Office 365
    Posts
    174

    Re: Calculate Elapsed Time Between Two Times

    Thank you AliGW for your quick respond. This formula seems to work; however, it doesn't seems to calculate when there are two occurrences of clock ins and clock outs such as in 11/13/2017. It only calculate the first instance. Is there a way to accomplish this?
    Thank you so much

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,919

    Re: Calculate Elapsed Time Between Two Times

    Or try:

    =IF(OR(COUNTIF($A$2:A2,[@Tdate])={3,5,7}),[@Ttime]-G1,"")

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Calculate Elapsed Time Between Two Times

    or try below
    I2=IF(AND(COUNTIFS(A$1:A2,A2)>2,ISODD(COUNTIFS(A$1:A2,A2))),G2-G1,"")
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Contributor
    Join Date
    02-20-2011
    Location
    Candada
    MS-Off Ver
    Microsoft Office 365
    Posts
    174

    Re: Calculate Elapsed Time Between Two Times

    Thank you so much Phuocam. I tried your formula and it works beautifully. What does {3,5,7} mean? I just want to learn more by understanding each piece of the formula

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculate difference between dates and times - display as percent elapsed
    By Sparkytech in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-27-2018, 08:07 PM
  2. Replies: 10
    Last Post: 03-31-2014, 01:18 PM
  3. [SOLVED] How do you calculate elapsed time in hours between times on different dates?
    By RPeruzzi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-10-2013, 01:16 PM
  4. Using start and end times to determine elapsed time
    By Lothar69 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-14-2011, 09:25 AM
  5. Replies: 2
    Last Post: 04-27-2011, 08:21 AM
  6. calculate elapsed time between dates and times
    By Jenna in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2006, 12:45 PM
  7. Replies: 6
    Last Post: 03-25-2005, 03:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1