[Solved] From colon to comma

Discuss the spreadsheet application

[Solved] From colon to comma

Postby Janina » Wed Mar 25, 2020 4:20 pm

Hello!
I´m new here and my english have some "leftovers".
Is there a possability to change 10:58 to 10,58 in a column?
Last edited by MrProgrammer on Sat Apr 11, 2020 6:15 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Open Office 4 / Windows 10
Janina
 
Posts: 2
Joined: Wed Mar 25, 2020 4:15 pm

Re: From colon to comma

Postby Villeroy » Wed Mar 25, 2020 5:08 pm

menu:Edit>Find&Replace...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: From colon to comma

Postby Lupp » Wed Mar 25, 2020 7:22 pm

Janina wrote:Is there a possability to change 10:58 to 10,58 in a column?

@Janina: Out of curiosity: Is there a usecase?

@Villeroy: If the original content is a number (formatted to HH:MM)
you get the text 10,58:00 with 'Replace' and the text 10,58,00 with 'Replace All'.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: From colon to comma

Postby Janina » Wed Mar 25, 2020 7:42 pm

It´s time; numbers of workinghours. It wont be all correct, but I don´t know hos to convert so that I can make a sum.
Open Office 4 / Windows 10
Janina
 
Posts: 2
Joined: Wed Mar 25, 2020 4:15 pm

Re: From colon to comma

Postby Villeroy » Wed Mar 25, 2020 8:00 pm

Multiply with 24 and remove the time format in order to get the correct number of hours.
Background: All times in all spreadsheets are fractions of day.
-------------------------------------
Time | Actual cell value
--------------------------------------
6:00 | 0,25 [quarter of a day] * 24 => 6
12:00 | 0,5 [half a day] * 24 => 12
18:00 | 0,75 [3/4 of a day] * 24 => 18
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: From colon to comma

Postby Lupp » Wed Mar 25, 2020 8:06 pm

If you have "working hours" shown as 10:58 I would assume it's supposed to mean 10 hours and 58 minutes. The respective cell should be formatted using the NumberFormat code
Code: Select all   Expand viewCollapse view
[HH]:MM
The internal representation of this value used for further calculations uses neither the colon nor a comma, but is purely "technical" in a sense you don't need to know about.
What you need to know is that the number is using the day as it's implicit unit of time.

If you want (e.g.) to calculate an amount of payment from that time value applying a hourly rate, you need to multiply that value by 24 because a day has 24 hours.

You do NOT need to change the format used to show the time.

Substituting a colon shown in the display of a numeric value by a comma, you will get a kind of text instead of a number, and the calculations you intend can be messed up completely.

If you are a first-time-user of Calc, you should read the tutorial viewtopic.php?f=75&t=39529&hilit=every+user+should+know and try to understand at least the sections obviously related to your current task.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: From colon to comma

Postby keme » Thu Mar 26, 2020 8:05 am

Janina wrote:It´s time; numbers of workinghours. It wont be all correct, but I don´t know hos to convert so that I can make a sum.


If you want to add upp hours and minutes using the comma delimiter, you are going to mess it up.
* At best, Calc will assume that the number is in standard decimal notation, and carry over from minutes to hours at >99 minutes, not at >59 minutes as it should.
* At worst, Calc will assume that "this is not a number", store it as text, and no calculations on the input can be trusted.
Just make sure you format all accumulations of time with square brackets around the hours part, and multiply by 24 whenever you need an actual hour count. You will get used to it before you know. Calc will take care of everything numerical. You will be good and fine.
User avatar
keme
Volunteer
 
Posts: 3358
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests