Time (Calc)

Discuss the spreadsheet application

Time (Calc)

Postby ETobias » Sat Sep 15, 2018 11:08 pm

One function that I think is missing in spreadsheets, and not just in OpenOffice, is the ability to calculate time that is in a "stopwatch" format. It does get messy in doing manual calculations. To me it seems that it would be a great enhancement.
OOo 2.3.X on Ms Windows XP + Windows 2000
ETobias
 
Posts: 6
Joined: Fri Dec 26, 2008 8:20 pm

Re: Time (Calc)

Postby MrProgrammer » Sat Sep 15, 2018 11:37 pm

ETobias wrote:One function that I think is missing in spreadsheets, and not just in OpenOffice, is the ability to calculate time that is in a "stopwatch" format.
Read section 4. Times in cells in Ten concepts that every Calc user should know. Perhaps you want a duration instead of clock time. If you need additional assistance describe precisely what "stopwatch format" means. Give an example of the calculation you would like to do.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.5 Build 9789 on Mac OS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3468
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Time (Calc)

Postby Zizi64 » Sun Sep 16, 2018 9:48 am

a "stopwatch" format.

I do not know what is it the "stopwatch" format exactly, but I suppose it, you want see the tenth and the hundredth and the thousandths of the seconds in the cell.
Try to use this format code in the applied cell style:
YYYY-MM-DD HH:MM:SS,000


or
for time only:
[HH]:MM:SS,000

The second format code will show the "days", "months" and "years" in "hours" unit.
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.7 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.0 and AOO4.1.5
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: 7162
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Time (Calc)

Postby Lupp » Sun Sep 16, 2018 3:27 pm

The colon separated formats should be reserved for TOD (TimeOfDay).

Appropriate formats for durations are (selected examples):
Code: Select all   Expand viewCollapse view
[S]" s"
[S].00" s"
[M]" min "SS.00" s"
[M]" min "SS" s"
[H]" h"MM" min"SS" s"

and many more.

Since all the time formats primarily are shaped for TOD, they unchangeably display values rounded-DOWN based on the resolution given by the specific format code.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1981
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Time (Calc)

Postby ETobias » Sun Sep 16, 2018 6:08 pm

Isn't everything listed above related to time of day? I want to do some calculations related to race times (running). I am able to get around it by manual calculations, i.e., trunc (to determine minutes), (D1-trunc(D1))*60 (to determine seconds). This works but it would be nice to have something easier and more elegant.
OOo 2.3.X on Ms Windows XP + Windows 2000
ETobias
 
Posts: 6
Joined: Fri Dec 26, 2008 8:20 pm

Re: Time (Calc)

Postby Zizi64 » Sun Sep 16, 2018 6:20 pm

The date-time values are floating point numbers in the spreadsheet softwares. The integer part represents thhe numbers of the days since the base date. The LO and AOO can handle three different base date. See them in the settings. The decimal fraction part represents the time value inside a day. Read: 0.5 = half day = 12:00:00.000
You can get the difference of two date-time values by substracting the start value from the end value. Then you can format the result (the difference) as is is described above.

Or you can calculate (multiply by 60; and 60; ) for the results in smaller units.
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.7 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.0 and AOO4.1.5
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: 7162
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Time (Calc)

Postby Lupp » Sun Sep 16, 2018 8:33 pm

ETobias wrote:Isn't everything listed above related to time of day? I want to do some calculations related to race times (running). I am able to get around it by manual calculations, i.e., trunc (to determine minutes), (D1-trunc(D1))*60 (to determine seconds). This works but it would be nice to have something easier and more elegant.

Built-in support for the traditional ways of giving time values in mixed units is only available for the formatted display and for entering values in the colon-separated way with an indispensable hours-part like in
Code: Select all   Expand viewCollapse view
0:2:13.605 for a time of 2 min 13.605 s
e.g.
The floatingpoint number used to represent such a value is always based on the unit d (day) which is the worst choice for durations, mainly because many countries forced days of 23 h or 25 h to occur legally.

Yes. That's clearly related to TOD.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1981
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Time (Calc)

Postby ETobias » Sun Sep 16, 2018 9:48 pm

As an example, T2=T1*((D2/D1)^1.06) Where T1 and T2 are times in minutes and seconds and D1 and D2 are distances. I was able to do it by entering the minutes and second in separate cells and do the calculations. It just seems that it should be an easier way.
OOo 2.3.X on Ms Windows XP + Windows 2000
ETobias
 
Posts: 6
Joined: Fri Dec 26, 2008 8:20 pm

Re: Time (Calc)

Postby Zizi64 » Sun Sep 16, 2018 9:52 pm

As an example, T2=T1*((D2/D1)^1.06) Where T1 and T2 are times in minutes and seconds and D1 and D2 are distances. I was able to do it by entering the minutes and second in separate cells and do the calculations. It just seems that it should be an easier way.


Please upload your real sample file (in ODF format) here.


You signature is:
OOo 2.3.X on Ms Windows XP + Windows 2000

Please update it.
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.7 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.0 and AOO4.1.5
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: 7162
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Time (Calc)

Postby robleyd » Mon Sep 17, 2018 3:34 am

Here is a very simple example of displaying times as hours, minutes, seconds and thousandths of seconds and calculating the difference. The cells are simply formatted as [HH]:MM:SS.000 - the decimal point being relevant for my locale; your locale may require a comma.

You can then adapt the format to display as Lupp suggests; you will still be able to calculate based on the underlying floating point value in which Calc stores the data you enter.
Attachments
part_seconds.ods
(8.45 KiB) Downloaded 1 time
Cheers
David
Apache OpenOffice 4.1.4 - Windows 7
Apache OpenOffice 4.1.5 - Slackware 14.2 - 64 bit
User avatar
robleyd
Moderator
 
Posts: 1962
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia


Return to Calc

Who is online

Users browsing this forum: biggles and 38 guests