| 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 |
| 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 | |||
Hybrid Mode
