How is the TODAY() function able to format the cell as a Date?

Thanks NikolinoDE for the suggestion, but Data Validation doesn't address the question (that is a user-defined layer on top of the basic worksheet).

Try this: go to a blank cell in a new Workbook (which usually has the "General" number format). Enter the function =TODAY() ... what happens?

You get today's date as a readable date, and the cell number format has been changed to "Date" (using the region-specific short date format).

The TODAY() function returns a double (the type with which Excel stores all numbers, and how Excel understands dates ... days since 1-Jan-1900). Clearing the format (Ctrl-Shift-#) shows that number (eg 44024).

Why (and how) does Excel decide that the double returned from TODAY() should trigger additional processing and formatting of the target cell? Why does =TODAY() + 1 also trigger this reformatting, but =2 * (TODAY()/2.0) not? Note this only happens when the formula is first entered: not if the function is pasted in (Paste Special / Functions), or if the user subsequently changes the format of the cell manually. So perhaps it is part of Excel's processing and parsing of the function text entry, which has special cases for some functions and not others.

=NOW() also applies additional formatting to the cell ("Custom").

If you define a UDF in VBA as returning a Date type (or even a Variant of type Variant/Date), it still appears in the worksheet as a number, and the cell's number format is unchanged.

I would like my C++ XLL-defined functions to get a piece of this action for formatting cells for Dates, like TODAY and NOW do. Is it possible?

(NB. I am using the C-style Excel SDK for my XLL, not the C# route, so workarounds that plug into the Excel object model, while not impossible, would be somewhat excessive).