Description: TBH I don't know if this is a bug or a feature. Additionally I've been unable to find any (Excel) documentation on this kind of formula notation so I shamelessly hope someone on QA team can help me out here Steps to Reproduce: 1. Open a new spreadsheet doc 2. Enter =TODAY() into A1 3. Enter =TEXT(,A1) into B1 Actual Results: '42970' appears in B1 (or whatever the current date is). It works that way in MS Excel Expected Results: Err511 appears instead Reproducible: Always User Profile Reset: Additional Info: According to erAck this odd formula: =TEXT(,$address) means something like "if an argument is missing/unspecified, use its default value" i.e. it should be an equivalent of =TEXT($address, "@") where "@" is a general number format. Alas it won't work like that in Calc, only in Excel User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:54.0) Gecko/20100101 Firefox/54.0
Now I've tried w/ recent-ish master build and I no longer get 'Err.511' in B1, but an empty cell
I get and 'Err.501' 'Invalid character' with Version: 6.0.0.0.alpha0+ Build ID: ddf0d439d36686008b6de16d59c9a9068622f26c CPU threads: 4; OS: Windows 6.19; UI render: default; TinderBox: Win-x86@42, Branch:master, Time: 2017-08-18_23:34:11 Locale: es-ES (es_ES); Calc: CL Maybe this depens on the character used as separator for dates on the Locale, with Spanish '/'.
Hello, The format has to be precised. A1=42971 B1=TEXT(A1;"DD/MM/YY") > 24/08/17 with English (USA) pattern B2=TEXT(A1;"00000") > 42971 Have a fine day, Jacques
> The format has to be precised. > A1=42971 > B1=TEXT(A1;"DD/MM/YY") > 24/08/17 with English (USA) pattern Well it doesn't have to be specified in MS Excel. If it is not specified AND the formula is written in this odd missing arguments notation, the default (General, @) format is used It just won't work in Calc and this is a real issue for a customer who wants to migrate their spreadsheets from MSO + xlsx to LibO + ods (fwiw we already advised them to use =TEXT($address, "@") workaround) > Maybe this depens on the character used as separator for dates on the > Locale, with Spanish '/'. and yes, it depends on locale (or user settings, Tools > Options > LibO Calc > Formula), you have to use locale-specific argument separator
I don't see the problem (the result for me is "42971" today), and furthermore this is a very odd example.. what actually happens is that the first omitted argument is substituted with 0 and the second argument (the TODAY() result date serial number) is converted to string, so TEXT(,A1) evaluates to TEXT(0,"42971") The assumption that in Excel an omitted first argument would lead to the General format being used IMHO doesn't hold.
Ah, in 5.2.x the missing argument isn't accepted for TEXT() (and likely other fixed two parameters functions), seems that was changed for 5.3.
(In reply to m.a.riosv from comment #2) > I get and 'Err.501' 'Invalid character' You need to enter the expression with your current function argument separator instead of ',' comma.
So, this is fixed since 5.3.
(In reply to Eike Rathke from comment #7) > (In reply to m.a.riosv from comment #2) > > I get and 'Err.501' 'Invalid character' > You need to enter the expression with your current function argument > separator instead of ',' comma. Please excuse me, some days it is better not to get out of bed.