Convert year and quarter columns into a single date column

[GoogleCodeExporter]

I am looking at some economic data (link below) which has 2 columns to describe 
the period for when the data was compiled.

This has, on some sheets, a quarter column and a year column, and on others, a 
month column and a year column.

Period  Year    Region
Q1  1983    North
Q1  1983    Yorks&Humb

Month   Year
Jan 1983
Feb 1983
Mar 1983

I'd like to be able to process the period / year and month / year columns into 
a single date column if possible. It's not obvious how to do that, and I can't 
seem to find out how.

Link is:

http://www.lloydsbankinggroup.com/media/excel/2010/04_11_10_historic_data.xls

Thanks

Original issue reported on code.google.com by gareth.d...@gmail.com on 19 Nov 2010 at 1:54

[GoogleCodeExporter]

Please use the mailing list for questions.  The issues database is for bug 
reports and enhancement requests.

For your first example, you could use "Add a new column based on this column" 
based on the Year column and use an expression similar to

(value+"-"+["01","04","07","10"][(cells['Period'][1]).toNumber()]+"-01").toDate(
)

This basically takes the quarter number and uses it to look up into a little 
array of month numbers and then convert the whole string to a date.

Original comment by tfmorris on 3 Mar 2012 at 7:17

• Changed state: Invalid