[Solved] How can I enter time intervals in Calc

Discuss the spreadsheet application
Post Reply
DamnedTexan
Posts: 7
Joined: Mon May 10, 2021 3:41 am

[Solved] How can I enter time intervals in Calc

Post by DamnedTexan »

I would like to be able to enter a list of time intervals in calc. Let me be clear, I UNDERSTAND HOW TO FORMAT THE CELLS SO THEY DISPLAY 1:06. I want the Input line to say 1:06 and not 1:06 AM. In other words I would like for the cell to contain the information I typed into it. The only calculation that will be done is to add them together at the end. How can I enter 1:06 as in 1 Hour 6 minutes without it being converted to 1:06 AM? I am trying to track how much time I spend on different projects, not what time it was. The only option I have found is to enter everything in minutes and convert it back at the end. Now I am doing more calculations in my head than calc is doing for me.
Seems the only reasons to use calc for this are to prevent me from losing the data and my hand writing is atrocious. Otherwise a piece of paper would do as much and as well as calc is doing.

My problem is not solved I just am resigned to the fact that what I want calc to do is not possible. Apparently no one ever thought someone might like to enter time that had nothing to do with the time of day. After all, who would ever like to keep track of track times. I mean, other than coaches, trainers, scouts, mechanics, drivers and sports writers. And who would ever wish to record elapsed time for any other reason? Efficiency experts, bookkeepers, accountants and people who a manage business don't count. So I suppose it makes perfect sense that calc is incapable of storing time as time and instead stores it a a fraction of a day.
Last edited by DamnedTexan on Mon May 10, 2021 6:00 am, edited 3 times in total.
OpenOffice 4.1.5 Windows 10
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How can I enter time intervals in calc

Post by robleyd »

Simply format the cells where you want to enter times as HH:MM and they will display as you want them, and still be suitable to use in calculations. If total time is expected to exceed 24 hours, you can use the format [HH:MM] to correctly display total hours.

If you are new to spreadsheets, you may find [Tutorial] Ten concepts that very Calc user should know a useful resource, particularly Section 4 - Times in cells.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
DamnedTexan
Posts: 7
Joined: Mon May 10, 2021 3:41 am

Re: How can I enter time intervals in calc

Post by DamnedTexan »

I want the cell to contain the information I type into it. I do not want it altered or converted. In other words I do not care to change how it is displayed, I want the input line to show the same thing I put in the cell.
OpenOffice 4.1.5 Windows 10
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How can I enter time intervals in calc

Post by robleyd »

The display in the Input Line would seem to be irrelevant for your purpose, unless there is something I am misunderstanding. It doesn't affect your ability to see in the cell what you have entered, nor does it affect the ability to use the data in a calculation.

I suspectt he only way to have the input line mirror the exact characters you input would be to first format the cells as text, which of course removes any capacity to use them in calculations.

The cell in which you enter times will in fact not store what you have entered, but will store a decimal representation of the time as described in the link I gave in my previous answer.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
DamnedTexan
Posts: 7
Joined: Mon May 10, 2021 3:41 am

Re: [Solved] How can I enter time intervals in calc

Post by DamnedTexan »

My times will ALWAYS be more than 24 hours. Every time without fail they will be more than 40 hours.
OpenOffice 4.1.5 Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How can I enter time intervals in calc

Post by Zizi64 »

Always there is conversion and the Calc always will format a típed-in numeric values.

The Date-Time related numeric values are floating pont numbers really. You can not type-in the floating point numbers directly: by ryping the bits of the signs, mantissa and exponent.

You can type-in aphanumeric characters and some other signs only.
When you type in these straight and curly characters:

Code: Select all

13
, the Calc will be convert them to foating point number (with zero decimal fraction part), and will display same characters as a formatted string:

Code: Select all

13
But when you type in these characters:

Code: Select all

1:03
the Calc will recognize it as a numeric value based on he ONE COLON character. The Calc "thinks in this way":

"This typed-in string represents hours an minutes by its numeric like parts: the 1 and the 03. ... I must convert it to a numeric value ... and I must display it as a Date/DateTime/Time value by the default/enforced formatting rules..."

The Calc will converts the typed-in string to a floating point number, and then it will format the number as a string like value.

It happens with EVERY numeric values in the Calc. Only plain texts remain truly identical texts, but calculations cannot be performed with the texts.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] How can I enter time intervals in calc

Post by Zizi64 »

My times will ALWAYS be more than 24 hours. Every time without fail they will be more than 40 hours.
Preformat/Format the Cells by custom a style containing the

Code: Select all

