SPLIT CELLS (HOURS)

asked 2019-02-18 08:06:26 +0100

kik94
11 ●9 ●10 ●15

updated 2019-02-20 19:53:46 +0100

Hello i have spreadsheet and i have some data in column A that looks like this :

00:15
01:30 they are in time format

Now what i want is a formula so that i can split them into column B and C. In B column there will be the 1st 2 digits before ":" and in C the 2 digits after them. For example : the 00:15 in A will become 00 in B and 15 in C the 01:30 in A will become 01 in B and 30 in C

Any idea how i could do this ? thanks in advance

edit retag flag offensive close merge delete

add a comment

0

answered 2019-02-18 17:49:17 +0100

erAck

8264 ●41 ●76 https://erack.de/

Assuming that the cell content of A1 here is indeed a wall clock time value type and not just text:

A1: 00:15
B1: =LEFT(TEXT(A1;"HH:MM");2)
C1: =RIGHT(TEXT(A1;"HH:MM");2)

edit flag offensive delete link

Comments

It is possible to use HOUR(), MINUTE() and SECOND() functions in case the result shall be in numeric format. MINUTE(A1) will return 15, HOUR(A2) will return 1 an so on. More about Date&Time functions here

SM_Riga ( 2019-02-18 22:11:40 +0100 )edit

add a comment

1

answered 2019-02-18 09:10:24 +0100

Lupp
20114 ●46 ●126 ●245

(Not an answer. Posted as an answer nonetheless for specific reasons.)

@kik94: Once again you posted the same question in (at least) two forums without mentioning the fact and without giving links. I already pointed you to the fact that this is considered for good reasons to be a violation of good practice. I will not answer your requests any longer If you don't change your behaviour insofar.

edit flag offensive delete link

Comments

1

Well, I should have known earlier.

ebot ( 2019-02-18 09:31:03 +0100 )edit

i apologise for my actions, i didnt thought it was bad what i was doing, it wont happen again

kik94 ( 2019-02-20 08:48:06 +0100 )edit

add a comment

0

answered 2019-02-18 09:28:32 +0100

ebot
3780 ●1 ●12 ●29

The display of the time, e.g. 00:15 is just an interpretation of Calc for the screen. The time is kept internally as a number. I do not know how your wish can be implemented.

You can, however, enter your time as text. This is then entered: ['00: 15] without square brackets. Important is the prefix [']. After ENTER it will not be visible. This tells Calc that it is a text. Now you can make your separation with the functions "Left" and "Right".

image description

edit flag offensive delete link

add a comment

SPLIT CELLS (HOURS)

3 Answers

Comments

Comments

Question Tools

Stats

Related questions

SPLIT CELLS (HOURS) edit

3 Answers

Comments

Comments

Question Tools

Stats

Related questions

SPLIT CELLS (HOURS)