Thank you, that =SUM(F3:F35) definitely got me a number in the cell, unfortunately, the sum isn’t correct and it’s a negative. I tried again to set for over 24 hours with [HH]:MM, but nothing.
This only could be checked having an anonymized example of your file and data. Stating “…to set for over 24 hours with [HH]:MM” is not sufficient - at least not for me. Please upload a test file and I’m pretty sure you’ll get an answer, why your calculation fails.
The sum most likely is correct, but if negative is not expected then your data (or formula if calculated) in cells F3:F38 is not correct.
As always: give details, otherwise help is not possible.
What details would you like? I thought I was being specific.
Also, If I add the time manually, the sum is 31 hours. the negative value displayed is 15:45
In response to Opaque, how do I upload a test file? Are you requesting a screen shot of the table?
There is nothing specific in your question except the range F3:F38 and a display format [HH]:MM
No details about data or formulas that calculate the times you are about to sum.
You have entered which times where that should sum to 31 hours? How did you enter them? What are the actual cell contents in cells F3:F38?
@Hawkster - Use the clip icon in edit mode of your original question to upload a file. And by the way - how could you assume, that stating “sum is 31 hours” has any degree of specification without letting us know, which data are summed up manually.
I assumed the info of a column with times in each cell of that column and attempting to total that entire column would be enough info. Obviously, I’m wrong.
The F column contains hours and minutes of time from the calculation from cells B and C of each row and adds a total time in column F. Column F is what I’m trying to calculate as a total of all time.
The actual contents of column F contain those times in hours and minutes. example:
Row B3 = 6:30 PM and row C3 = 11:15 PM. The total = 4 hours and 45 minutes or 4:45 in F3
Row B4 = 7 PM and C4 = 12:15 AM. The total = 5 hours and 30 minutes or 5:30 in F4
F5 = 4 hours or 4:00
F6 = 5 hours and 15 minutes or 5:15
I’d like to add the F column of those total times for a grand total.
I’ll try to add a file in original question.
File added
Hello Hawkster, your problems result from row 7 and 8 (C7 and C8). You crossed midnight in these rows. The total in hours seems to be correct but in fact the result is counted as negative value. You could see this when changing the format to number. Let’s see how @erAck or @anon73440385 work around this (both pointed already to the 24 h - negative value - problem). You should intercept this incidence somehow. One way could be to split the entry into xx:xx to 23:59 PM and next row 00:00 to yy:yy AM. Another way could be to prove the sign and multiply by (-1).
And normally it would be right to add the date. It could otherwise be the time of three days ago, right?
@Hrbrgr : in general yes, but I’d guess no one would work 72 hours at a stretch. It’s a lot more effort to enter date and time if you want to keep records of daily work tasks only. Shortcuts as [CTRL] + [;] wouldn’t help either since you don’t get the wanted time but the value from time-reference 0 to current time. Also I’d guess that the data isn’t entered immediately but in addition. Another reason, why shortcuts probably won’t help. Hm. I’m also really interessted in this and how to solve it smart with LO calc.
From my point of view the formtting and calculation of “Time” is not adapted to want I assume to be the purpose of the table. I assume this table to be the base of invoicuine working hours to some client. An the number of working hours should be a decimal number (to be used as an multiplicator with an hourly rate). So I personally would (and in my own time sheets I’ve used it that way) change the format to decimal number and add some IF construct to deal with working over midnight.
See resulting example added to an update of my answer.
If you used the [HH]:MM duration format already for cells F3:F38 you would had seen that the result in F7 is -18:45 and in F8 is -18:30, because the value in column C is smaller than the value in column B as the duration crossed midnight. If start and end value are always in a span less than 24 hours then it is sufficient to use this formula in F3 =C3-B3+(C3<B3) and fill down. This for times crossing midnight adds the value 1 (one day) to the result, compensating the negative offset.
If start and end value can span durations of 24 hours or more then you have to use combined date+time cells which you can simply subtract without further compensation.
The overall sum in your attached sample document btw is 32:15, not 31 hours…
This worked for me. Can you please explain logic behind it? Especially brackets.
I think I’ve got it now. Brackets simply get you Boolean value. If it is greater, it returns +1 and if it is less, it returns zero. Very clever. Again thanks. Can’t upvote since I’m new here but this is solution.
Thank you all for the suggestions. It appears oblivious there isn’t a simple solution other than using numeral values, which won’t suffice since the time of day is required per job in each row, which is denoted by the AM/PM. I don’t want to add dates and the grand total of time will most likely always exceed 24 hours, apparently making it more difficult. I will simply add them up manually and input into the cell.
If this is your conclusion …
I was not talking about the grand total not exceeding 24 hours. Each individual duration (job) must be less than 24 hours to use the data as is. Carefully read what I wrote. The solution is completely simple. You won’t find anything simpler. You just need to change the formula once that calculates each row. For completeness change the wall clock time format to a duration format to spot errors early.
And please don’t use Answer for comments if it is not an answer to the original question, use add a comment instead. Thanks.
I never indicated you said such, reading the varying solution drew that conclusion. You stated using “decimal numbers” and thus, as I stated, using numeral value. Isn’t that the same with the addition of decimals? Also, I changed the format in one row to test it out and not being completely spreadsheet literate, I can’t figure out your meaning. If I change the two cells in rows to decimals, the total in column F is inaccurate. How can I replace 6:30 PM with a decimal and yet, display the time I started? I can’t replace with 6.5 PM to 11:15 PM in C3 or 11.25 PM. The total would be the sum of those two numbers equaling 17.75. If I just changed the format in column F to decimal from the added times in each rows, that doesn’t add either. Therefore, I’m stumped and decided just to deal with it manually. Replying in an “answer” box was simply my final answer to do it this way.
Have you really read and tested the suggested solutions? In case I will simply add them up manually and input into the cell. I’d also say “If this is your conclusion …” and would shake my head. No one suggested to create something like 6.5 PM in column B/C.
All you would have to do is to take the file provided by @anon73440385 in the first answer. There you have still your structure, there you have the correct formulars already in place, there you have the correct totals line-by-line and in column F. If you don’t like the numeric totals you could add another column G where you copy the result e.g. G3 =F3 and according the column down plus configure the format to [HH]:MM, voilà display style as you prefer. It’s also not necessary to understand the formula. Copy, paste & modify the formular would also suffice if you’d prefer to continue with your original file.
If you enter decimals the value isn’t a time value. A decimal value 6.5 does not equal time 6:30, 6.5 used in time calculations would be 6 days and 12 hours. Do not change the values in column B or C. Change the formulas in column F instead, as given in my answer. Read it, understand it, do it. Or do it without understanding, but don’t try to do something else without understanding.