I have several rows with start time and end time, then set a formula to add the total time for that task. Now I would like to add that column of those total times to a cell at bottom of the column, but after using the formula =F3:F38, the end results is #VALUE! in the cell box. I clicked the Format Cell to [HH]:MM for time over 24 hours and other tried options, but nothing is working. I have no idea what is causing this.
Can someone help, please.
Thank youIndividula Time Info.xlsx
Hello,
=F3:F38 is not a valid formula. May be you want =SUM(F3:F38)
Update Do 23. Mai 11:30:32 CEST 2019 (having test data now)
Please read my last comment - here is an suggested solution
But be aware - even this calculation will fail, if you work more that 24 hours in a row.
Nitpick: =F3:F38 is a valid formula, but it doesn’t do what the OP wished it did… it creates a reference of an implicit intersection of the range F3:F38 with the formula’s cell position, which on row 3 returns the content of F3, on row 4 the content of F4, …, up to row 38 the content of F38. In any other row (<3 or >38) an error because there is no implicit intersection with the formula’s range.
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.