CODEWITHSUNDEEP
google-sheetsdate-formatting
user2240778
user2240778

Reputation: 309

Display text representation of a date as a formula result

I get this date format from Zapier 

2018-08-16T01:13:58

I use this formula to split and extract the first half of the split (the date).

=index(SPLIT("2018-08-16T01:13:58","T"),0,1)

Google Sheets displays the formula output as a number, e.g. 43328

If I format this cell manually, using the menu Format >> Number >> Date, Google Sheets will display it as the formatted date, e.g. 16/08/2018

If I use DATEVALUE() as such:

=datevalue(index(SPLIT("2018-08-16T01:13:58","T"),0,1))

then the cell displays #VALUE!

Error DATEVALUE parameter '43328' cannot be parsed to date/time.

How can I write my formula such that it is displayed as formatted date, without having to format the formula's cell through the menu?

Upvotes: 0

Views: 1187

Answers (3)

ChrisD
ChrisD

Reputation: 319

You can use the TEXT function:

=TEXT(43328,"dd-mm-yyyy")

Upvotes: 1

user2240778
user2240778

Reputation: 309

After much searching this seems to work for me.

=regexreplace(SUBSTITUTE(SPLIT("2018-08-16T10:02:29","T") + "","-","/"), "^(\d+).(\d+).(\d+)$", "$3/$2/$1") & " " & regexreplace(RIGHT("2018-08-16T10:02:29", find("T","2018-08-16T10:02:29") -3), "^(?:(?:([01]?\d|2[0-3]):)?([0-5]?\d):)?([0-5]?\d)$", "$1:$2:$3")

Outputs 16/08/2018 10:02:29

Upvotes: 0

HaPhan
HaPhan

Reputation: 321

Date shows in google spreadsheet are formatted by google spreadsheet settings. If you want to change the datetime format of your sheet, you will have to change Locale setting to that of your need.

If you don't want to go over settings, you can set it manually by code.

First, you will need to put this formula =SPLIT("2018-08-16T01:13:58","T") + "" somewhere to get the date value. Here I assume you use cell "Z1"

Now, you will have to change the date format each time "Z1" changes it value. I use onEdit(e)

function onEdit(e) {
      var ss = SpreadsheetApp.getActiveSheet()
      if (range.getColumn() == 26 && range.getRow() == 1) { // if Z1 is modified
        var d = ss.getRange("Z1").getValue()
        var inputDate = Utilities.formatDate(d, "GMT+7", "dd/MM/yyyy")
        ss.getRange("B5").setValue(inputDate)
        }
      }

There you go. Each time Z1 change, which means the date value is inputted, the correct date + format will be reflected in B5.

Upvotes: 0

Related Questions