Forum Discussion
Year Function returning #REF!; not sure why
I am actually a bit embarrassed for asking this, but I've been trying for 2 days now and cannot figure out why this is happening.
I'm using the dynamic tables (but this problem happens in or outside the tables). I'm just trying take a date and put the year, month, and day into separate columns.
| Date | Year | Month | Day |
| Friday, December 31, 2021 | #REF! | 12 | 31 |
| Friday, January 14, 2022 | #REF! | 1 | 14 |
I can open a different workbook and the YEAR function works, it just stopped working for this workbook.
Things I've noticed:
In the workbook that is correct, the formula is in caps- YEAR(A1); in the workbook where it is not working the formula wont stay in caps. When I hit enter, it changes to lowercase- year(A1).
In the one table the formula is working, there is still an error msg-- inconsistent calculated column formula; but all rows do have the exact same formula. Pasting an example didn't work, it didn't show the error msg or flag, but all the rows do have the same formula.
I'm already 15 tabs into this workbook and would hate to need to start over because of this.
Can I buy a clue? Anyone... Thanks in advance.
Ben
- Maybe a named range called "year"?
8 Replies
- You have a name conflict - there's named range on DD!A2:A13 that is called "Year". You should avoid using names that conflict with excel's function names.
- Thank you. I will definitely keep that in mind.
Ben Kilman wrote:In the workbook that is correct, the formula is in caps- YEAR(A1); in the workbook where it is not working the formula wont stay in caps. When I hit enter, it changes to lowercase- year(A1).
Do you have a macro called "year"?
- There are no macros in the workbook.
- Maybe a named range called "year"?
Can you upload your workbook? Would help to be able to see the formatting of your cell. Thanks. EDIT: FYI, caps does not matter for your formula. You can write it in all lower-case or proper-case and it'll work perfectly fine. Test it yourself.
- Yes I can, let me remove personal info first and then I will add it.
It will be Tab: Misc Purchases that shows both the issue and the year that is working but with the error msg.
