Hello i have spreadsheet and i have some data in column A that looks like this :
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”.
Assuming that the cell content of A1 here is indeed a wall clock time value type and not just text:
=LEFT(TEXT(A1;"HH:MM");2)
=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