Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a column in my spread sheet that is in YYYY-MM format and this is a dimension in a bar chart. It is in a string format but how can I convert it to a date format?
I have below format in the data load editor
DateFormat='MM/DD/YYYY';
Thank you!
you can use the Date and Date# function.
First use Date# which reads a string and interpets as a date:
Date#( [FieldName],'YYYY-MM') as NewDate
Then you can change the date format display using Date() . Wrap it around the Date# so the result of Date# flows right into the Date function as follows:
Date( Date#( [FieldName],'YYYY-MM') , 'MM/DD/YYYY' ) as NewDate
There are couple of ways you can do this:
1) Date#(DateField, 'YYYY-MM') as DateField
2) MakeDate(SubField(DateField, '-', 1), SubField(DateField, '-', 2), 1) as DateField
HTH
Best,
Sunny
hi
Basically you need a string that contains all the three information required for a date ie Year , Month and Day otherwise,by using the following conversion ,
Date(Date#([Your Date Field],'YYYY-MM') , 'MM/DD/YYYY') is always going to use 1 as the day for your dates
ex:
2015-01 will be 01/01/2015
2015-02 will be 02/01/2015
so on..
hth
Sasi
Thank you all for your help!
I have one more question about the date format.
My chart has month/year on the axis but it only shows the months that have corresponding amount. Is it possible to format so that it shows other months that do not have any data? Or would I have to create months on the spreadsheet manually?