Dates in fomula showing as whole number

[Dave]

I have a fomula in a cell that takes the name of a person (from cell 2B),
their License number (from another cell 2C)and the Date that License Expires
(From cell 2D). The expire date in "2D" is either the word "none" or a date
that that persons license needs to be renewed.
Those instructors with "None" come out in the calculated field fine, however
the ones with dates come back as whole numbers, Example 8/6/10 shows 40396.
any help will be appreciated

 

[Eduardo]

Hi,
You need to change the format of that cell or column, highlight the cell or
the column, right click on the mouse format cells, choose date

 

[Bernard Liengme]

That is because dates are stored as numbers in Excel
See http://www.cpearson.com/excel/datetime.htm
40396 is Aug 6, 2010
All you need to is format the cell as a date

BTW; It is always a good idea to show the formula that is given a problem
when getting help room a newsgroup. Also it is more general to talk of cell
B2 not 2B.
best wishes

 

[Dave]

It is formatted as a "date" here is the formula =(C2&","&" "&D2&" "&E2&"
"&T2&" "&"("&"Expire"&" "&U2&")")
C2 is the lastname, D2 is the first name, E2 is middle, T2 is Certificate
number, and U2 is the expire date.
Thanks again to all

 

[T. Valko]

The problem is becuse you're concatenating a bunch of cells together the
result of the formula is a TEXT string.

Try it like this: (all on one line)

=C2&","&" "&D2&" "&E2&" "&T2&" (Expire"
&" "&TEXT(U2,"m/d/yy")&")"

 

[Dave]

I tried:
=C2&","&" "&D2&" "&E2&" "&T2&"(Expire"&" "&TEXT(U2,"m/d/yy")&")"
it now returns FALSE?
Did I goof something else up?
Thanks

 

[T. Valko]

it now returns FALSE?

Hmmm...

Using this data:

C2 = Public
D2 = John
E2 = Q
T2 = 1000
U2 = 8/6/2010

And this formula:

=C2&","&" "&D2&" "&E2&" "&T2&" (Expire"
&" "&TEXT(U2,"m/d/yy")&")"

I get this result:

Public, John Q 1000 (Expire 8/6/10)

There's no way that formula can return FALSE.

 

[Dave]

That is correct.
Here again is the formula I copied and pasted it:

=C2&","&" "&D2&" "&E2&" "&T2&"(Expire"&" "&TEXT(U2,"m/d/yy")&")"

Here is the results:

FALSE

Thanks

 

[T. Valko]

I'm baffled. All my knowledge of Excel tells me that it is impossible for
that formula to return FALSE. The formula isn't doing any comparative
testing that would return a logical value.

Is that formula part of a larger formula?

Try it again in a new empty file just as a test.

There is supposed to be a space between T2&"( and Expire, but that won't
cause a problem.

 

[Dave]

I too am baffled! I did it and a new book/sheet and it works fine!
I rechecked it and it still will not work in the book I am working on.
The formula is not part of a bigger one that I can see.
Thanks

 

[T. Valko]

Try this and see what happens.

Have the file where you want to use this formula open.

Open a new empty file and enter the formula in some cell. Use the same cell
references.
Verify that the formula works and you get the correct result.
Copy the formula *from the formula bar*. To do this select the cell with the
formula. Use your mouse and highlight the formula *in the formula bar*. Then
do: Edit>Copy, hit Escape. Navigate to the open file where you want to use
the formula. Select any empty unused cell the do: Edit>Paste.

Does the formula still work?