To get an actual Date value which you can format using normal number formatting...


`=DATEVALUE(MID(A1,1,10)) + TIMEVALUE(MID(A1,12,8))`

eg.

| | A | B |
|-|---|---|
|**1**|2016-02-22T05:03:21Z|2/22/16 5:03:21 AM|

* Assumes timestamps are in UTC
* Ignores milliseconds (though you could add easily enough)

The [`DATEVALUE()`](https://support.google.com/docs/answer/3093039) function turns a formatted date string into a value, and [`TIMEVALUE()`](https://support.google.com/docs/answer/3267350) does the same for times. In most spreadsheets dates &amp; times are represented by a number where the integer part is days since 1 Jan 1900 and the decimal part is the time as a  fraction of the day. For example, 11 June 2009 17:30 is about 39975.72917. 

The above formula parses the date part and the time part separately, then adds them together.

I found it much simpler to use `=SUM(SPLIT(A2,&quot;TZ&quot;))`

Format `yyyy-MM-dd HH:mm:ss.000` to see the date value as ISO-8601 again.

Try this 

    =CONCATENATE(TEXT(INDEX(SPLIT(SUBSTITUTE(A1,&quot;Z&quot;,&quot;&quot;),&quot;T&quot;),1),&quot;yyyy-mm-dd&quot;),&quot; &quot;,TEXT(INDEX(SPLIT(SUBSTITUTE(A1,&quot;Z&quot;,&quot;&quot;),&quot;T&quot;),2),&quot;hh:mm:ss&quot;))

Where **A1** can be a cell with ISO-8601 formatted string or the string itself.

Text data of variable length are being injected into tableview cell labels. In order for each cell height to be properly sized, I have implemented in `viewDidLoad()`:

    self.tableView.estimatedRowHeight = 88.0
    self.tableView.rowHeight = UITableViewAutomaticDimension

This estimates the height to be 88.0 pixels and should resize the height automatically if larger. It works perfectly for cells that *have yet* to be scrolled to (as `UITableViewAutomaticDimention` is called upon scrolling *to* the cell), but not for the cells that are initially rendered onscreen upon loading the table with data.

I have tried reloading the data (as suggested in many other resources):

    self.tableView.reloadData()

in both `viewDidAppear()` and `viewWillAppear()` and it did not help. I am lost.. does anyone know how to render the dynamic height for the cells loaded initially on screen?

Dynamic Height Issue for UITableView Cells (Swift)

I&#39;m porting a library from Ruby to Go, and have just discovered that regular expressions in Ruby are not compatible with Go (google RE2). It&#39;s come to my attention that Ruby &amp; Java (plus other languages use PCRE regular expressions (perl compatible, which supports capturing groups)), so I need to re-write my expressions so that they compile ok in Go.

For example, I have the following regex:

    `(?&lt;Year&gt;\d{4})-(?&lt;Month&gt;\d{2})-(?&lt;Day&gt;\d{2})`

This should accept input such as:

    2001-01-20

The capturing groups allow the year, month and day to be captured into variables. To get the value of each group, it&#39;s very easy; you just index into the returned matched data with the group name and you get the value back. So, for example to get the year, something like this pseudo code:

    m=expression.Match(&quot;2001-01-20&quot;)
    year = m[&quot;Year&quot;]

This is a pattern I use a lot in my expressions, so I have a lot of re-writing to do.

So, is there a way to get this kind of functionality in Go regexp; how should I re-write these expressions?




How to get capturing group functionality in Go regular expressions

I have a bunch of ISO-8601 formatted strings in a column of my sheet.  How can I get google sheets to treat them as Dates so I can do math on them (difference in minutes between two cells, for example)?  I tried just ```=Date(&quot;2015-05-27T01:15:00.000Z&quot;)``` but no-joy.  There has to be an easy way to do this.  Any advice?

ISO-8601 String to Date in Google Sheets cell

<p>I have a bunch of ISO-8601 formatted strings in a column of my sheet.  How can I get google sheets to treat them as Dates so I can do math on them (difference in minutes between two cells, for example)?  I tried just <code>=Date("2015-05-27T01:15:00.000Z")</code> but no-joy.  There has to be an easy way to do this.  Any advice?</p>


I can convert a [`java.util.Date`][1] to a [`java.time.Instant`][2] (Java 8 and later) this way:  

    Calendar cal = Calendar.getInstance();
    cal.set(Calendar.HOUR_OF_DAY, 8);
    cal.set(Calendar.MINUTE, 30);
    Date startTime = cal.getTime();
    Instant i = startTime.toInstant();

Can anybody let me know about the convert that instant to date with a specific date &amp; time format? i.e 2015-06-02 8:30:00

I have gone through api but could not find a satisfactory answer.


  [1]: http://docs.oracle.com/javase/8/docs/api/java/util/Date.html
  [2]: http://docs.oracle.com/javase/8/docs/api/java/time/Instant.html

How to convert an Instant to a date format?

I am using carbon but trying to get the first day of the month so I can run a report from the beginning of the month till the current day.

        $date = [
            &#39;start&#39; =&gt; new \Carbon\Carbon(&#39;last month&#39;),
            &#39;end&#39; =&gt; new \Carbon\Carbon(&#39;today&#39;)
        ];

The above code will show todays date back to same date in the previous month. But I want to get from the 1st to now.

Is there an easy way to do this like I am above? Cant find anything in the docs.

Carbon - get first day of month

I have a .Net MVC 5 application that is using Data Annotations, Entity-Framework Jquery 2.1.3 and Jquery UI 1.11.4. 

When I render an edit form with an input of type date using the UK format &quot;dd/MM/YYYY&quot;; the following error message appears when using Google Chrome:

&gt; The specified value &#39;10/10/2001&#39; does not conform to the required format, &#39;yyyy-MM-dd&#39;.  jquery-2.1.3.js:5317

**Model**

    public class MyModel
    {
        [Column(TypeName = &quot;date&quot;), DataType(DataType.Date), Display(Name = &quot;My date&quot;)]
        [DisplayFormat(DataFormatString = &quot;{0:dd/MM/yyyy}&quot;)]
        public string MyDate { get; set; }
    }

**Mark up**

    &lt;input class=&quot;text-box single-line&quot; data-val=&quot;true&quot; data-val-date=&quot;The field My date must be a date.&quot; id=&quot;MyDate&quot; name=&quot;MyDate&quot; type=&quot;date&quot; value=&quot;10/10/2001&quot; /&gt;

The value is set correctly in the input control but the date does not appear in the browser. I first thought this was an issue with jQuery as it is appearing the jQuery script file, but when testing in IE and Firefox everything is working fine.

I then assumed it was my regional setting in chrome as by default Chrome thinks everyone English is in America, I changed the regional setting to UK and still the same issue appears.

A simple fix would be to change the format in my model to universal but to UK users this is a little alien.

Is there a way to tell chrome that accept date formats in &quot;dd/MM/YYYY&quot;?

The specified value does not conform to the required format yyyy-MM-dd

I have a string in this format:

    var dateTime = &quot;06-17-2015 14:24:36&quot;

I am using moment.js and I am trying to convert it into `YYYY-MM-DD HH:mm:ss` -&gt; `2015-06-17 14:24:36`.

I have tried this method

    dateTime = moment( dateTime, &#39;MM-DD-YYYY HH:mm:ss&#39;,true).format(&quot;YYYY-MM-DD HH:mm:ss&quot;);

But getting dateTime as Invalid Date.

Format datetime to YYYY-MM-DD HH:mm:ss in moment.js

Is there a way to parse timezone in &quot;+00:00&quot; format with `datetime.strptime`? For instance:

    Python 3.4.3 (v3.4.3:9b73f1c3e601, Feb 24 2015, 22:43:06) [MSC v.1600 32 bit (In
    tel)] on win32
    Type &quot;help&quot;, &quot;copyright&quot;, &quot;credits&quot; or &quot;license&quot; for more information.
    &gt;&gt;&gt; from datetime import datetime
    &gt;&gt;&gt; datetime.strptime(&quot;12:34:56+0000&quot;, &quot;%X%z&quot;)
    datetime.datetime(1900, 1, 1, 12, 34, 56, tzinfo=datetime.timezone.utc)
    &gt;&gt;&gt; datetime.strptime(&quot;12:34:56+00:00&quot;, &quot;%X%z&quot;)
    Traceback (most recent call last):
      File &quot;&lt;stdin&gt;&quot;, line 1, in &lt;module&gt;
      File &quot;C:\Python34\lib\_strptime.py&quot;, line 500, in _strptime_datetime
        tt, fraction = _strptime(data_string, format)
      File &quot;C:\Python34\lib\_strptime.py&quot;, line 337, in _strptime
        (data_string, format))
    ValueError: time data &#39;12:34:56+00:00&#39; does not match format &#39;%X%z&#39;

Any ideas?

How to parse timezone with colon

I use the [SPLIT][1] function to split a string which looks something like `1.23/1.15`.

Right now it yields two cells, as it should. But how do I get a certain element from the result? I would like to do something like this:

    SPLIT(&quot;1.23/1.15&quot;, &quot;/&quot;)[0]

to extract `1.23`. What is the correct syntax for that?

I tried using the `INDEX` function, without success: `=INDEX(SPLIT(&quot;1.23/1.15&quot;, &quot;/&quot;), 0,0)`


  [1]: https://support.google.com/docs/answer/3094136?hl=en

Get first item from Split()

I would like to use Google Sheets for its convenient global access, but I really need to display numbers in Lakh and Crore (Indian style) format to avoid confusion among managers.

`150,000` should display as `1,50,000` and `12,000,000` should display as `1,20,00,000`.

I tried setting the format to `##,##,##,000` but my number still displays as `12,000,000`. I couldn&#39;t find anything [in the docs][1].

Does anyone know of a way to accomplish this?


  [1]: https://support.google.com/docs/answer/56470?hl=en

Displaying Lakhs and Crores in Google Sheets

I&#39;m trying to find a link which allows me to download a CSV formatted version of my Google Spreadsheet.  Currently I&#39;m using:

    https://docs.google.com/spreadsheets/d/DOCID/export?format=csv

This works great except that it only download the first Sheet.  My document has multiple sheets.  Does anyone know how to format this link so that it downloads either all the sheets or a specific sheet?  Something like:

    &amp;sheet=all
or

    &amp;sheet=3

Download link for Google Spreadsheets CSV export - with Multiple Sheets

I need to write a conditional format rule with a custom formula, that should trigger when the certain cell&#39;s (the cell in the 3rd row of the column of the current cell) value is `&quot;TODAY()&quot;`, and the current cell is `empty`. I know how to check the other cell, but is there a way to check the current cell&#39;s value in the same rule?

As you can see on this image, one column has a different color because the 3rd row of the column of the current cell contains the current date. And only empty cells are colored.
[![][1]][1]

Here is my rule:

`=and($3:$3=TODAY(), ????)`

It should apply to all cells in a range `A4:M10`

I need it to be the one rule, not combination of multiple rules. I need to put something to the place of `????`

In other words, I need to place the value described as &quot;Cell is empty&quot; in the custom formula as it&#39;s part.

Here is an example spreadsheet: [https://docs.google.com/spreadsheets/d/1vpNrX2aUg8vY5WGDDuBnLfPuL-UyrjFvzjdATS73aq8/edit?usp=sharing][2]


  [1]: http://i.stack.imgur.com/pVgUn.png
  [2]: https://docs.google.com/spreadsheets/d/1vpNrX2aUg8vY5WGDDuBnLfPuL-UyrjFvzjdATS73aq8/edit?usp=sharing

How to address the current cell in conditional format custom formula?

https://developers.google.com/apps-script/guides/rest/api#parameter_and_return_types

When using the Google Execution Api for Java, the ID of the spreadsheet isn&#39;t very easy to understand. Here is the code snippet:

    // Initialize parameters for that function.
    String sheetId = &quot;&lt;ENTER_ID_OF_SPREADSHEET_TO_EXAMINE_HERE&gt;&quot;;
    List&lt;Object&gt; params = new ArrayList&lt;Object&gt;();
    params.add(sheetId);

Any Ideas on how to locate this. I run the Sheets api to return the id of a SpreadSheet but it returns a link, and when I use the https://spreadsheets.google.com/feeds/spreadsheets/*STRING* or just the *STRING* itself I still get:
&quot;code&quot; : 404,
  &quot;errors&quot; : [ {
    &quot;domain&quot; : &quot;global&quot;,
    &quot;message&quot; : &quot;Requested entity was not found.&quot;,
    &quot;reason&quot; : &quot;notFound&quot;



How to do I locate a Google SpreadSheet ID?

Here is a standard way to serialise date as ISO 8601 string in JavaScript:


&lt;!-- begin snippet: js hide: false console: true babel: false --&gt;

&lt;!-- language: lang-js --&gt;

    var now = new Date();
    console.log( now.toISOString() );
    // outputs &#39;2015-12-02T21:45:22.279Z&#39;

&lt;!-- end snippet --&gt;

I need just the same output, but without milliseconds. How can I output `2015-12-02T21:45:22Z` ?&amp;nbsp;

How to output date in javascript in ISO 8601 without milliseconds and with Z

I&#39;m trying to format a Postgres date representation into a ISO 8601 string. I&#39;m assuming that there is a Postgres function that can do it, but I found the documentation short on examples. 

My query is 

    SELECT
      now()::timestamp

which returns

    [{{2016, 8, 9}, {3, 56, 55, 754181}}]

I&#39;m trying to get the date into a format that looks more like 
`2016-8-9T03:56:55+00:00`. 

What changes do I need to make to my query to make that happen? Thanks for your help.

Turn postgres date representation into ISO 8601 string

I am trying to use the `Duration` class instead of `long`. 
It has superior literal syntax. I like its flexibility, though it looks weird.

&quot;PT10S&quot; means 10 seconds, what is the problem to accept &quot;10 seconds&quot;?!
Okay never mind.

I am just curious why PT prefix has been chosen (not &quot;DU&quot; e.g.) and why any prefix is better here rather than nothing?
 

Content Type	Original Author	Original Content on Stackoverflow
Question	Bob Kuhar	View Question on Stackoverflow
Solution 1 - Datetime	rcoup	View Answer on Stackoverflow
Solution 2 - Datetime	Chris Bandy	View Answer on Stackoverflow
Solution 3 - Datetime	Akshin Jalilov	View Answer on Stackoverflow

ISO-8601 String to Date in Google Sheets cell

Datetime Problem Overview

Datetime Solutions

Solution 1 - Datetime

Solution 2 - Datetime

Solution 3 - Datetime

How to get capturing group functionality in Go regular expressions

Dynamic Height Issue for UITableView Cells (Swift)

Attributions