Text into Date in Google Sheets

Question

When importing data from Magento to Google Sheets, the date of each transaction is in text format.

For example:

1.1.2020 14.54.16

31.12.2019 16.17.32

How can I format the date part into value? The time of the day should be eliminated.

Desired output:

43830

43831

Test sheet here: https://docs.google.com/spreadsheets/d/1IyGzzrWQMeM-FbV3TVBl23V0NTKq7aWfuf88DKnQwwQ/edit?usp=sharing

what do the numbers in the desired output represent

Ian Middelkamp
– Ian Middelkamp

2020-01-09 14:44:17 +00:00
Commented Jan 9, 2020 at 14:44 — Ian Middelkamp
– Ian Middelkamp, Commented Jan 9, 2020 at 14:44
Numerical value of the date.

Miika K
– Miika K

2020-01-09 15:05:39 +00:00
Commented Jan 9, 2020 at 15:05 — Miika K
– Miika K, Commented Jan 9, 2020 at 15:05
how to get from 1.1.2020 to 43830?

Ian Middelkamp
– Ian Middelkamp

2020-01-09 15:10:41 +00:00
Commented Jan 9, 2020 at 15:10 — Ian Middelkamp
– Ian Middelkamp, Commented Jan 9, 2020 at 15:10

Ian Middelkamp · Accepted Answer · 2020-01-10 11:13:47Z

2

1) The Time component can be extracted using REGEXTRACT function, as I have placed it in the test sheet

=REGEXEXTRACT(A2, "(\d{1,2}\.\d{1,2}.\d{4,4})")

2)replace . with *

=SUBSTITUTE(B2,".","*")

3)Split on *

=SPLIT(C2,"*")

4)Create Date Object

=DATE(F2, E2, D2)

5)Convert to Numerical

=DATEVALUE(G2)

edited Jan 10, 2020 at 11:13

answered Jan 9, 2020 at 14:50

Ian Middelkamp

1508 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Miika K Over a year ago

Hi Ian! Thanks for the input. However, that's not what I'm trying to achieve. Your suggestion is in a text format and that is what I'm trying to get rid of. The output should be in a number format because that's how I'd able to change it to any date format (e.g. YYYY-MM-DD or DD-MM-YYYY) I prefer.

Ian Middelkamp Over a year ago

so you want to make a date object from the text is that correct?

Ian Middelkamp Over a year ago

Try edited solution. See test sheet. Although I still don't understand google's numerical value of date :P

carlesgg97 Over a year ago

As a pointer, Google's numerical value of a date is computed as the number of days starting on 1900-1-1.

Collectives™ on Stack Overflow

Text into Date in Google Sheets

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related