Ask Your Question

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

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

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


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

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


Working, thank you! gravatar ( 2018-12-04 14:32:36 +0100 )edit

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


Thank you! gravatar ( 2018-12-04 14:32:52 +0100 )edit

Question Tools

1 follower


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

Seen: 12 times

Last updated: Dec 04