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?
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?
Last edited by Pepe Le Mokko; 10-01-2019 at 02:40 AM. Reason: Removed unnecessary part of title
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!!
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
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
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
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.
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
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?
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.
Glenn, the tracker in working now (I guess). I have changed 20 to 15000. I am attaching the tracker here.
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.
Thank you again! Wish to learn more and more of excel.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks