Compare a date with QQ format to text

[MarcVank]

I searched a lot, but couldn’t find the right solution;

specs: libreoffice Version: 4.2.2.1 / Build ID: 420m0(Build:1) / Dutch / Ubuntu 13.10

In Calc I have a cell with a date value, which I format to show in which quarter it fell. Next I have to compare this value of the cell in a sum.if-function to a text-cell but I can’t figure out how to compare them to get a TRUE-result.

The value of the cell is e.g. 01-01-2014, which is shown as “1ste kwartaal” (Dutch for “1st quarter”) by formating this cell with a date format of “QQ”. This cell, with the generated “1ste kwartaal” value, has to be compared with a cell with the text-value “1ste kwartaal” and the only outcome i get is FALSE…

Does anybody know how it’s done?

cheers!
Marc

[w_whalley]

If cell A1 contains the date 2014-01-01 formatted as QQ and cell B1 contains the text 1st quarter, then the formula =IF(TEXT(A1,"QQ")=B1) returns TRUE. It even works if cell A1 has a different format.

[MarcVank]

Thanks for your reply!

First i got an 501 error, but that was caused by the “,” seperator. In my locale I have to use “;” as a seperator.
Once that was solved it worked like a charm!

This is gonig to save me from making a messy lookup-table.

Thanks again!