+ Reply to Thread
Results 1 to 11 of 11

calculating total time

  1. #1
    Registered User
    Join Date
    05-24-2019
    Location
    India
    MS-Off Ver
    2013
    Posts
    30

    Question calculating total time

    I need help with calculating total time. I am attaching a sample excel sheet in which I need a column (in sample sheet column H) to calculate total time day wise from columns D an F. Could you guys help me with this problem?
    Attached Files Attached Files
    Last edited by Pepe Le Mokko; 10-01-2019 at 02:40 AM. Reason: Removed unnecessary part of title

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: calculating total time

    Change D2 to:
    =IF(D2="","",F2-D2)

    and format as HH:MM, not as text.

    In H2:
    =IF(I2<>"Completed","",IF(AND(I1="In Progress",I2="Completed"),SUM(G1:G2),G2))

    If this is not what you want, please amend your sheet to show expected results, as you didn't TELL us what you wanted calculated. this is a guess!!
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    05-24-2019
    Location
    India
    MS-Off Ver
    2013
    Posts
    30

    Question Re: calculating total time

    Hi Glenn,

    Thank you for the sample. What I want is; the total number of hours for a particular day to be displayed in "total Time" column.

    For instance in the attached image sample. for the date 09/24/2019 there are 4 rows having time 02:17, 00:22, 00:55 and 02:44 respectively, so, I want the "Total Time" column to display the sum of these 4 rows (which is 5.38 hours calculated manually). I hope I was able to make it clear this time
    Attached Images Attached Images

  4. #4
    Registered User
    Join Date
    10-21-2010
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    34

    Re: calculating total time

    Hi,

    one suggestion, change the date format to m-d-yy h:mm AM/PM; so it is easier to calculate if the time goes overnight.

    example
    In Out Total Hours
    10-1-19 08:35 AM 10-1-19 09:35 AM 01:00
    10-1-19 08:30 AM 10-2-19 08:30 AM 24:00

    The formula i used for total hours =TEXT(D3-C3,"[hh]:mm")

    Regards
    Ram

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: calculating total time

    This may not give you what you wanted.... but that may be because there is no blank row between the last two dates:

    =IF(COUNTIF($C$2:C2,C2)=1,SUM(G2:INDEX(G2:$G$20,MATCH(TRUE,INDEX(ISBLANK($F2:$F$20),0),0))),""), copied down; with the same change in column F.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-24-2019
    Location
    India
    MS-Off Ver
    2013
    Posts
    30

    Question Re: calculating total time

    Hey Glenn,

    Thank you so much it is working now. I understand that there wasn't blank row for 09/26/2019 and 09/27/2019 which I added.

    Just one thing the formula that you gave is working only for two rows, If I am adding more than two rows then the "total time" shows N/A. I have attached the images for both situations.

    Sample 1 is the image where the formula is working all fine, whereas sample 2 is the image where, when I add data in more than 2 rows the "total time" column shows N/A
    Attached Images Attached Images

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: calculating total time

    Hard to tell with a picture of a sheet rather than an actual sheet.... But did youchange the 2 20s in the formula given above, if you were going beyond row 20?

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: calculating total time

    Back at the PC. Please post my sheet, showing the error. I can't tell from a picture, as it seems OK to me...

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  9. #9
    Registered User
    Join Date
    05-24-2019
    Location
    India
    MS-Off Ver
    2013
    Posts
    30

    Question Re: calculating total time

    Glenn, the tracker in working now (I guess). I have changed 20 to 15000. I am attaching the tracker here.
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: calculating total time

    Looks OK to me!!

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  11. #11
    Registered User
    Join Date
    05-24-2019
    Location
    India
    MS-Off Ver
    2013
    Posts
    30

    Re: calculating total time

    Thank you again! Wish to learn more and more of excel.

+ 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. Total time calculating incorrectly
    By macrocosm.nomad in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 06-04-2018, 03:45 PM
  2. Calculating total time based on start and end time.
    By famin in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-11-2016, 09:50 AM
  3. [SOLVED] Total time not calculating correctly
    By moppyau in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-07-2013, 09:46 PM
  4. Formula for Calculating Total Time
    By cherilannc in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-22-2013, 10:42 AM
  5. Calculating Total Time
    By SailorGuy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-31-2008, 05:09 AM
  6. [SOLVED] Calculating total time
    By David Gartrell in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-23-2006, 03:45 AM
  7. calculating total time
    By bcamp1973 in forum Excel General
    Replies: 4
    Last Post: 05-08-2006, 02:32 PM

Tags for this Thread

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