Solved Formula is returning the text format instead of date format

December 11, 2014 at 13:01:02
Specs: Windows 7
I am working on creating a identifier for my data but when I enter in the formula into Excel 2010, the results are not appearing as I expected.

Here is my formula:
=A2&(LEFT(C2,3))&LEFT(B2,3)&LEFT(F2,1)

where the cell A2 contains the date 11/23/1982

However, in the results, it is appearing as 30278.

The most frustrating part is I did the same formula in another spreadsheet and it worked as expected! The dates in that spreadsheet showed in the date format. Unfortunately, I did not save the other spreadsheet since that was just a draft to get the identifier.

message edited by luthientinuviel


See More: Formula is returning the text format instead of date format

Report •


✔ Best Answer
December 12, 2014 at 14:23:31
Untested...

=TEXT(A2,"m/d/y") & the rest of your stuff

or

=DATE(MONTH(A2)&"/"&DAY(A2)&"/"&YEAR(A2)) & the rest of your stuff

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
December 11, 2014 at 23:49:51
Cell should be formatted as date. Right click on the cell, select Format Cells. From Category list select Date.

Report •

#2
December 12, 2014 at 06:24:26
TeslaAC, that does not work because the cell that contains the result of the formula produces more information than just the date. The cell also contains the first three characters from two other cells and the first character from a third cell, which are all letters. So the result actually looks like 30278AbcDefG. Formatting a cell to Date does not work in a cell that contains alphabetic characters or most symbols (like ! or >). Just on the chance that someone suggested it, I already tried changing the cell format for the result cell prior to posting my question. The source cell (A2) is formatted correctly as a date. The same information in the cell with the formula provided above is not appearing as a date. That is where the problem lies.

message edited by luthientinuviel


Report •

#3
December 12, 2014 at 12:55:40
Have you tried using the TEXT function?

Report •

Related Solutions

#4
December 12, 2014 at 14:23:31
✔ Best Answer
Untested...

=TEXT(A2,"m/d/y") & the rest of your stuff

or

=DATE(MONTH(A2)&"/"&DAY(A2)&"/"&YEAR(A2)) & the rest of your stuff

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Ask Question