Ask Your Question


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

kik94 gravatar image

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

3 Answers

Sort by » oldest newest most voted

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

erAck gravatar image

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 more


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 gravatar imageSM_Riga ( 2019-02-18 22:11:40 +0100 )edit

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

Lupp gravatar image

(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.

See also:

edit flag offensive delete link more



Well, I should have known earlier.

ebot gravatar imageebot ( 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 gravatar imagekik94 ( 2019-02-20 08:48:06 +0100 )edit

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

ebot gravatar image

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 more
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 187 times

Last updated: Feb 20 '19