Concatenate A Date – Make it show correctly.

Earlier this week a manager in my department brought over a person who I have never met. Apparently this individual had an Excel “problem” and I am the go to guy when someone has and Excel “problem”.

Now that’s not a bad role to have. In fact I consider it job security.

The problem he was having was with a file that he was trying to send up to a mainframe job. He had data in a worksheet and was concatenating the fields to get the data in the correct order for the upload. However one of the fields he was concatenating was a date field.

This problem is very common. The concatenated date was showing as 40910 (a serial date) instead of 01/02/2012. He was at a loss on how to make the date display the way he wanted.

Here is an example of what he was looking at and how I solved his problem. In cell B2 he had his date formatted as mm/dd/yyyy. However when joined the fields together in cell D2, the results were not what he wanted. The date was displayed in a serial format.

	A	B	C	D	E
1	Color	Date	Color 2	Concatenate	Formula in Column D
2	Blue	01/02/2012	Green	Blue40910Green	=CONCATENATE(A2,B2,C2)
3	Blue	01/02/2012	Green	Blue01/02/2012Green	=CONCATENATE(A3,TEXT(B3,"mm/dd/yyyy"),C3)

Well the solution to this is to convert the date field to text and then to format the field as mm/dd/yyyy. My solution is shown in cell D3.

=CONCATENATE(A3,TEXT(B3,"mm/dd/yyyy"),C3)

Converting the date to text then formatting it resolved his problem. In the end my manager was pleased and he was pleased.

Sometimes it’s good to be me.