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

0 votes

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.

Jan 5 in Others by Kithuzzz
• 28,700 points
74 views

1 answer to this question.

0 votes

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);
answered Jan 5 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
1 answer

Excel MATCH function is not working on an array but works once directed to the matched value

According to the definition here: support.microsoft.com/en-us/office/… if you do ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 53,160 points
91 views
0 votes
0 answers
0 votes
1 answer

How to open a huge excel file efficiently

A good example is using PIA's/Interop or ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 53,160 points
95 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
530 views
0 votes
1 answer

setuptools: build shared libary from C++ code, then build Cython wrapper linked to shared libary

There is a seemingly undocumented feature of setup that ...READ MORE

answered Sep 11, 2018 in Python by Priyaj
• 58,100 points
270 views
0 votes
1 answer

setuptools: build shared libary from C++ code, then build Cython wrapper linked to shared libary

There is a seemingly undocumented feature of setup that ...READ MORE

answered Sep 21, 2018 in Python by Priyaj
• 58,100 points
1,593 views
0 votes
1 answer
0 votes
1 answer

How to format numbers as lakhs and crores in excel/ google spreadsheet when the number could be negative too?

Excel formatting, in my opinion, can only ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 53,160 points
1,373 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP