Ask Your Question
0

compare a date with QQ format to text [closed]

asked 2014-03-27 19:54:32 +0200

MarcVank gravatar image

updated 2015-09-09 20:38:19 +0200

Alex Kemp gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 15:48:03.252538

2 Answers

Sort by » oldest newest most voted
3

answered 2014-03-28 01:35:26 +0200

w_whalley gravatar image

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.

edit flag offensive delete link more
1

answered 2014-03-28 08:49:23 +0200

MarcVank gravatar image

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!

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-03-27 19:54:32 +0200

Seen: 303 times

Last updated: Mar 28 '14