Ask Your Question

How to convert date to text with given format?

asked 2017-09-20 14:06:21 +0100

artfulrobot gravatar image

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!

edit retag flag offensive close merge delete



Use the @pierre-yves samyn's solution. Wrt your direct question:

There is TEXT function that accepts the date format codes.

Mike Kaganski gravatar imageMike Kaganski ( 2017-09-20 14:37:57 +0100 )edit

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

artfulrobot gravatar imageartfulrobot ( 2017-09-20 15:16:08 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-09-20 14:26:37 +0100

pierre-yves samyn gravatar image


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 DataGroup and outlineGroup▸tick Quarters

image description


edit flag offensive delete link more


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.

artfulrobot gravatar imageartfulrobot ( 2017-09-20 15:17:35 +0100 )edit

See my answer…

karolus gravatar imagekarolus ( 2017-09-20 15:25:43 +0100 )edit

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

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-09-20 15:30:06 +0100 )edit

Thanks, understood.

artfulrobot gravatar imageartfulrobot ( 2017-09-20 16:34:25 +0100 )edit

answered 2017-09-20 15:24:30 +0100

karolus gravatar image


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 @pierre-yves samyn.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-09-20 14:06:21 +0100

Seen: 1,807 times

Last updated: Sep 20 '17