[hh]:mm
format code for the numbers before/after you type-in the "dutaration/time interval values".
Last edited by Zizi64 on Mon May 10, 2021 8:21 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] How can I enter time intervals in calc

Post by robleyd »

It seems I am not properly understanding your problem. I can create a spreadsheet and format it to allow me to enter, and see, times in the format e.g. 1:06 and use those entries to calculate a sum of times displaying total hours even if the sum is greater than 24 hours - see the attached sample.

Perhaps if this does not cover what you need to do, upload a sample spreadsheet file showing the data you are working with and an example of what result you are trying to achieve. How to upload a file.
Attachments
hours_demo.ods
(9.47 KiB) Downloaded 201 times
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
DamnedTexan
Posts: 7
Joined: Mon May 10, 2021 3:41 am

Re: [Solved] How can I enter time intervals in Calc

Post by DamnedTexan »

I changed everything to minutes and it works. The major issue was that it would change times under one hour such as 00:57 to 12:57 whenever for whatever reason it decided the time format should be 12 hours on this sheet and 24 on that sheet. I decided that if the only way it could store time was in fractions of a day and those fraction could be changed by .5, 12 hours, then I would enter the time as a whole number with the title Minutes in the top cell instead of Time which I originally planned to use. It works and I know any mistakes are due to my fat fingers.
OpenOffice 4.1.5 Windows 10
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] How can I enter time intervals in Calc

Post by robleyd »

You are probably missing something simple; if you want to get other sets of eyes to try and see what is going on, please feel free to upload a sample spreadsheet that shows the issue.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] How can I enter time intervals in Calc

Post by Villeroy »

You only need to understand that spreadsheets can have 2 different types of cell values: text and number. Text is for labels and names of categories, identifiers etc. Numbers are for anything calculatable such as decimal figures, dates, times and boolean true/false values.
As an arithmetic tool, the program always tries to interprete input as number. If input does not evaluate to a number, then the input is treated as text.
This is NOT a matter of taste or preference. It is a matter of functionality. There are ways how you can enter numeric expressions as literal text but then these values can not be used in calculations anymore. You won't be able to sum up your time intervals if they are literal text. The text "36:00" is not the same as the time value 36:00 but the time value is the exact same value as the number 1.5 because 36:00 is 1.5 days and behind the scenes all times are calculated in unit "days". You can calculate with that number and format it any way you like whereas the text "36:00" is a string of 5 characters, 4 digits with a colon. No number format will change the look of the text "36:00" and you can't calculate with that string of characters even if it looks like a formatted number.

And then there are formulas. Formulas start with a = and return the same 2 types of values. Most formulas return numbers (we are talking about a tool named "Calc"), some formulas return text, e.g =LEFT(A1;3) returns the leftmost 3 characters from the text in cell A1. Formulas may return error values as a third type of possible spreadsheet values. Errors propagate across all dependent formulas. If there A1 has an error value, =LEFT(A1;3) will show the same error as in in its predecessor A1.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Bill
Volunteer
Posts: 8932
Joined: Sat Nov 24, 2007 6:48 am

Re: [Solved] How can I enter time intervals in Calc

Post by Bill »

DamnedTexan wrote:I want the Input line to say 1:06 and not 1:06 AM.
The cell format code [HH]:MM:SS can be used for more than 24 hours. For your example, the cell and input lines show 01:06:00. This is the closest I've found to what you want.
AOO 4.1.14 on Ubuntu MATE 22.04
DamnedTexan
Posts: 7
Joined: Mon May 10, 2021 3:41 am

Re: [Solved] How can I enter time intervals in Calc

Post by DamnedTexan »

Thank Villeroy for explaining in detail something that I understood long before your post. While it is good information you came closer to describing my problem than to solving it. You weren't really close to describing my issue, just closer to that than to giving me a solution. After bouncing the question about I realized the easiest thing to do was convert everything to minutes and there is no longer a problem. [Solved]
OpenOffice 4.1.5 Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] How can I enter time intervals in Calc

Post by Villeroy »

So you multiplied the times by 1440 (24*60) and removed any time formatting?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
DamnedTexan
Posts: 7
Joined: Mon May 10, 2021 3:41 am

Re: [Solved] How can I enter time intervals in Calc

Post by DamnedTexan »

Why would I need to multiply times by 1440? 1 hour and 30 minutes is 90 minutes, 5 hours is 300 minutes, you multiply the number of hours by 60 and add the minutes. Where does 1440 enter into that discussion? I am not sure what you think I was trying to do, but I am positive that you got it wrong. I honestly do not understand why you keep replying to a post that says [Solved] with answers that come very close to having nothing to do with the question I asked. I will give you credit for being the most persistently unhelpful person here.
OpenOffice 4.1.5 Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] How can I enter time intervals in Calc

