Split Date into Month and Year

[#1]

In my excel spreadsheet that I'm uploading into QV I have a "date received" column where all the dates are in this format: DD-MMM-YY (e.g. 07-Mar-13). I would like to have options in my multi box in QV to see just Month and Year. I'd like to compare data from different years by month (like what was the data like this March compared to March 2012), but I don't want to have to change anything in the spreadsheet because the file is used by many people and I don't want to change the format of that "Date Received" column. Is there any way just to create a Month and Year choice in my multi box that draws the information from DD-MMM-YY in the Date Received info?

[Gysbert_Wassenaar · Accepted solution]

That sounds like you selected Override Document Settings on the Number tab and set it to Date for the month field. The month() function returns an integer between 1 and 12 (actually, it creates a dual value with also a text value). If you format 1 as a date you get the date 31-12-1899. So, if that's the case unselect Override Document Settings.

[#3]

It worked, thank you!

[Sharad02]

Sometimes Date table might not have same alignment like for e.g in given example of Ship Date. If You try to used Year([Ship Date]) as Year it will not get Correct Output So instead of that in this situation we better to used SubField([Ship Date], '/',3) as Year_1  . 

You can see the difference from the images.Original Table

 

Split Year Updated Table