How to convert date to text with given format?

[artfulrobot]

I have data like this:

Date            Value
2016-01-02         12
2016-02-03          2
2016-05-12         22

It’s a big dataset.

What I want as an end result is a chart of sum(value) per quarter.

I made a new column that took the date string, converted it to an internal date, and then formatted that column like YYYYQ which results in displayed values like 2016Q1 and 2016Q2. However when I do a pivot table from that, it uses the underlying value (the specific date) not the formatted value, so it fails to group by quarter.

I could solve this problem many ways, here’s some ideas (none of which I can find a way to do!)

• if there was a DATE_FORMAT(<CELLREF>, <FORMATSTRING>) function that resulted in a text string, that would work.
• if there was a FORMATTED_VALUE(<CELLREF>) function, that would also work.
• if there was a Use formatted value option in pivot table, that would work.

Can anyone help?

Currently my work around is to copy the column into a text file and then paste it back over the top as text values!

---

[mikekaganski]

Use the @PYS’s solution. Wrt your direct question:

There is TEXT function that accepts the date format codes.

[artfulrobot]

Thanks, Mike! that’s it! If you want to write it as an answer I can tick it.

[PYS]

Hi

Sorry if I do not understand but it seems there is no need for an extra column. Just create the data pilot with “Date” & “Value”. Then click in the Date column and Data▸Group and outline▸Group▸tick Quarters

Regards

[artfulrobot]

Interesting, but does not appear to work on my LO (5.1.6): when I click in a Date cell in the created pivot table and click Data menu » Group and Outline » Group, I do not get that pop-up box; I just get another column in the pivot table duplicating the first.

[karolus]

See my answer…

[PYS]

@artfulrobot this occurs when the data is text and non-date format… e.g. '2016-02-03 (begins with a ')

[artfulrobot]

Thanks, understood.

[karolus]

hallo

it seems the initial error is on import the Data from (csv|text) - repeat this step and change the Column-header of the preview from Standard to Date ymd.

continue with proper answer of @PYS.