Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 05 Dec 2018 18:34:55 +0100Average time between actionshttps://ask.libreoffice.org/en/question/175184/average-time-between-actions/ I have a column that has a date and time recorded in it like 09/19/18, 9:34 AM. I would like to find the average time between these recordings each day, then average all the days together. So if I have recordings at 9:39, 2:10, 5:30, 7:30 etc, what is the best way to format the cells to achieve this?WhitelionessWed, 05 Dec 2018 18:34:55 +0100https://ask.libreoffice.org/en/question/175184/[Calc] Rounding auto-inserted time valueshttps://ask.libreoffice.org/en/question/172378/calc-rounding-auto-inserted-time-values/ Using Ctrl+Shift+; is great for quickly entering the current time, but it introduces a subtle problem when performing calculations on the values.
Consider a start time and end time entered this way in fields formatted to show HH:MM
Start time: 20:11
End time: 20:20
Duration: 00:08 (End time minus Start time)
Hold on! Shouldn't the duration should be 00:09?
When I look at the actual figures entered by Ctrl+Shift+;, I see ...
Start time: 20:11:48.42
End time: 20:20:26.28
and if I reformat the Duration field as HH:MM:SS, I get the result ...
Duration: 00:08:37.86
... which of course is correct.
So, my question(s):
Is there an easy way to either truncate the Insert Current Time function (Ctrl+Shift+;) so it only inserts HH:MM, *or* what's the easiest way to round that Duration field?
zzxkkwWed, 14 Nov 2018 09:02:03 +0100https://ask.libreoffice.org/en/question/172378/Calculation Helphttps://ask.libreoffice.org/en/question/164979/calculation-help/ I made a spreadsheet telling me that I worked for 4:00 if I clocked in at 12:00 PM and clocked out at 4:00 PM. My hourly rate is $11.25. 4:00 formatted into a number gives me 0.17 and therefore if I do a $11.25*4:00 I get a $1.88 when in fact I made $45.00. Is there a way to multiply hourly rate by hours worked without changing the time or currency formatted cells?chippedcrackerThu, 06 Sep 2018 21:23:51 +0200https://ask.libreoffice.org/en/question/164979/Multiplying timehttps://ask.libreoffice.org/en/question/160175/multiplying-time/ I need to keep hours for a group of volunteers and report the total monthly. Sometimes they work in groups and sometimes they work individually. I created a spreadsheet with columns for start time, end time, elapsed time, and number of volunteers present. I formatted the first two time columns HH:MM AM/PM and the elapsed time column HH:MM. To get the elapsed time, I subtract start time from end time and that does display the correct elapsed time.
Then it goes haywire. At first I tried to sum() the elapsed time column and then multiply it by the sum() of the number of volunteers present column. Somehow 13:00 x 12 computes to 16:00. I tried just manually multiplying the total sum() of the elapsed time column by 12 and got the same result.
Can anyone tell me what I'm doing wrong, or how to achieve my objective? I don't want to have to make an entire row entry for each volunteer because the report would become very large, very fast and also because there are a lot more columns of other types of data that I don't want to have to replicate for each volunteer.
Thanks,
--BrianBrianMMon, 09 Jul 2018 17:58:16 +0200https://ask.libreoffice.org/en/question/160175/Is there an antilogarithm scale selection for Calc axes.https://ask.libreoffice.org/en/question/151230/is-there-an-antilogarithm-scale-selection-for-calc-axes/ I have an XY scatter chart showing temperatures against date. Logarithmic scale works but it compresses the wrong end of the time scale. Antilogarithm would allow old time data to be compressed but keep the latest data stretched out and more readable.
I've previously used Calc to generate an antilogarithm table covering 13 billion years (or thereabouts) which was then painted onto a 24ft long display. Historical images were then attached re big-bang, suns, planets, life, dinosaurs, mankind, pyramids etc, all the way to 1888. i.e. it is another useful means of compressing components. See the bottom scale at HO Model Railroad/Dial D-for-Diesel at web.ncf.ca/fx829.
There is a typical temperature graph under the heading Bees & Bats.
EarltonTue, 03 Apr 2018 12:26:14 +0200https://ask.libreoffice.org/en/question/151230/How to change updating time from link to external datahttps://ask.libreoffice.org/en/question/145944/how-to-change-updating-time-from-link-to-external-data/ I have a file with a link to external data, it works fine but when i first set it up I left the standard 60 seconds for the updates, but now i'd like it to update more like every 600 seconds instead.... ayone knows how to change it ? thanksnfuschiniSat, 10 Feb 2018 17:18:00 +0100https://ask.libreoffice.org/en/question/145944/Can calc update time changes automatically when a time is changed. (A knock on effect). I am using excel for a hourly time management process systemhttps://ask.libreoffice.org/en/question/143994/can-calc-update-time-changes-automatically-when-a-time-is-changed-a-knock-on-effect-i-am-using-excel-for-a-hourly-time-management-process-system/ I would like for a time management system spreadsheet to be able to update time when one change is made. ie: if I change 1 o clock to 2 o clock I would like for the 1 hour change to automatically update the rest of the file tommy6141Wed, 24 Jan 2018 16:39:07 +0100https://ask.libreoffice.org/en/question/143994/How to create Chart value x from 5s to 20min?https://ask.libreoffice.org/en/question/139526/how-to-create-chart-value-x-from-5s-to-20min/ I have data from 5s, 1min, 5min and 20min. I need to put it on a point and line chart. The problem I'm having is the time value is being reflected as sequel intervals and that's not what I'm looking for.
![image description](https://i.imgur.com/SF8P2xp.jpg)multifragFri, 01 Dec 2017 16:01:10 +0100https://ask.libreoffice.org/en/question/139526/custom date and time formatshttps://ask.libreoffice.org/en/question/139500/custom-date-and-time-formats/Hello:
In libreoffice calc (currently using 4.3.7.2) I would like to use custom date and time formats in cells as follows:
Date: YYYYMMDD, that is without separators between year, month and day, eg 20171130.
Time: HHMM with 0-24 h scale without separator between hour and minutes, eg 2132
I tried to set the above formats in format/cells/numbers/category date and time respectively but it doesn't work.
For date it accepts my custom format YYYYMMDD if I type it in the format code field, and it can be saved. But if I enter a date in a cell formatted with my custom format the date changes strangely, eg if I type in the cell 20171130, it becomes -84100904 after hitting enter.
For time formats it even doesn't accept my custom HHMM time fromat. If I type my custom format code in the format code field, the category changes (from time) to user-defined.
What is the solution for this?
Thanks,
suseuser04
suseuser04Thu, 30 Nov 2017 23:46:00 +0100https://ask.libreoffice.org/en/question/139500/When opening xls file in ods, data and time formats are not recognizedhttps://ask.libreoffice.org/en/question/136819/when-opening-xls-file-in-ods-data-and-time-formats-are-not-recognized/When I open a excel file that has date strings like 2017-11-02 and time like 11:45:35, LO opens them with data format 2/11/17 and 1/1/04. Why does it not automatically recognize them as valid dates and times, and is there anything I can do to fix that?Jim RoperThu, 02 Nov 2017 19:19:34 +0100https://ask.libreoffice.org/en/question/136819/How to add a column of Hourshttps://ask.libreoffice.org/en/question/129434/how-to-add-a-column-of-hours/On a sheet I am Calculating the total time from the F column as below;
F4=10:30
F5=09:30
F6=00:00
F7=08:15
F8=10:00
F9=11:00
F10=09:30
F11=01:00
I would like to add these hours Up. So in this Example, in F35 the result should be **59:45**
If there is a way to do this, could someone please give a step by step guide on how I might do this?
Thank-you very much community.simontfsThu, 07 Sep 2017 19:24:05 +0200https://ask.libreoffice.org/en/question/129434/Unable to adjust X-axis labels and tick marks (timestamp) in a Calc line charthttps://ask.libreoffice.org/en/question/129436/unable-to-adjust-x-axis-labels-and-tick-marks-timestamp-in-a-calc-line-chart/ Hi,
I'm charting date & time based data in a line chart (e.g. 1-minute samples over a 24 hr or 48 hr period, = 1,440 samples/24 hr). The X-axis displays date and time. The X-axis labels that are displayed are set automatically. I can't adjust the start of the labelling (it now displays a start time of 16:02, where it should be 16:00), and the interval is set automatically (e.g. 27 minutes) and can't be adjusted. I would like to format the axis to start with the 1st sample as label, and manually set the interval of both the labels and the tick marks. I formatted the X-axis timestamp both as text (=text(REF),MM-DD-YYYY) and as number. It doesn't produce a different result.LibreHenkThu, 07 Sep 2017 19:37:46 +0200https://ask.libreoffice.org/en/question/129436/Calc: How to convert time in MM:SS into secondshttps://ask.libreoffice.org/en/question/109684/calc-how-to-convert-time-in-mmss-into-seconds/Hello, I have read answers to similar questions, but I have not understood which formula I have to use.
In my case I have in the cell "B2" a time expressed in MM:SS and I need to convert it into seconds.Tyco72Sun, 09 Jul 2017 12:02:08 +0200https://ask.libreoffice.org/en/question/109684/Running total/cumulative sum of time?https://ask.libreoffice.org/en/question/95056/running-totalcumulative-sum-of-time/Attached is an example document to help elucidate my question:
[C:\fakepath\Running total:cumulative sum of time example.ods](/upfiles/14950217499189278.ods)
In this document I want to use a formula to make a running total of the time logged. However, I don't want to convert the hours into minutes; I would like the figures to remain HH/MM/SS.
Obviously when the minutes reach 60, they should be converted into an hour, then reset, and when the seconds reach 60, they should be converted into a minute, then reset.
I would prefer that this could be done with the hours, minutes and seconds divided into 3 cells as shown, but if not possible, then placing all within the same cell would be acceptable.
How can I achieve this?
Cheers.
**-EDIT-** I have received an answer from **JohnSUN** that works.
I am leaving this thread open a little longer for the purpose of further discussion, but I am satisfied with this solution.
Will also take a look at some of the other answers when I get more time, thanks to all who have contributed.
***Here is the updated document, with the problem solved using JohnSUN's directions***:
[C:\fakepath\Running total; cumulative sum of time example UPDATED W: JOHNSUN'S ANSWER.ods](/upfiles/14950216461515751.ods)appreciatethehelpSat, 13 May 2017 18:08:12 +0200https://ask.libreoffice.org/en/question/95056/Calc: Sort on formatted time columnhttps://ask.libreoffice.org/en/question/94843/calc-sort-on-formatted-time-column/ I have a spreadsheet with separated date and time columns. The time columns are in format "H:MM:SS AM/PM" (e.g. "1:23:45 AM". I have formatted the cells correctly (highlight the time column, Format > Cells... > Numbers tab > Category: Time, Format code "H:MM:SS AM/PM")
The trouble comes when I try to sort by this column. It's not sorting chronologically, it's stacking all the AMs with the PMs. Is there a way I can make this work?BunglerThu, 11 May 2017 21:21:06 +0200https://ask.libreoffice.org/en/question/94843/How do I turn off time auto-formating?https://ask.libreoffice.org/en/question/93840/how-do-i-turn-off-time-auto-formating/In Libre Calc I enter 7:30, I want it to look like 7:30. Instead it looks like 07:30:00 AM. I tried going to Tools>Auto Correct Options and unchecking every single box and it still changes what I type.
Please give the user control back. Perhaps the most frustrating thing about using modern software is the programmers taking away control from the user. This is why I left windows and starting using linux.
BTW putting ' in front of everything you type is not a solution, its a work around for poor design.moemoemoemoeSun, 30 Apr 2017 15:34:15 +0200https://ask.libreoffice.org/en/question/93840/RTD (real time data) cell format condition increase decrease cell valuehttps://ask.libreoffice.org/en/question/88262/rtd-real-time-data-cell-format-condition-increase-decrease-cell-value/ I'm using an RTD (Real Time Data) sheet. numbers increase / decrease all the time.
I'm trying to implement a Format condition for some reason its not working.
I would like the cell if the RTD data is greater than prev data it will show green, if data is less than prev data it will show red, if data is the same will show yellow.
at this moment its only showing Yellow, it's not recognizing the change in data value.
I initially referenced to this page from earlier discussion and applied same concept but it did not work.
https://ask.libreoffice.org/en/question/87602/conditional-format-each-row-each-cell/
anyone have any ideas, thank you in advance
---------------------------------------------------
updated:
Providing data sheet will do no good unless u have software that updates data. Is there a way to have a cell change conditional format upon itself with Real Time Data.
ie. column A row 1 = 10
data A1 changes to 11 I would like the cell to change Green
data A1 changes to 9 I would like the cell to change RED
A1 cell is highlighted cell value is greater than > current A1 cell value background changes Green
A1 cell value is less than > current A1 cell value background changes Red
A1 cell value is equal to = current A1 cell data background changes white
JG101Tue, 21 Feb 2017 19:22:59 +0100https://ask.libreoffice.org/en/question/88262/How do I grab data from an Automatic Updating Cell (=NOW()) and put it into a different cell and not have it update?https://ask.libreoffice.org/en/question/83640/how-do-i-grab-data-from-an-automatic-updating-cell-now-and-put-it-into-a-different-cell-and-not-have-it-update/I know this is *probably* not possible, but hey I've done some pretty amazing things with Calc so it's worth asking. What I am trying to do is hard to explain so i'll just give an example.
Cell A1 is =NOW() - Cell B1 is =A1
So lets say A1 says: December 1, 2016 5:55 AM (the current time), and I put in =A1 to B1 (which would grab the December 1, 2016 5:55 AM and put it into B1).
What I want to happen is, lets say in 10 minutes when NOW() updates to December 1, 2016 6:05 AM, I want B1 to STAY at December 1, 2016 5:55 AM and NOT change to the current time.
Basically what I want to happen is record the time when I put the formula into the cell and not update anymore. I know this could all be avoided by just typing in "December 1, 2016 5:55 AM" but with what I am working on that actually takes quite a bit of time.MrTravisLSun, 11 Dec 2016 08:04:06 +0100https://ask.libreoffice.org/en/question/83640/What is my mistake?https://ask.libreoffice.org/en/question/76186/what-is-my-mistake/A1(23:34) B1(00:05) =MOD((B1-A1), 1) = 7:06 ? should be 00:31
Thanks for your time!abiTue, 30 Aug 2016 13:27:12 +0200https://ask.libreoffice.org/en/question/76186/Ref.info needed: =MOD, (findng Differce)https://ask.libreoffice.org/en/question/75857/refinfo-needed-mod-findng-differce/ having (18:00) (21:06),
=MOD((18:00)-(21:06), 1) = 7:06
Why?
Thanks ...
gripWIN@gmail.comabiThu, 25 Aug 2016 22:16:32 +0200https://ask.libreoffice.org/en/question/75857/How do I create a macro to insert the time into a cell in Calc?https://ask.libreoffice.org/en/question/73224/how-do-i-create-a-macro-to-insert-the-time-into-a-cell-in-calc/In my time-tracking spreadsheet I was able to click shift and ":" to insert the current time into a cell. How can I duplicate same in Libre Office Calc? I can insert the time into a cell but it takes several clicks to do so, I was able to do this in Excel with one keystroke.NancyHTWed, 13 Jul 2016 15:59:31 +0200https://ask.libreoffice.org/en/question/73224/Date calculationshttps://ask.libreoffice.org/en/question/72032/date-calculations/ Hi,
on my work I often receive documents with a due response date (that is already late when I recieve it...).
Anyway, I created a spreadsheet to control witch responses are more or less late to organize my work.
The spreadsheet has a column with the due date and another wich is "=today()- the value on the due date cell).
It´s good, but I would like it to stop counting when I get done with that process.
How would I do that in a simple way?IporãTue, 21 Jun 2016 16:16:58 +0200https://ask.libreoffice.org/en/question/72032/Format a cell so it automatically adds PM after the timehttps://ask.libreoffice.org/en/question/70635/format-a-cell-so-it-automatically-adds-pm-after-the-time/ I'm trying creating a time sheet for work. I add in the time and it calculates my hours based on the start and finish time.
I want to use 12 hour time as opposed to 24 hour, but I always have to enter the finish time in 24 hours or it will change to AM. Is there a way around this? Removing the AM in the format code results in the time being "12:00 P5".
Thank you in advance.XcytheSun, 29 May 2016 04:02:19 +0200https://ask.libreoffice.org/en/question/70635/cell format date time durationhttps://ask.libreoffice.org/en/question/68968/cell-format-date-time-duration/Need to find a clean way to **change a remainder value of time, from decimal to minutes**. ie 0.25 should be 0.25/60 = 15 mins. if I format the result cell C1 , best I can come up with is a fraction with a **format code** 0.00/60
The following formula I am using gives the duration or time difference between 2 dates and times. I can't seem to seperate my exponent to the left of the decimal from the trig on the right of the decimal point? should be basic. Don't want macros.
Cells A1 = 28/04/2016 , B1 = 06:15
A2 = 29/04/2016 , B2 = 12:25
C1= =SUM((A2+B2)-(A1+A2))*24
C1 looks like 30.10/60 with my format code.
So , any ideas how I can remove the /60 part for a clean number. I'd ideally like 40hrs 15Min as a result. If the exp and trig can be seperated,I can do that bit. Thanks for any help.
StarMarinerThu, 28 Apr 2016 22:43:04 +0200https://ask.libreoffice.org/en/question/68968/Solved: Calc: Concatenate does not carry over leading zerohttps://ask.libreoffice.org/en/question/68703/solved-calc-concatenate-does-not-carry-over-leading-zero/ I am combining data from three fields into one. The first column is "Hour", the Second "Minute", and the third "AM/PM". Data in the first two columns is numeric, and for the third column it is either blank, A, or P (text). In the minutes column, 00 is represented as 0, and 05 is 5, and so on. I revised the format for this column so it would have leading zeros, so became 00. When I use concatenate (along with a : between them) the leading zeros disappear, so for example 8:00 is displayed as 8:0. I've read I can format the minutes as text, however won't they need to be recognized as numbers for when I convert the time to 24 hour format? The end result of these cells is that I will have them not as 8:00 PM but rather 20:00.Crash16Sat, 23 Apr 2016 03:31:49 +0200https://ask.libreoffice.org/en/question/68703/Calc - Time and date conversion problemshttps://ask.libreoffice.org/en/question/64138/calc-time-and-date-conversion-problems/ Hola to All!
I am using LO Version: 5.0.3.2 on Mint Linux. I have struggled for two days with this and cannot find a solution through tons of trial-and-error, or in any online help.
Here are two Calc field contents I am importing from two different external resources that I cannot change the format of:
"127.0.0.1 - - [26/Jan/2016:14:33:07]" and "t=2016-02-06T09:30:08"
The goal is to be able to do a time/date difference comparison between these two cells, as well as being able to sort them.
I am doing a 'mid' string function on the first to grab the "26/Jan/2016:14:33:07" date and time string, and in the second one doing a 'mid' and 'right' function to get two strings w/o the "T", and then using concatenation to stitch them together.
But no matter what cell date/time formats I use, what format I set as system "default" in the options, I cannot come up with a solution to come up with a common date and time in one cell that the system will recognize as valid. I prefer not to have two columns, one for date and one for time as that would probably add a huge amount of complexity to sorting and doing calculations on.
Can it be done in one system recognizable format (sorts properly) like: 02/06/2016 15:10:43?
I am not sure that will sort correctly, but some of the cell formats, using datevalue() show that format.
But I cannot even come up with a conversion process that will directly give me kind of date/time format.
Am I barking up the wrong tree with this approach? Any ideas?
Thanks!
Monty
MontyJMon, 08 Feb 2016 22:54:07 +0100https://ask.libreoffice.org/en/question/64138/How can I display the result from a time calculation as hours with two decimals, for example "1.25 hours"https://ask.libreoffice.org/en/question/59044/how-can-i-display-the-result-from-a-time-calculation-as-hours-with-two-decimals-for-example-125-hours/ I'm doing a calculation where I deduct one timestamp from another. I want the result in hours with two decimals, for example 16:00-15:30 would be 0.5 hours. What cell format should I use?ramaTue, 06 Oct 2015 21:32:36 +0200https://ask.libreoffice.org/en/question/59044/Calc: sum up dates in from different columns in a third column?https://ask.libreoffice.org/en/question/50954/calc-sum-up-dates-in-from-different-columns-in-a-third-column/ Hi,
I have an issue with Calc.
I'm using libre Office Version: 4.3.4.1 , english .
I have a column containing full dates (including time), to which I need to add 9 hours (in another column).
The first column is formated as DD-MM-JJJJ UU:MM:SS
Second column is formated as UU:MM
I now want to add these columns
In a third column, I am using formula =Column1+Column2 (Or "=O2+N2" , next row "O3+N3" , etc.)
The formulas get adapted coorectly according to each row.
Still, 9 hours get added, but also another day gets added! The final date is not 30-04 anymore, but 1-05, 02-05 etc. even though this cannot be explained by 9 hour time difference.
How come? What can I do to prevent this (just 9 hours need to be added to the original date...)? Does anyone know?
Kind regards,
NilsNils-EuroClixThu, 21 May 2015 17:23:42 +0200https://ask.libreoffice.org/en/question/50954/applying function in Calchttps://ask.libreoffice.org/en/question/49216/applying-function-in-calc/Hi all,
How to make function that calculate time difference from col A and B and automatically the result is shown in col C
function I used is: =(Bn<An)+Bn-An, where n is number of row.
The problem is, that function must be applied manually each time I want the result / time difference /kylieMon, 13 Apr 2015 13:30:21 +0200https://ask.libreoffice.org/en/question/49216/strange time displayhttps://ask.libreoffice.org/en/question/41866/strange-time-display/I looked into the matter @joe.aquilina.92 asked (http://ask.libreoffice.org/en/question/41816/time-formats-in-calc-suitable-for-use-in-calculations/) as well and found something which appears to me as bug. However before I write a bug report I would get some opinions here.
LibO 4.2.6.3 on XP
I format a cell MM:SS and entered the string 55:30 directly into the cell and get as a result a display of 30:00 and applying the formula =cell/24 the result is 0.0963...
but
formatting a cell HH:MM and entered the string 55:30 directly into the cell and get as a result a display of 07:30 and applying the formula =cell/24 the result is 0.0963...
see the two screen shots:
![image description](/upfiles/14147458355960572.png)
![image description](/upfiles/1414745852168544.png)
Please also note that the input line displays always 55:33:00 when I choose A6 or A7
The file is attached. [TestTime.ods](/upfiles/14147461406253429.ods)
I would appreciate to get opinions on what I am seeing here.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
edit after comments:
@karolus and @Lupp - I looked at your answers and come the conclusion that the time must always be entered in a string like h:mm:ss to get it correct. Any other data string entry leads in my sheet to an error and what is displayed is not correct.
Wouldn't it therefore be necessary to allow only an time entry in as a string of the form h:mm:ss or hh:mm:ss?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
edit after comment from @Lupp
@Lupp - Sorry I did not want to say "...of the form h:mm:ss or hh:mm:ss?" but "...of the form hh:mm or mm:ss?"
With your long edit - thanks - my originally planned question "Wouldn't it make sense..." becomes obsolete. For me the conclusion is to always (=independent of the cell format) enter time in the hh:mm:ss pattern to avoid any mistakes. A key element to understand is "For TOD there is a standard telling that an incomplete notation of the pattern 'num:num' is to read in the sense of "hours and minutes given"." and the automatism that when 24hrs are full the display become 00:00:00 whereas the value in the cell is 24:00:00. This helped me to understand that entering the string 55:30 creates always a 0:07:30 (cell format: hh:mm:ss) or 07:30 (cell format: hh:mm). The value is 55hr 30min which is 48hr+07hr and 30 min but the display is only shows the 7hr+30min beyond the 48hr.
Pretty tricky, thus one must be very careful.
Thanks for the discussion and I hope that it was helpful for others as well.
ROSt52Fri, 31 Oct 2014 10:03:46 +0100https://ask.libreoffice.org/en/question/41866/