|Is there a static date formula?|
None that I know of.
Both =TODAY() and =NOW() are "dynamic" or "volatile" which means the function will recalculated whenever Excel calculates
To get a Static Date you need a VBA macro.
Or enter it from the key board using either
CTRL+; (SEMICOLON) for Date
CTRL+SHIFT+: (COLON) for Time