[Solved] Concatenate columns [dates]

Discuss the spreadsheet application

[Solved] Concatenate columns [dates]

Postby danjde » Fri Mar 19, 2010 5:58 pm

Hi friends,

I have a column X as entering a date (in italian format)
I would like the content of the column X automatically copy on column Y

I tried with the "concatenate" function but I get a strange result:

EXAMPLE after "concatenate"

I have a column on X date "26/02/2010"
the columnY returns "40235"

how to solve?

Many many thanks!
Last edited by Hagar Delest on Fri Mar 19, 2010 10:19 pm, edited 1 time in total.
Reason: tagged [Solved].
LibreOffice 6.3.5.2 on Linux Ubuntu 18.04, Codename: bionic (no dual boot)
User avatar
danjde
 
Posts: 36
Joined: Mon Mar 15, 2010 3:42 pm
Location: Italy, Verona

Re: concatenate columns [dates]

Postby Villeroy » Fri Mar 19, 2010 6:14 pm

Format that number as date. There are no dates in a spreadsheet. Any number can be displayed as date, any date/time can be displayed as a number. 26/02/2010 is day number 40235 since day zero on the time scale (30/12/1899 00:00).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29293
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: concatenate columns [dates]

Postby Robert Tucker » Fri Mar 19, 2010 6:16 pm

LibreOffice 7.x.x on Fedora 33 and Ubuntu 20.10 (Dual Boot)
User avatar
Robert Tucker
Volunteer
 
Posts: 1249
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: concatenate columns [dates]

Postby danjde » Fri Mar 19, 2010 6:43 pm

excuse me but i've not very experience on calc :oops:

1) i've formatted column X by dates

2) i've applied function "concatenate" on column Y (and formatted it by dates)

3) instert dates on column X

4) obtain "40235" on column Y

i don't undertand where i should put the code =TEXT(X1;"dd-mm-yyyy")

could combine it on the Y cell so: =CONCATENA(X1)text(Y1;"dd-mm-yyyy")

many thanks again ;-)
LibreOffice 6.3.5.2 on Linux Ubuntu 18.04, Codename: bionic (no dual boot)
User avatar
danjde
 
Posts: 36
Joined: Mon Mar 15, 2010 3:42 pm
Location: Italy, Verona

Re: concatenate columns [dates]

Postby danjde » Fri Mar 19, 2010 7:02 pm

SOLVED!

i just enter the value X1 in column Y1
thus:

=X1
LibreOffice 6.3.5.2 on Linux Ubuntu 18.04, Codename: bionic (no dual boot)
User avatar
danjde
 
Posts: 36
Joined: Mon Mar 15, 2010 3:42 pm
Location: Italy, Verona

Re: concatenate columns [dates]

Postby Villeroy » Fri Mar 19, 2010 7:13 pm

The result of TEXT(number;format-code) is a text, a sequence of digits and slashes. Text values sort alphabetically and you can not calculate with these dates. No matter what that text inis:
DAY(A1) =>30
MONTH(A1) =>12
YEAR(A1) =>1899

whereas 40235 is a date indeed.
DAY(40235) =>26
MONTH(40235) =>2
YEAR(40235) =>2010
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29293
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: concatenate columns [dates]

Postby Robert Tucker » Fri Mar 19, 2010 7:26 pm

Take a look at these screenshots and download the documents.

text-date-01.jpg
text-date-01.ods
(6.86 KiB) Downloaded 234 times
LibreOffice 7.x.x on Fedora 33 and Ubuntu 20.10 (Dual Boot)
User avatar
Robert Tucker
Volunteer
 
Posts: 1249
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: concatenate columns [dates]

Postby Robert Tucker » Fri Mar 19, 2010 7:27 pm

Take a look at these screenshots and download the documents.

text-date-02.jpg
text-date-02.ods
(6.91 KiB) Downloaded 220 times
LibreOffice 7.x.x on Fedora 33 and Ubuntu 20.10 (Dual Boot)
User avatar
Robert Tucker
Volunteer
 
Posts: 1249
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: concatenate columns [dates]

Postby danjde » Fri Mar 19, 2010 7:34 pm

many many thanks, i will try..

ciao!
LibreOffice 6.3.5.2 on Linux Ubuntu 18.04, Codename: bionic (no dual boot)
User avatar
danjde
 
Posts: 36
Joined: Mon Mar 15, 2010 3:42 pm
Location: Italy, Verona

Re: [Solved] Concatenate columns [dates]

Postby danjde » Tue May 18, 2010 10:55 am

i've tested and it works perfectly!

many many thanks ;)
LibreOffice 6.3.5.2 on Linux Ubuntu 18.04, Codename: bionic (no dual boot)
User avatar
danjde
 
Posts: 36
Joined: Mon Mar 15, 2010 3:42 pm
Location: Italy, Verona


Return to Calc

Who is online

Users browsing this forum: uzman26 and 18 guests