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

[DS_London]

I have an XLL which I have coded up, and some of the functions defined therein return dates, that is they return doubles which represent serial dates. They appear in the spreadsheet in General format (eg 43298).

If I enter the formula =TODAY() in a cell that was previously empty, I get today's date displayed and the cell's format has been changed to Date. That is also true if I enter =TODAY() + 1, but not if I enter =2 * (TODAY()/2).

So, how does Excel decide which format to apply? Can I somehow tap into this decision-making to get the doubles from my XLL UDF displayed as dates? Typically UDFs are not allowed to change the format of the calling cell.

(cf UDFs written in VBA, where you use a Date type, or return a Variant of type VT_DATE).

[#2]

@DS_London 

Here is a workbook with a list of options to help you decide.

If anything more specific is required please let me know.

If the post helped you, please mark it as the "correct answer" so that others in this forum can benefit from it too.

Thanks, I hope I could help.

Niolino

I know I don't know (Socrates)

[#3]

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).