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

(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: Split Cells (View topic) • Apache OpenOffice Community Forum

Well, I should have known earlier.

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

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

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)

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