0

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.

Jamie Hunter
  • 1
  • The problem is whether your data is a string or a date. Post a spreadsheet with it and you'll get an appropriate answer. – Mike Steelson May 22 '21 at 07:48

4 Answers4

2

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)

C. Peck
  • 3,641
  • 3
  • 19
  • 36
amber_s
  • 21
  • 3
1

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

enter image description here

Sergey
  • 1,111
  • 5
  • 7
  • I wish I understood what that meant.. :D I'm just a beginner trying to turn this long date into 2 columns with the date and time. I've tried this and it returns a #VALUE! error message. Any further suggestions? – Jamie Hunter May 22 '21 at 07:33
  • if the value is a date, not a string, use =split(C6," ") – Mike Steelson May 22 '21 at 07:51
1

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@"),"@")

enter image description here

You can then format the results to your liking.

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

marikamitsos
  • 10,264
  • 20
  • 26
0

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:

enter image description here

sdugan
  • 225
  • 4
  • 8
  • Thanks. I tried that but it returned a #NAME? error. Do you have any other suggestions of what I could be doing wrong? – Jamie Hunter May 22 '21 at 07:34
  • In those formulas, the "DATESTAMP_CELL" should be a reference to the cell with the datestamp in it. In this case, if the datestamp is in A1, the formula should be "=LEFT(A1,10)" and "=RIGHT(A1,11)" – sdugan May 22 '21 at 15:57