Nice easy one..... I have a date dd/mm/yyyy in Excel and want the list box properties to show only the Year.

[knightwriter]

Nice easy one for some kind individual..... I have a date format dd/mm/yyyy in Excel and want the list box properties to show only the Year. How can this be done? First post on the community here so I'm hoping for a return! Many thanks, Stephen.

[Anonymous]

Stephen

Try

     year(date#('DD/MM/YYYY')

Best Regards,     Bill

[knightwriter]

Thanks for the speedy reply Bill.

Do I c&p this into the Script Editor by the Date Load?

Regards,

S

[Anonymous]

Stephen

Yup.

Paste your script into this thread if you would like someone to look at it & suggest how ?

Best Regards,     Bill

[Bill_Britt]

Hi,

If you only want the year you would use.

year(Fieldname) as year

Bill

[#6]

I had this problem with an Excel report we got from "Hoopla". It looked like a date, but it wasn't a true Excel date, so none of the standard functions (as suggested above) worked for me. I tore my hair out trying to figure out what was wrong with my formulas, until I realised QV didn't know they were dates. Now I know, but I'm bald ;{

Here's what the 'date' looked like in the Excel (actually, a .CSV file):

7/29/2013 19:44

Here's what I put in my LOAD statement:

MakeDate(Mid([Join Date],index([Join Date],'/',2)+1,4),Left([Join Date],index([Join Date],'/',1)-1),Mid([Join Date],index([Join Date],'/',1)+1,index([Join Date],'/',2)-index([Join Date],'/',1)-1)) As NJDate,

Essentially, I went through the date, located the '/' delimiters, and used the "Index" function to find the positions dynamically (this adjusts automatically for single digit months or dates). Note that when you use Index like this, the 3rd parameter is the instance of the searched for text, so Index(text,'/',2) finds the SECOND instance of "/".

[knightwriter]

Thanks all, I got it it sorted.

@Kevin would it be rude to link rogaine?! Thanks for your help.

S