CODEWITHSUNDEEP
excelexcel-formula
Gaus Mohd
Gaus Mohd

Reputation: 11

Excel formula converting date into number

I have data in various columns in excel. I have used a Concatenate function to merger all the required data. Data is reflecting property for all the fields except Date. It is reflecting date as a number

I am using it Excel and not VBA so don't want any coding.

ID abx <GO> BXT 5985 100 43672 <GO> <Print>

Above is the output of concatenating formula. Date i.e. 07/26/19 is reflecting as 43672

The output of excel formula i.e. concatenate should reflect below output.

ID abx <GO> BXT 5985 100 07/26/19 <GO> <Print>

The date should reflect instead of a number.

Upvotes: 1

Views: 3542

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

The concatenation is treating the date like text so it's losing any formatting that the cell has applied to it.

07/29/19 is represented by the number 43672 as that's the number of days since 1/1/1900.

To show the number as date use something like =B3 & " " & TEXT(C3,"mm/dd/yyyy") where C3 contains your date.

Upvotes: 1

Related Questions