How do I split this datestamp in Google Sheets

Question

I have a datestamp in this format on C6:

21/05/2021 10:41:35 PM

How can I split this into date and time so that I have date in D6 and time in E6?

I want to do this because I have browsing history I want to import to calendar.

I found several answers to this question with various answers but none of them worked for me.

Answer 1

You can use SPLIT(C6, " ") to split contents of the cell. This splits the time and AM/PM too, but you can join it in another cell using JOIN(" ", E6:F6)

Answer 2

Here is a simple option that you can use as long as each datestamp is exactly the same:

Use the LEFT() and RIGHT() functions. (These also work in Excel)

For the date, use LEFT(DATESTAMP_CELL,10). This will return the first 10 characters from the cell, which in this case are the date "21/05/2021".

For the time, use RIGHT(DATESTAMP_CELL,11). This will return the last 11 characters from the cell, which in this case are the time "10:41:35 PM".

This should be the result:

Answer 3

Since your data comes from an imported .csv file, it could be formatted as text.
If that is the case, try the following formula

=SPLIT(REGEXREPLACE(A1,"(.*\/\d+) ","$1@"),"@")

You can then format the results to your liking.

(As always, do adjust ranges and locale as needed)

Answer 4

date in Google Sheets is always an integer, and time is a fractional number, so you can divide the date and time very easily