Post by Lupp »

I would assume there is a chance that you may come to a point where you want to calculate soemthing partly based on durations you directly entered, but partly also based on durations calculated from TOD values (by substraction mostly).
If this can occur it's essential that all the durations are expressed in the same unit.

Since durations calculated from TOD primarily use the fix (pseudo-) unit d (day), you need to apply a factor of 1440(=60*24) to them before you combine them with a time entered based on the unit min.

Most likely Villeroy had this in mind.

You ("DamnedTexan") might consider to stop your ranting and to try to be one of the helpful persons here.
Replying to (probably only attempted in some cases) answers routinely that you already knew this and much more, may not be the most productive attitude.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] How can I enter time intervals in Calc

Post by Zizi64 »

Why would I need to multiply times by 1440?
Because all of numeric Date/Time values are stored in unit DAYS by default (the integer part represents the whole days, and the decimal fraction represents the hours, minutes and seconds.) If you want to see your numeric DateTime related data (without any formatting) in unit Minutes, then you must multiply it by 1440.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] How can I enter time intervals in Calc

Post by Lupp »

Going back to the original question.
Its central point -as far as I understood- was
DamnedTexan wrote:...How can I enter 1:06 as in 1 Hour 6 minutes without it being converted to 1:06 AM?...
It has indeed a very simple answer: You can't.

However, there is a reason for that, and a useful answer should state that also: Some locales (in specific en-US, but also others) use outdated formats by default for TOD respecting their old-fashioned and stubborn users. To a bit more detail: As soon as the "recognition" process trying to get something numeric from an entered sequence of keystrokes guesses A TOD as the intended input, it will set the respective cell to the associated format, and for 1:06 that's 1:06 AM in en-US. No user posting here responsible for that. It's just an attempt to do what the supposed "most users" would expect - and in fact they mostly expect nonsense.

Therefore: If you want to get 1:06 for 1:06 - not as a string, but as a formatted numeric content- you need to prepare the cells intended to take values of the kind by setting an appropriate number format for them. Since you only gave an example using the old sexagesimal representation of fractions of an hour, this format should be [H]" h "MM" min"(*). Doing it this way it's indispensable that you also accept the "unit" d as already mentioned - with all its disadvantages. The related factor 1440 was already discussed, and, of course, you knew everything in advance.

(*) Often badly replaced by [H]:MM. The colon should only be used for TOD, but not for durations. There are standards.
Last edited by Lupp on Fri May 14, 2021 5:17 pm, edited 2 times in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] How can I enter time intervals in Calc

Post by Villeroy »

t105211.ods
How to enter time intervals
(13.99 KiB) Downloaded 173 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
DamnedTexan
Posts: 7
Joined: Mon May 10, 2021 3:41 am

Re: [Solved] How can I enter time intervals in Calc

Post by DamnedTexan »

First I would like to apologize for getting upset with some of you who attempted to answer my question. It was my fault that many of you gave me answers that had nothing to to with what I wanted to know. I phrased my original question poorly because at the time I didn't know enough to know what to ask. In spite of the question being vague and confusing a number of you attempted to help. I was out line responding as I did to some of you and I apologize. You were trying to help and I should have been more appreciative of that.

To clarify my original question...
Now that I understand the answer I realize I should have asked "How can you enter time intervals in calc AND PREVENT IT BEING CONVERTED INTO A FRACTION OF A DAY? The answer is you can not do it, because calc only understands time as a portion of a day.

After someone here made me aware of that I chose to simply convert everything into minutes. There is no need to multiply anything by 1440 because there have been no times entered that were anything except numbers as far as calc is concerned. I had entered times in 15 to 20 cells when I started having problems and asked my question. In my opinion the fact that calc will convert 45 minutes to 12:45 (That is an example I made up to illustrate a point, please do not start explaining 12 vs. 24 hour clock formats) at random makes it almost useless for time keeping. Because it will show 00:45 in the cell while storing it as 12:45 and when you add the column of times it will add 12 hours to the 45 minutes you actually entered but still show only 00:45 in the cell making it difficult to find the error. Converting everything to minutes before it goes in the spreadsheet is simple and it works. When you enter 210 in a cell it remains 210 and calc will not begin treating it as if it is actually 930.

Thank you to everyone who answered and I apologize for the sarcastic remarks I made to some of you.
OpenOffice 4.1.5 Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] How can I enter time intervals in Calc

Post by Villeroy »

Apologies accepted.
:super:
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply