Nested IF malfunction when used with WEEKDAY function

[markling]

Binary package hint: openoffice.org

The following Calc formula appears to have malfunctioned.

=IF(WEEKDAY(B24,2)=7,"Sunday",(IF(WEEKDAY(B24,2)=1,"Monday",(IF(WEEKDAY(B24,2)=2,"Tuesday",(IF(WEEKDAY(B24,2)=3,"Wednesday",(IF(WEEKDAY(B24,2)=4,"Thursday",(IF(WEEKDAY(B24,2)=5,"Friday",(IF(WEEKDAY(B24,2)=6,"Saturday","...")))))))))))))

I shall refer to this formula as the Saturday Formula.

What is expected to happen:

The formula should return the name of the day corresponding with the results of the WEEKDAY function applied to Cell B24. So if B24 has the date "3 Oct 2010", the WEEKDAY (applied using the Type value 2) function will return the value 7 and the formula will display the value "Sunday".

The IF function has been nested with each day of the week, so that if the first weekday value is not found, it looks for the second and so on until it has checked for all seven days of the week. If none of the days of the week are found (if, for example, Cell B24 is blank), then it should display the value "...".

What actually happens:

The formula displays the correct day for every day of the week. But if Cell B24 does not contain a date (for example, if it is blank) it displays the word "Saturday".

Further explanation:

The same problem does not occur if the "IF(WEEKDAY..." function is nested only 5 times. So the following formula, which I shall refer to as the Friday Formula, works correctly:

=IF(WEEKDAY(B24,2)=7,"Sunday",(IF(WEEKDAY(B24,2)=1,"Monday",(IF(WEEKDAY(B24,2)=2,"Tuesday",(IF(WEEKDAY(B24,2)=3,"Wednesday",(IF(WEEKDAY(B24,2)=4,"Thursday",(IF(WEEKDAY(B24,2)=5,"Friday","...")))))))))))

The Friday Formula correctly displays "..." if none of its nested IF functions are satisfied. The only significant difference it has to the Saturday Formula is one less IF function.

I was unable to repeat the malfunction by nesting a plain IF, nor by nesting an IF using the DAY function. So the following formula, which contains the same number of IF's as the Saturday formula, correctly delivers "..." if none of its IF functions are satisfied.

=IF(B1=1,"ONE",(IF(B1=2,"TWO",(IF(B1=3,"THREE",(IF(B1=4,"FOUR",(IF(B1=5,"FIVE",(IF(B1=6,"SIX",(IF(B1=7,"SEVEN","...")))))))))))))

(Reffering to Cell B1 in this formula for no other reason than this is where it was looking for a date).

The following formula, which also has the same number of IFs as the Saturday Formula, also correctly delivers "..." if none of its nested IFs are satisfied:

=IF(DAY(B1)=1,"ONE",(IF(DAY(B1)=2,"TWO",(IF(DAY(B1)=3,"THREE",(IF(DAY(B1)=4,"FOUR",(IF(DAY(B1)=5,"FIVE",(IF(DAY(B1)=6,"SIX",(IF(DAY(B1)=7,"SEVEN","...")))))))))))))

Note that its use of the DAY function seems to indicate that the problem is related to the nesting of the WEEKDAY function.

ProblemType: Bug
Architecture: i386
Date: Sun Oct 3 16:42:01 2010
DistroRelease: Ubuntu 9.10
InstallationMedia: Ubuntu 9.10 "Karmic Koala" - Release i386 (20091028.5)
Package: openoffice.org-core 1:3.1.1-5ubuntu1.2
ProcEnviron:
 PATH=(custom, no user)
 LANG=en_GB.UTF-8
 SHELL=/bin/bash
ProcVersionSignature: Ubuntu 2.6.31-22.65-generic
SourcePackage: openoffice.org
Uname: Linux 2.6.31-22-generic i686

[penalvch (penalvch)]

markling, thank you for reporting this bug and helping make Ubuntu better. Could you please attach the file that demonstrates this problem?

[Launchpad Janitor (janitor)]

[Expired for openoffice.org (Ubuntu) because there has been no activity for 60 days.]

[penalvch (penalvch)]

markling, the reason why one does not get the "..." as anticipated from the first formula has to do with how Excel handles:

=WEEKDAY(,2)

which equals 6. Both OpenOffice.org and LibreOffice keep compatibility with this result. Microsoft disclaims this issue at http://office.microsoft.com/en-us/excel-help/about-dates-and-date-systems-HP005200674.aspx?CTT=5&origin=HP005209336 noting:

"Because the rules that govern the way that any calculation program interprets dates are complex, you should be as specific as possible about dates whenever you enter them. This will produce the highest level of accuracy in your date calculations."

Despite this, this issue really is not a bug and requires more discussion, which should be done on an appropriate mailing list or forum. If you have a Microsoft support contract you can leverage, feel free to bring this to the attention of Microsoft and post the results to this bug. Otherwise, feel free to post to one or both of the following Microsoft forums and post a link of it to this bug:

http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads
http://answers.microsoft.com/en-us/office/forum/excel?page=1

apt-cache policy openoffice.org-writer
openoffice.org-writer:
  Installed: 1:3.2.1-7ubuntu1.1
  Candidate: 1:3.2.1-7ubuntu1.1
  Version table:
 *** 1:3.2.1-7ubuntu1.1 0
        500 http://us.archive.ubuntu.com/ubuntu/ maverick-updates/main i386
Packages
        500 http://security.ubuntu.com/ubuntu/ maverick-security/main i386
Packages
        100 /var/lib/dpkg/status
     1:3.2.1-7ubuntu1 0
        500 http://us.archive.ubuntu.com/ubuntu/ maverick/main i386 Packages

apt-cache policy libreoffice-calc
libreoffice-calc:
  Installed: 1:3.3.2-1ubuntu2~maverick1
  Candidate: 1:3.3.2-1ubuntu2~maverick1
  Version table:
 *** 1:3.3.2-1ubuntu2~maverick1 0
        500 http://ppa.launchpad.net/libreoffice/ppa/ubuntu/ maverick/main i386 Packages
        100 /var/lib/dpkg/status

Microsoft Office Excel 2003 (11.5612.5606)

apt-cache policy wine1.3
wine1.3:
  Installed: 1.3.18-0ubuntu1
  Candidate: 1.3.18-0ubuntu1
  Version table:
 *** 1.3.18-0ubuntu1 0
        500 http://ppa.launchpad.net/ubuntu-wine/ppa/ubuntu/ maverick/main i386 Packages
        100 /var/lib/dpkg/status

[Björn Michaelsen (bjoern-michaelsen)]

[This is an automated message.]
There are no new official OpenOffice.org releases in Ubuntu packaging anymore => Won't Fix

If the problem persists, please mark this bug as "also affects project Libreoffice" or "also affects distribution Libreoffice (Ubuntu)" if that has not happened already.

Please leave references to upstream OpenOffice.org bugs in place to allow cross pollination.