.ods - If formula not working + date formats

Discuss the spreadsheet application

.ods - If formula not working + date formats

Postby rrabbit » Sun Mar 22, 2020 7:49 pm

I am trying to select all the cells within a certain date range.

It will not update the date format that I have chosen for the cell (it stays in the old format).

The If formula is also not working. I wrote =IF(A2='t';G2=H2) and still nothing.

I have attached my file.
Attachments
20202203_1003.ods
(10.93 KiB) Downloaded 12 times
OpenOffice 4.1.6 Windows 7
rrabbit
 
Posts: 5
Joined: Sat Mar 14, 2020 7:59 pm

Re: .ods - If formula not working + date formats

Postby John_Ha » Sun Mar 22, 2020 7:52 pm

See Documentation/How Tos/Calc: IF function for examples of correct usage of the IF function.

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7774
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: .ods - If formula not working + date formats

Postby RusselB » Sun Mar 22, 2020 7:57 pm

For the IF formula, change the 't' to "t"
For the formatting, your cell A2 has a ' in front of the date, which means OpenOffice sees it as text, not a number, and, therefore, does not change it when applying a number format.
Removing the ' in A2 will make that a number that used the YYYY-MM-DD format, which you can change to match the format you want.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6062
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: .ods - If formula not working + date formats

Postby Zizi64 » Sun Mar 22, 2020 8:02 pm

Code: Select all   Expand viewCollapse view
=IF(A5="t";D5;"")


The string marker is the double quote mark, but not the apostrophe sign.

The proper structure of the IF statement:

=IF(the condition ; result when it is true ; result when it is false)
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9429
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: .ods - If formula not working + date formats

Postby John_Ha » Sun Mar 22, 2020 8:28 pm

Zizi64 wrote:=IF(the condition ; result when it is true ; result when it is false)

... where result must be a value and not a formula like "Set G2 to the value in cell H2"

IF

Returns one of two values, depending on a test condition.

Syntax: IF(test; value1; value2) where:

test is or refers to a logical value or expression that returns a logical value (TRUE or FALSE).
value1 is the value that is returned by the function if test yields TRUE.
value2 is the value that is returned by the function if test yields FALSE.

If value2 is omitted it is assumed to be FALSE; if value1 is also omitted it is assumed to be TRUE.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7774
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: .ods - If formula not working + date formats

Postby rrabbit » Sun Mar 22, 2020 8:37 pm

RusselB wrote:For the IF formula, change the 't' to "t"
For the formatting, your cell A2 has a ' in front of the date, which means OpenOffice sees it as text, not a number, and, therefore, does not change it when applying a number format.
Removing the ' in A2 will make that a number that used the YYYY-MM-DD format, which you can change to match the format you want.


The double quotes for the string worked. Calc applied the single quotes automatically to the string.

For the dates, nothing is working. Number, text, date formats, it will not recognise it in the If statement.

I changed the cell A2 to a word, and put that word into the =If(A2="hello";H2=D2;"") This produced a false result. When I simply put D2 as the positive result, it gave me the positive result. It breaks when I try to move one cell to another.

Dates in the same =If(A2=02/03/20;D2;"") does not work. This leads to the question: should a date be formatted with quotes or not? Without quotes, Calc takes away zeroes automatically within the date in the formula, causing it to not match A2.
OpenOffice 4.1.6 Windows 7
rrabbit
 
Posts: 5
Joined: Sat Mar 14, 2020 7:59 pm

Re: .ods - If formula not working + date formats

Postby Zizi64 » Sun Mar 22, 2020 8:44 pm

=If(A2=02/03/20;D2;"")


The 02/03/20 means two divisions: 2 divided by 3 and then the result divided by 20. That is not a Date value.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9429
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: .ods - If formula not working + date formats

Postby rrabbit » Sun Mar 22, 2020 10:28 pm

The 02/03/20 means two divisions: 2 divided by 3 and then the result divided by 20. That is not a Date value.


Ah, the date formtting is making more sense. Whilst Calc will change the format visually to the format I have chosen, the text inside the cell when opened for editing is still with the slashes.

=IF(A2="2020-03-02";D2;"") does not give me what I want either. How does Calc recognise dates in formulas?
OpenOffice 4.1.6 Windows 7
rrabbit
 
Posts: 5
Joined: Sat Mar 14, 2020 7:59 pm

Re: .ods - If formula not working + date formats

Postby RusselB » Sun Mar 22, 2020 10:39 pm

My recommendation when dealing with comparing to a specific date, is to use the DATE function in your comparison,
A2 displays as you see it, but the comparison has to be on the value of the date, rather than the actual date.
Your latest IF is looking for the string 2020-03-02 in A2, but A2 doesn't contain that string.
Try
Code: Select all   Expand viewCollapse view
=if(A2=date(2020;3;2);D2;"")

This should work, if you are using the date format of YYYY-MM-DD
Look at the DATE function in the help file for more information.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6062
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests