How to convert a date to a string in Google Sheet

Question

I am trying to figure out how to convert a date into a string in a google sheet.

I have one date field that has varying formats. I want create another column that's literally just the same but as a text. For example, if I had the following data

date       date_as_string
12-05-2016 '12-05-2016
12/5/2016  '12/5/2016
2016-12-10 '2016-12-10

Where the ' is just to denote that it is a string note a date.

If your question has een answered, please mark one of the answered as accepted. — Alexander, Nov 20 '19 at 17:54

score 68 · Answer 1 · answered Nov 29 '16 at 03:21

68

You can use the TEXT function.

=Text(cellReference, "mm-dd-yyyy")

answered Nov 29 '16 at 03:21

Alexander

59,041
12
98
151

Oh sorry, I realize I phrased my question very poorly. What I meant was I have a date field. It might have the format mm-dd-yyyy or it might have a format mm/dd/yyyy. I would like to create a new column that is literally just the same but as text with the same format. I'll update my original question as appropriate. – Vincent Nov 29 '16 at 06:39
That's ... a completely different question from what you asked lol. Like not even close. – Alexander Nov 29 '16 at 06:40
Right. Sorry. I remember trying to use the TEXT formula, but it seems like you can't just specify using the same format. – Vincent Nov 29 '16 at 06:43
TEXT is still the way to do this, but you need a way of determining the format on a string per string basis. – Alexander Nov 29 '16 at 07:00

score 53 · Accepted Answer · answered Aug 31 '17 at 08:03

53

Use the TEXTJOIN function. Give an empty delimiter, TRUE for the ignore empty, the referenced cell as text1, and don't supply anything for text2.

=TEXTJOIN("",TRUE,A1)

Done.

answered Aug 31 '17 at 08:03

Chindraba

820
1
18
19

2

A nasty trick, but far the best answer! (I've Tried all others, but none really worked well) – Richard de Ree Jul 14 '18 at 16:16
5

@Richard I think learning Perl taught me to look for the 'nasty' tricks which make life easier in the end. – Chindraba Jul 15 '18 at 21:54

score 17 · Answer 3 · answered Nov 17 '19 at 01:45

17

=TEXT(now(),"yyyy-MM-dd hh:mm:ss")

This cell is now a string.

answered Nov 17 '19 at 01:45

Alan

9,167
4
52
70

score 9 · Answer 4 · answered Aug 31 '17 at 05:53

9

copy the column to another column
select the copied column
go to menu 'Format' --> 'Number' --> 'Plain text'

answered Aug 31 '17 at 05:53

fstang

5,607
4
25
26

I used this with my Google spreadsheet. The dates look 1 on on the sheet but but reading them comes up with something different. Just select the rows you want to fix and the do # 3 above. – slindsey3000 Sep 26 '18 at 19:23

score 5 · Answer 5 · edited Nov 13 '19 at 12:43

5

to concatenate text to date you can do this:

=CONCATENATE("today is: ", TEXT(HOY(), "dd-mm-yyyy"))

edited Nov 13 '19 at 12:43

Aki

3,709
2
29
37

answered Mar 12 '19 at 15:17

user1918578

91
1
3

Max Makhrov · Answer 6 · 2021-06-18T11:42:09.800

there's no way to detect the format of dates in google sheets. But you may use getDisplayValues property. Paste this code into script editor:

function repeatAsText(A1Notation, sheet) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  if (sheet) { sheet = ss.getSheetByName(sheet); }
  else { sheet = ss.getActiveSheet(); }
  var range = sheet.getRange(A1Notation);
 
  return range.getDisplayValues();
}

and then use it as ususl formula:

=repeatAsText("A1:A3")

A1Notation -- string like "A1" or "B3:AD15".

sheet -- name of sheet, like "Sheet1". It is optional, use ActiveSheet if omitted

How to convert a date to a string in Google Sheet

6 Answers6

Linked