Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 03 Jul 2019 13:57:26 +0200How to create a chart with date-time values.https://ask.libreoffice.org/en/question/199843/how-to-create-a-chart-with-date-time-values/I'm trying to create a chart where the x-axis is date time values, but the chart always seems to line up all y values for each date with the date only, no separation by the fraction for time.
How can I make it use the full value?TimLeeWed, 03 Jul 2019 13:57:26 +0200https://ask.libreoffice.org/en/question/199843/weird Calc results from elapsed-time formula, specific times onlyhttps://ask.libreoffice.org/en/question/177589/weird-calc-results-from-elapsed-time-formula-specific-times-only/In Calc, I use a formula for getting the elapsed time between entries, and the output of the formula returns a 2 or 3 digit number representing *minutes elapsed* between clock times. The formula:
`=(B1-A1)*24*60`
where I manually enter the times (A1 and B1) as HH:MM, formatted as 24-hour for display. For most values, this returns results like this
15:00 15:53 53
However, when the two clock times are roughly between 7:55 and 8:55 AM (the exact limits shift, and I can't keep track of why), and usually ONLY if they are 54 minutes apart OR LESS, they return a number with an inexplicable fraction (13 decimal places!) as here:
08:00 08:53 53.0000000000001
or here:
08:02 08:56 54.0000000000001
or here:
07:57 08:49 52.0000000000001
or here:
07:55 07:56 0.999999999999996
BUT, not here:
08:01 08:56 55
nor here,
08:43 08:57 14
nor here
07:55 08:48 53
I could probably narrow down the specific limiting factors, but I am not that patient. If you ask me why I chose the formula I use for this, my answer is, 'because I did a search and this was the simplest one that worked for me', not because I understand exactly how it works--I'm not that strong a numbers guy. But I think I can see why it should work, and why it should not return fractional results!
Very confused and hoping for help. The weird results are practically NBD, as I get the info I need from the answer, even if it has 13 decimal places. But, while I'm not good at math, I would like for my math to work and make sense, so I'm pretty bothered by this.
thanks for any help!
dpmaddalenaFri, 28 Dec 2018 22:13:11 +0100https://ask.libreoffice.org/en/question/177589/timestamp calculationshttps://ask.libreoffice.org/en/question/172573/timestamp-calculations/Hi,
i have timestamps like:
> 1970-01-01T00:00:45.890622+00:00
> 1970-01-01T00:00:45.891422+00:00
and I need to find the difference between them, in this example, I would be looking for something like `0.008000`.
How do I do this?
I've already adjusted the format of my cells to `YYYY-MM-DDTHH:MM:SS.000000+00:00`,cerrThu, 15 Nov 2018 20:21:27 +0100https://ask.libreoffice.org/en/question/172573/Sum time gives error Err:522https://ask.libreoffice.org/en/question/136086/sum-time-gives-error-err522/ I have columns formatted as Category: Time, Format: 13:37.
I want to use the formula Sum() to summarize the amount of time used in the columns.
My formula: =SUM(G8:G39)
I get the error: Err:522
This is how my Spreadsheet looks like: https://pasteboard.co/GQFGEIr.png
soloThu, 26 Oct 2017 07:37:41 +0200https://ask.libreoffice.org/en/question/136086/question Can LibO display a calc cell using a wrapped date&time?https://ask.libreoffice.org/en/question/131431/question-can-libo-display-a-calc-cell-using-a-wrapped-datetime/I have recently been using OpenOffice and do not understand the full range of date/time formatting options available.
Is it possible to take a value such as "2017-09-18 15:45" to display the two pieces of the value stacked inside a Calc cell?
I've tried a few things to no avail.
Note: Open Office will display the date as a wrapped value and I have made extensive use of that capability for some time. However, OO is causing heartache due to its antiquated method of managing pending changes.
--
Thanks
DanAdminDanAdminTue, 19 Sep 2017 22:16:12 +0200https://ask.libreoffice.org/en/question/131431/calc how to add time increments?https://ask.libreoffice.org/en/question/121650/calc-how-to-add-time-increments/I want to make out a time sheet for students, showing how they spend their time in 15-minute increments.
But they can start their day wherever they want.
I need column on the left that says start time on the top, and then underneath that a formula that adds a 15-minute increment to whatever the time is.
So:
8:00 AM
8:00+00:15
or something like that.SusanCraginFri, 11 Aug 2017 15:56:52 +0200https://ask.libreoffice.org/en/question/121650/Convert "days:hours:minutes:seconds" to secondshttps://ask.libreoffice.org/en/question/64606/convert-dayshoursminutesseconds-to-seconds/ Hello,
I have a cell entries which look like "7:20:45:53" and stands for "days:hours:minutes:seconds". How can I convert these to seconds?
I need this because I have other cell entries that are like "92643.665931" in seconds.
Thanks for your help in advancebonanza123Wed, 17 Feb 2016 10:24:22 +0100https://ask.libreoffice.org/en/question/64606/calc - calculate with custom timestampshttps://ask.libreoffice.org/en/question/82737/calc-calculate-with-custom-timestamps/Let's say I have cells with custom timestamps, e.g.
2016-10-18T14:14:09.831Z-GET
2016-10-18T15:05:09.854Z-GET
And I want to calculate the time elapsed in-between them (in this case, roughly ``3060``seconds).
How do I do that?DiesNutsTue, 29 Nov 2016 12:31:49 +0100https://ask.libreoffice.org/en/question/82737/Change GMT date and time to GMT minus 7https://ask.libreoffice.org/en/question/42041/change-gmt-date-and-time-to-gmt-minus-7/Sorry, I can't upload my example because I need 3 points to do so. But you can copy/paste the semi-colon separated values below, save it as a .csv file then open it in LibreOffice spreadsheet and delimit on semicolons only (since there are commas in the formulas). :)
When I download Twitter stats, I get a .csv file that contains this date and time format (in one cell):
2014-11-02 01:58 +0000
Their date and time is provided in Greenwich Mean Time, but I want the data to display in my local date and time (GMT - 7, Arizona USA).
I concocted a time-consuming and manual workaround where I delimit/break apart the contents of the original cell so that the info is spread across 12 columns, like this:
Begin copy paste into .csv file here:
a;b;c;d orig gmt;e date only;f hour;g minute;h gmt;i gmt-7;j azHour;k azTime;l azDate
528727735825601000;https://twitter.com/radiophoenix/status/528727735825600513;Tune in now (7-9 p.m.) for @HipRawkNation http://t.co/UHeZPRk6Ls;2014-11-02 01:58 +0000;2014-11-02;01;58;+0000;=F2-7;=IF(F2<7, F2+24-7-12+12, F2-7);=TIME(J2, G2, 0);=IF(F2<7, (E2-1), E2)
528697813476340000;https://twitter.com/radiophoenix/status/528697813476339712;Now Playing The Revelations - How Could You Walk Away (Featuring Tre Williams) http://t.co/vzLPekOpsL;2014-11-01 23:59 +0000;2014-11-01;23;59;+0000;=F3-7;=IF(F3<7, F3+24-7-12+12, F3-7);=TIME(J3, G3, 0);=IF(F3<7, (E3-1), E3)
NOTES:
"separated by other = ;"
format columns E as date Y/M/D
The date should change in the first row of data (from Nov. 2 to Nov. 1, 2014), but not in the second (should remain Nov. 1, 2014).
End copy paste.^^
I really do not want to go through this formatting, etc. every time I download new data from Twitter. Is it possible to update both the date and the time in one or two lines? Math is NOT my strong suit so I've burned hours on something you can probably whip up in 5 minutes.
Thank you! :)
PS I am using LibreOffice 3.5.5.3
Build ID: 7122e39-92ed229-498d286-15e43b4-d70da21
Mac OS 10.6.8 pamelar987Wed, 05 Nov 2014 02:32:49 +0100https://ask.libreoffice.org/en/question/42041/