I got this question from someone on the forums and thought it might be worth reposting to my blog for later reference as well.
The question:
Now this one made me scratch my head for a minute and then I fired up my trusty “Calculated Column Debugger”… Excel!!!Hello – I have an out-of-the box date column titled, “Departure Date” which renders values in the format, ‘DD/MM/YYYY’. I want to be able to filter by Year only so I believe the easiest way to accomplish this is to create a calculated column titled, Year and populate that column with the YYYY values from the Departure Date column. I tried using several formulas but so far have been unsuccessful. I’m currently using this formula, “=TEXT(YEAR([Departure Date]),”YYYY”)” for the calculation value and the value comes up as “1905”…? Help!
USEFUL TIP: ALMOST ANY FORMULA YOU CAN PUT TOGETHER IN EXCEL, YOU CAN COPY TO A SHAREPOINT CALCULATED COLUMN AND IT SHOULD WORK JUST FINE.
I copied the formula over to an Excel cell and put a date in another cell to reference and sure enough… I got 1905 too! Then just looking at the formatting options for the TEXT method and the YEAR method, I was able to deduce the answer thus…
The answer:
The problem is that you’re doubling up on methods by passing the result from YEAR to the TEXT method. Either of the following should work just fine:
- =TEXT([Departure Date], “yyyy”)
- =YEAR([Departure Date])
Because the TEXT method is expecting a DATE value to be passed, but the YEAR method is returning a TEXT value, it confuses the TEXT method which doesn’t know how to interpret the value and thus produces the weird result you saw.
Cheers
C
No comments:
Post a Comment
Comments are moderated only for the purpose of keeping pesky spammers at bay.