Hi ,
I have a formula in Openoffice Calc that is not working in LibreOffice Calc… can anyone help me make it work?
It is a simple formula to calculate the day of the week from a date:
TEXT(WEEKDAY(DATEVALUE(TEXT(D39&"-"&$Data.K32&"-"&resultsYear,1))),“ddd”)
where resultsYear is a RANGE which has a value ‘2014’.
Data.K32 has the value ‘Mar’
D39 is ‘28’
There appears an Error:502 in the cell…
OpenOffice gives the correct value of ‘Sat’
Thanks in anticipation,
Allan
Anyone?
Thank you so far for the comments and tips!
The formula spans the year end and beginning of the following year and so defined range ‘resultsYear’ is 2015 for March but 2014 for October.
My formula originally (in OpenOffice) included “/” and I replaced this by “-” for LibreOffice 4.3.5.2.
The format of D39 (the day) is ‘number’ as is the defined range ‘resultsYear’.
So how do I get the DATEVALUE of the date “3-Mar-2015”?
For the sake of simplicity, let’s say cells B1, B2, and B3 contain the date components “28”, “Mar”, and “2014” respectively. Rather than TEXT(B1&"-"&B2&"-"&B3,1) perhaps try CONCATENATE(B1,"-",B2,"-",B3). The Format parameter value of 1 in the TEXT function seems odd according to the help page examples.
I think the issue is the ‘1’ as last parameter in the inner TEXT() function, I don’t know what that parameter means, as @Owen comment, as I know only text parameters with cell formats.
Attached a sample file tested with different version.
DateValueTest.ods
What seems works fine in all version and Aoo.
=TEXT(DATEVALUE(D39&"-"&$Data.K32&"-"&resultsYear),“ddd”)
There are open issues.
1: The formula is not correct with regard to the specifications of the used functions. This also applies to OO.
2: The 2014-03-28 wasn’t a saturday but a friday.
3: A gingerly rectified version of the formula can only work as intended if the running LibO will accept the pattern “D-MMM-Y” as a date. I couldn’t find this specified. It must be by default, however, as it cannot be added to the ‘Date acceptance patterns’.
4: A Calc document as heavily depending on fragile constructs should be redesigned before going to the level of adapting a single formula. See also 6:
5: It is not clear whether the values polled from D39 and ‘resultsYear’ (28 and 2014 as we are told) are text or numeric.
6: Assuming the concatenated string is “28-Mar-2014”, it is an amazing (obviously old) malfunction of LibreOffice (V4.3.5.1 used) and OpenOffice (V3.2.0 Portable used) as well that this string is actually accepted under US locale setting representing the 2014-03-28 (ISO 8601). Same with “28-Mar-14” interpreted as an abbreviation. But “32-Mar-14” is neither rejected as illegal (only 31 days in march!) nor interpreted as 2014-04-01 (next day after the 31st of march) consequently but as 1932-03-14. It’s ridiculous. We cannot rely on functions or processes distorting logic to such an extent.
7: My tests with AOO 3.2.0 Calc and with LibO 4.3.5 Calc didn’t show any differences with respect to the formula under discussion. Supplement: OpenOffice 3.2.0 did not reproduce the described behaviour on a second try. The “32-Mar-14” was now rejected as invalid argument (error code 502) which is accepable as I see it. May be my first report was based on a wrong view as I worked with both the programs at the same time. I thorougly reproduced the described behaviour with LibO4.3.5, however.
8: Advice: Redesign the sheets used in a way that you can keep unambiguous dates and work with them by logically sound functions. I cannot take responsibility for suggesting a solution basing on DATEVALUE() under the conditions explained.
I’ll be glad getting comments on my remarks (under 6: mainly).
Editing:
AOO seems to work more reliable with this respect. To be clear: This is not related to the missing result the questioner complained about.