Can an Excel xll function indicate that the return value should be displayed as a date and not a number

Question

Under the hood, Excel "dates" are doubles, and the XLOPER structure appears to have no sense of date but merely xltypeNum (unlike Variants in VBA).

I have a straightforward XLL with a function that returns the current date as an LPXLOPER and requires no arguments (type "P"). I am using "P" rather than "B" as the return value because I have other functions that typically return dates but could also return errors.

DLLEXPORT LPXLOPER WINAPI epToday()
{
    static XLOPER xResult;// Return value

    xResult.xltype = xltypeNum;
    xResult.val.num = ExcelDateForToday(); //A function that returns a double for today's date

    return &xResult;
}

The calling spreadsheet shows a number for the return value (eg 40303). In the same manner that the built-in function TODAY() does, I would like to be able to instruct Excel to treat the double value it has returned as a date. Exists a method for achieving this?

Should I use a callback to modify the format for the caller cell since =TODAY() converts the cell's NumberFormat to "Date"? Excel appears to be doing this, for instance, if I recalculate a cell using the =TODAY() function, Excel will once more set the NumberFormat to Date.

Answer 1

To my knowledge, the only method to tell Excel that the value you're returning from an XLL function is a date is to use the TODAY() approach of setting the date format via a callback. It does, however, have a few shortcomings:

1. Performance (for very large numbers of calls, although I haven't checked this)
2. Requires special handling if the caller is not the worksheet
3. Does the wrong thing for =TODAY() - AnotherDate, but so does Excel!
4. Will override the format on every recalc - may annoy the user if another format is preferred

Without seeing the Excel source code, I'm not sure how Excel is able to magically avoid (4) by determining whether it is being called as part of the calculation cycle or not. You might create a wrapper function, such as =SetFormat(MyToday(), "yyyy-mm-dd"), to set the format and provide the user a choice (or to undo your format change as the callback would be queued second).

The callback is fairly straightforward in xlOil (disclaimer: I wrote it):

  XLO_FUNC_START(testToday())
  {
    CallerInfo caller;
    if (!caller.fullSheetName().empty()) // Check caller is a worksheet
      excelPost([=] 
      {
        excelApp().Range[caller.writeAddress().c_str()]->NumberFormat = L"dd-mm-yyyy"; 
      });
    std::tm buf; 
    auto now = std::time(0);
    localtime_s(&buf, &now); // Slightly labourious to get current date in C++
    return returnValue(buf);
  }
  XLO_FUNC_END(testToday);