| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stop dates from showing as numbers - when formated as dates | Excel Discussion (Misc queries) | |||
| What is fomula for entering dates that are 7 days apart? | Excel Worksheet Functions | |||
| Fomula for number of days on each month from a date range | Excel Discussion (Misc queries) | |||
| Dates showing are not what I type | New Users to Excel | |||
| include maximum number in fomula | Excel Worksheet Functions | |||
Linear Mode
