Hey all, not sure how to word this, but I basically need help modifying a formula, please see the attached example:
When I studied the Calc doc you attached, there came up a few questions.
-1- For what reasons are the examples so uniform? Is there something you didn’t tell about the scope of application?
-2- You did not make clear if you are aiming at durations expressed in the default “unit” of 1d (day) for time values in spreadsheets, or are you talking of Time-Of-Day (TOD) results you want to achieve.
-3- (related to -2-) Did you choose the ‘Numbers’ format code HH:MM:SS consciously?
For more details and for suggested solutions see this attachment.
Due to the rather lengthy nature of my answers to your questions, I have elected to post them in an answer instead of a comment, to circumvent the need to post numerous comments, which would make for poor reading.
IN REFERENCE TO LUPP’S ANSWER
Good questions!
Clearly I have not provided nearly enough information, so here goes:
- The examples are uniform as I had to make an example document detailing the problem as the original document that sparked the need for the formula contains sensitive information/personal data that I am not at liberty to divulge/post on the internet.
It would have been more work for me to hide said information than to make an example document.
Plus in an example there is less clutter and I can use the whole spreadsheet to help explain what I am trying to achieve in the context of the spreadsheet itself.
The only difference in scope is that I want to add the time of more columns together, such as is detailed in this modified version of the original document I uploaded:
Formula example modified.ods
I figure that once I know how to change the formula contained within the cells at the end of each row to add the total of a second group of three columns, I can use that information to add a third/fourth/fifth etc, when/if needed. Please correct me if I am wrong.
In case you were wondering, the thick borders separating the cells into groups of three are only there to distinguish each group of hours/minutes/seconds.
-
My aim is to make a document that my clients can use to log time invested in certain activities, by entering the time in the empty cells (they will be empty upon completion; as you can see they each currently contain the number “1”).
Each cell represents HOURS, MINUTES AND SECONDS, in that order.
Once the client has entered in the relevant time info, I want the document to add up the total time at the end of each row and display the result (in the cells formatted HH/MM/SS).
As you can see, I know how to do this using one group of three cells, but I can’t seem to figure out how to change the formula to add the total of a second (or even third etc.) group of three cells.
It’s probably fairly simple, but as an absolute novice I can’t figure it out without causing the formula to cease functioning correctly. -
Yes I did choose HH:MM:SS (how did you make that show up in red by the way?) hopefully my long-winded explanation made it clear why.
As always I am grateful for your time, Mr. Jäger.
“(how did you make that show up in red by the way?)”
This forum’s software uses for anything inserted as “code” (see the toolbar) a monotype font in red.
Less uniform examples wouldn’t consume significantly more time on your part. The uniform ones make me needlessly speculate about their origin. a set of examples should try to allow for all the relevant aspects to be demonstrated. In this case a relevant aspect is surely the “TOD-question”. Durations can be >=24h. TOD can not.
Why do you incur the trouble of needing 3 cells for entering one chunk of the total duration? It aggravates the maintenance of the sheets and the usage as well.
Why don’t you tell the users to enter a duration like 17:30:30 for 17h 30 min 30s? Except for ‘Italian (Italy)’ I don’t know a single locale where this would fail.
In addition you might use a specific duration format like [HH]"h "MM"min "SS"s" and get a signal that the input was recognized correctly by the format change.
Why don’t you ask the core question “How best to enter a chunk of duration for usability in calculations?”?
If there are specific needs, articuilate them, and an experienced user may find a short way to help you.
“Why do you incur the trouble of needing 3 cells for entering one chunk of the total duration?”
-The reason I am doing it this way is because some of my clients are very young; I’m not sure that I even trust them to find the colon (
key on the keyboard without getting discouraged with using the spreadsheet.
To circumvent this issue, I feel it is a good idea to confine keyboard usage required to the numbers pad to the right of the keyboard and the arrow keys, which this method allows.
My apologies if I have misunderstood, but my understanding from reading your question is that by the “TOD-question” you are asking whether the purpose of the spreadsheet is to log Time
Of Day or “Duration” (which I assume just means time without reference to when in the day).
If I have this right, then the latter is the case; the purpose of the spreadsheet is to log time spent doing certain activities.
“Why don’t you ask the core question “How best to enter a chunk of duration for usability in calculations?””
As previously stated, I wasn’t sure how to word the question (although now I am thanks to you).
I didn’t even realize that displaying time (without reference to when in the day) was referred to as “duration” and time of day as “TOD”.
Another problem I was having (since the purpose of the spreadsheet is to log DURATION and not TIME OF DAY), is that when the accrued time reached 24 hours it would reset.
By using the time format code you advised ([HH]"h "MM"min “SS"s”) this problem was solved! Excellent!
Clearly I needed a “duration” format code, not a “TOD” one.
In short: The “Time of Day” is ruled by administrations: The imternational standard (UTC) itself has to work with coordinated leap-seconds (of no meaning in everyday life), but political authority interferes much more (assignment to zones, shifting twice a year …). Therefore you cannot reliably calculate durations from ToD values in spreadsheets. The scientific quantity “time” is another thing again. Most people like to mix that all up. Your case is hopefully not too complicated.