Split date format

Question

Asked 6 years, 4 months ago

Viewed 242 times

1

I have the following value 14/02/2018 13:53:12 in excel.

Cell with value is in text format.

When having concatenate the value the date above is like this: 43145,57861111111

Does anyone know how to solve?

The function I’m using is like this: =CONCATENAR("Codigo: ";A1;" - Data: ";B1)

2

Possible duplicate of Excel days function Dates does not work

– danieltakeshi

2018/02/15 at 19:29
Try to put in the format Data

– danieltakeshi

2018/02/15 at 19:33
Nothing. It hasn’t worked yet

– adventistaam

2018/02/15 at 19:35
Edit the question with the formula you are using to concatenate in the column D

– danieltakeshi

2018/02/15 at 19:37
I edited the question

– adventistaam

2018/02/15 at 19:42

2 answers

4

You must convert to text using the TEXT function, see below:

=CONCATENAR("Codigo: ";A1;" - Data: ";TEXTO(B1;"dd/MM/aaaa HH:mm"))

You are not signed in. Login or sign up in order to post.

by danieltakeshi • **3,960** points · Answer 1 · 2018-02-15T19:48:13+00:00

With the data:

+---+---------+-------------+
|   |    A    |      B      |
+---+---------+-------------+
| 1 | 9630638 | 43145,57861 |
+---+---------+-------------+

With the formula of concatenating =CONCATENAR("Codigo: ";A1;" - Data: ";B1) the result is Codigo: 9630638 - Data: 43145,5786111111

This is because Excel is using cell data B1as number, then just convert it to date format to get the date: =TEXTO(B1;"dd/mm/aaaa hh:mm:ss")

So you can concatenate with this formula: =CONCATENAR("Codigo: ";A1;" - Data: ";TEXTO(B1;"dd/mm/aaaa hh:mm:ss"))

And the result is: Codigo: 9630638 - Data: 14/02/2018 13:53:12