When a calculation field refers to a field in another record or table, the field cannot be stored and cannot have a permanent index. This keeps you from using it on the "many" side of a relationship.
If the field is never edited after initial data entry, you could use an auto-enter calculation to enter the date into a date field and now it would be an indexed field that will work for you.
If this field is subject to any chance it will be edited, then best bet is to write a script using set Field to update the field using data from the related table of dates. A script trigger on the data field can use OnObjectSave to perform this script.
It should also be possible to replace this table of related dates with a calculation that converts the ordinal number into date. That could also eliminate the "unstored calculation" challenge here.
Come to think of it, if an ordinal number of 1 represents January 1 of the specified year, this calculation would covert it into a date:
Date ( 1; ordinalNumberField ; year field )
the Date(1;ordinal fied;year field) idea works awesome!!
Thank you! Thank You! Thank You!
Have a great Week!