Dates in fomula showing as whole number
 

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

Dates in fomula showing as whole number
 

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

"Dave" wrote:

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

Dates in formula showing as whole number
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Dave" wrote in message
...
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

Dates in formula showing as whole number
 

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Dave" wrote in message
...
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

Dates in formula showing as whole number
 

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")&")"


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Dave" wrote in message
...
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

Dates in formula showing as whole number
 

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" wrote:

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")&")"


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Dave" wrote in message
...
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

Dates in formula showing as whole number
 

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.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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" wrote:

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")&")"


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Dave" wrote in message
...
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

Dates in formula showing as whole number
 

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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" wrote:

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")&")"


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Dave" wrote in message
...
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

Dates in formula showing as whole number
 

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.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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" wrote:

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")&")"


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Dave" wrote in message
...
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

Dates in formula showing as whole number
 

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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" wrote:

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")&")"


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Dave" wrote in message
...
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

Dates in formula showing as whole number
 

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: EditCopy, hit Escape. Navigate to the open file where you want to use
the formula. Select any empty unused cell the do: EditPaste.

Does the formula still work?

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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" wrote:

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")&")"


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Dave" wrote in message
...
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