Excel – Converting a date from mm/dd/yyyy to its numerical value

microsoft excelworksheet-function

I am getting data from a reporting tool. The report contains Date and User columns (in that order). The tool returns dates in mm/dd/yyyy format.

Excel does not convert the imported data to a numerical date; it is stored as a text value (the raw data from the reporting tool is aligned to the left instead of to the right).

I need to concatenate User and the numeric date value (in that order). Using the raw imported data produces a result in the form userAmm/dd/yyyy instead of the needed format like userA42379. The concatenation of date as an unformatted numeric value only happens correctly if the raw data is in dd/mm/yyyy format (otherwise the input data is not interpreted as a date).

I tried to convert the dates from mm/dd/yyyy format to dd/mm/yyyy format using MID/LEFT/RIGHT and the number formatting in Excel but that didn't work. The date still appears in mm/dd/yyyy format when concatenated with another cell with text in it.

Any ideas?

Best Answer

You said the report contains Date and User columns (in that order).

You also said the tool returns dates in text format in the form of mm/dd/yyyy. Let's say in that case you have the folowing:

enter image description here

You need to concatenate User and Date value (in that order). This is what we are going to do in cell C2

Now I am confused as to which way you wish the concatenated date format to be. Do you want it in mm/dd/yyyy or dd/mm/yyyy do you want the date code to give an output such as userA 42379?

I will cover all three.

For concatenating whilst maintaining the date format (mm/dd/yyyy), use the following formula

=B2&" "&A2

enter image description here

The &" "& in the middle adds the space in between.

For concatenating the date in the format (dd/mm/yyyy), use the following formula

=B2&" "&MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,4)

enter image description here

For concatenating the date code such as 42379, use the following formula

=B2&" "&DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))

enter image description here

Related Question