How can I display the current year in a cell?
=TODAY() and =NOW() each display 20/06/2013
=YEAR() displays Err:511
=YEAR(TODAY()) displays 05/07/1905
=YEAR(NOW()) displays 05/06/2013
=RIGHT(NOW(),4) displays 9306
I know that =YEAR(A3) displays 2013 when A3 contains =NOW(), but 1 don’t want to setup a cell that I can reference with the YEAR function.
1 Like
Part of the answer depends on whether or not you are going to be feeding the year into some other calculation.
If you need the actual 4-digit year in the cell, and no other numbers in that cell, you can use either =year(today()) or =year(now()) as the formula in the cell, and make sure the format of the cell is either “Numbers” tab | Category:Number | Format:General or Format:-1234. (A cell that hasn’t been formatted has the formatting code of “Numbers” tab | Category:Number | Format:General.)
If you just need to display the current date as a 4-digit year and you don’t care what the cell really contains, you can do the above to get the 4-digit year, or you can put the date in the cell (using =today(), or if you want to include seconds, =now() as the formula for the cell) and then format the cell to display as “Numbers” tab | Category:Date | Format: custom format code: YYYY (four upper-case Y’s).
What will not work is to get the year into the cell by =year(today()) and then use the “Numbers” tab | Category:Date | one of the mm/dd/yyyy formats because then the cell has the year but the formatting routine for displaying that cell’s value thinks the cell should be interpreted as if it had a date serial number, and 2013, for example, as July 5, 1905.
So, either extract the year (=year(today()) or =year(now()) to get just the year) and display that as a number, or get the date via =today() or =now() and display that value as a custom date format of YYYY.
The data in the cell is not necessarily what is displayed in a cell, particular when it comes to dates. A cell can contain 2013-06-20 but can be displayed as 2013. Format > Cells… > Numbers tab > Format Code of YYYY.
You should get 2013 with both =year(today()) and =year(now()) if you are typing in an unformatted cell (of course if the call is set to Date it will display a Date…)
That is what I get using LO 4.0.3.3 under Windows XP x86
Which LO version are you using? Under which OS? Maybe that was an old bug and it is already solved…
=YEAR(TODAY()) on LO 4.0.4.2 under Windows XP returns 2013 which is correct. It looks like user is using an old version of software or some problem in profile or similar.
Thank you everybody.
As suggested, the problem was caused by the cell in question being formatted as a date. When changed to unformatted, =year(now()) displays the correct year.
As Pedro pointed out the LibO version I made a test in 3.6.6 on XP @Pedro1’s formulas work as well as @oweng’s formatting information.