Ask Your Question
0

Calc: If-Statement pulling data from multiple cells - date is not shown correctly [closed]

asked 2018-12-04 14:02:35 +0100

patrick.de gravatar image

updated 2018-12-04 14:05:02 +0100

Hi,

many thanks for the support provided here by everyone!

My issue: I want to write names and birth dates of people in a sheet. I am using a dropdown list (Data->Validity) in cell A1 to indicate how many people are about to be entered. A2 to An then read "Person 1, Person 2, ... Person n". B2 to Bn contain their names. C2 to Cn their birth date.

In D1 I want to create a letterhead. The letterhead should contain each persons name and birthdate, and the wording should be according to the number of people (for one person, Concering "name", born on "birth date"; for multiple people "Corning the people ..."). For this I use an IF-statement:

=IF(A1=1;"Concerning "&B2&", born"&C2)&IF(A1=2;"Concering the persons "&B2&", born"&C2&" and"&B3&", born"&C3","")

The IF-statement works, the problem is, that the date is not displayed correctly. LO outputs the number it uses internally (I learned that LO stores dates internally as numbers, as is explained here) instead of a birth date. C2 to Cn are formatted correctly as date. Formatting D1 as date didn't do the trick, none of the formatting options for D1 make the date display correctly.

Any help is appreciated, thank you!

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-12-04 15:17:32.822958

2 Answers

Sort by » oldest newest most voted
0

answered 2018-12-04 14:09:00 +0100

erAck gravatar image

updated 2018-12-04 14:09:33 +0100

The & operator works with the cell content, not its formatted display string, and as dates are formatted date serial numbers that's what it takes. Instead of C2 use TEXT(C2;"YYYY-MM-DD")

edit flag offensive delete link more

Comments

Working, thank you!

patrick.de gravatar imagepatrick.de ( 2018-12-04 14:32:36 +0100 )edit
0

answered 2018-12-04 14:09:12 +0100

Mike Kaganski gravatar image

Use TEXT spreadsheet function to format date. See Number Format Codes for details.

edit flag offensive delete link more

Comments

Thank you!

patrick.de gravatar imagepatrick.de ( 2018-12-04 14:32:52 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2018-12-04 14:02:35 +0100

Seen: 12 times

Last updated: Dec 04