Date Displaying as Numbers when Concatenated

[purceld2]

Hi

I have a issue I cannot seem to resolve row K9 is not recognising the date format like the others. I have checked and it is formatted exactly the same is all the other calls so I am baffled.

Any ideas

Perry Hill Territory Spreadsheet.xlsm
	A		C	D	E	F					K
6	1				07/09/2019	18/09/2019					107/09/201918/09/2019
7	1				07/09/2019	18/09/2019					107/09/201918/09/2019
8	1				28/06/2019	28/07/2019					128/06/201928/07/2019
9	1				03/04/2019	18/04/2019					14355818/04/2019
10	1				19/01/2019	25/02/2019					119/01/201925/02/2019
11	1				01/11/2018	03/01/2019					101/11/201803/01/2019
12	1				21/07/2018	28/07/2018					121/07/201828/07/2018
13	1				10/02/2018	24/02/2018					110/02/201824/02/2018
14	1				29/07/2017	15/10/2017					129/07/201715/10/2017
15	1				26/03/2017	07/05/2017					126/03/201707/05/2017
16	1				08/03/2017	26/03/2017					108/03/201726/03/2017
17	1				07/11/2016	26/12/2016					107/11/201626/12/2016
Assignment

Cell Formulas
Range	Formula
K6:K17	K6	=A6&D6&E6&F6

Cells with Data Validation
Cell	Allow	Criteria
E6:F17	Date	between 01/01/2016 and 31/12/2022
A6:A17	Whole number	between 1 and 35

 

[jasonb75]

E9 is the only valid date in the table, the rest are all text strings that look like dates. For some reason, it is the only one that is actually working correctly, it is the others that are all wrong.
This is what I get when I copy your sheet to excel, which is what I would have expected.

Book1
	A				E	F					K
6	1				07/09/2019	18/09/2019					14371543726
7	1				07/09/2019	18/09/2019					14371543726
8	1				28/06/2019	28/07/2019					14364443674
9	1				03/04/2019	18/04/2019					14355843573
10	1				19/01/2019	25/02/2019					14348443521
11	1				01/11/2018	03/01/2019					14340543468
12	1				21/07/2018	28/07/2018					14330243309
13	1				10/02/2018	24/02/2018					14314143155
14	1				29/07/2017	15/10/2017					14294543023
15	1				26/03/2017	07/05/2017					14282042862
16	1				08/03/2017	26/03/2017					14280242820
17	1				07/11/2016	26/12/2016					14268142730
Sheet6

Cell Formulas
Range	Formula
K6:K17	K6	=A6&D6&E6&F6

 

[purceld2]

E9 is the only valid date in the table, the rest are all text strings that look like dates. For some reason, it is the only one that is actually working correctly, it is the others that are all wrong.
This is what I get when I copy your sheet to excel, which is what I would have expected.

Book1
	A				E	F					K
6	1				07/09/2019	18/09/2019					14371543726
7	1				07/09/2019	18/09/2019					14371543726
8	1				28/06/2019	28/07/2019					14364443674
9	1				03/04/2019	18/04/2019					14355843573
10	1				19/01/2019	25/02/2019					14348443521
11	1				01/11/2018	03/01/2019					14340543468
12	1				21/07/2018	28/07/2018					14330243309
13	1				10/02/2018	24/02/2018					14314143155
14	1				29/07/2017	15/10/2017					14294543023
15	1				26/03/2017	07/05/2017					14282042862
16	1				08/03/2017	26/03/2017					14280242820
17	1				07/11/2016	26/12/2016					14268142730
Sheet6

Cell Formulas
Range	Formula
K6:K17	K6	=A6&D6&E6&F6

Thanks

jasonb75​

for you prompt reply, still a bit lost, what do you suggest to resolve the issue please.

 

[jasonb75]

How are the cells formatted?

You appear to have achieved something that should be impossible, we need to figure out what you've done and how before we can do anything to resolve it.
At best, I would guess that the dates were entered into cells formatted as text before the data validation rule was set up, while E9 was entered into a cell formatted as date with or without validation.

Try formatting all of the date cells in the table as 'General'. Valid dates will change to 5 digit numbers, invalid dates will remain unchanged.

Next, click on an empty cell and copy (Ctrl c), then select all of the date cells in the table and use pastespecial - add (Ctrl Alt v, d, enter).

This should now show all of the dates as 5 digit numbers. If this has happened then we can proceed to resolve the issue, if not further investigation will be needed.

 

[purceld2]

How are the cells formatted?

You appear to have achieved something that should be impossible, we need to figure out what you've done and how before we can do anything to resolve it.
At best, I would guess that the dates were entered into cells formatted as text before the data validation rule was set up, while E9 was entered into a cell formatted as date with or without validation.

Try formatting all of the date cells in the table as 'General'. Valid dates will change to 5 digit numbers, invalid dates will remain unchanged.

Next, click on an empty cell and copy (Ctrl c), then select all of the date cells in the table and use pastespecial - add (Ctrl Alt v, d, enter).

This should now show all of the dates as 5 digit numbers. If this has happened then we can proceed to resolve the issue, if not further investigation will be needed.

The only thing that comes to mind that may of caused this, is that I have been experimenting with Pivot tables, and I think converting from range to table and back. I think it's best to scrap this sheet and start again and see if I have the same issue.

Really appreciate all you help

 

[purceld2]

The only thing that comes to mind that may of caused this, is that I have been experimenting with Pivot tables, and I think converting from range to table and back. I think it's best to scrap this sheet and start again and see if I have the same issue.

Really appreciate all you help

Sorry I will try what you suggested first

 

[jasonb75]

I think it's best to scrap this sheet and start again and see if I have the same issue.

I can't see the pivot tables being the cause of it, but anything is possible. If you do start again, it should come out the same as the mini sheet that I posted earlier, i.e. the concatenated column contains numbers instead of dates. Once you get to this point it's a simple change to the formula to get it how you want it.