Cell Format Issue Becky 27.09.2006 17:58:02 I get unexpected results when date formatting a worksheet cell in a MS Excel spreadsheet - in other words it looks like a calculation is trying to take place such that entering 1220 in the cell returns a date of 5/4/1903 and entering 12202006 0r 122006 returns a negative date displayed as ########. I have not experienced something like that before – and I use MS Excel way too much! The cells are formatted as dates. Is there a way I can enter the dates without the slashes?

RE: Cell Format Issue Marcelo 27.09.2006 18:27:02 hi Becky, Format as custom mdaaaa hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Becky" escreveu: [Quoted Text] > I get unexpected results when date formatting a worksheet cell in a MS Excel > spreadsheet - in other words it looks like a calculation is trying to take > place such that entering 1220 in the cell returns a date of 5/4/1903 and > entering 12202006 0r 122006 returns a negative date displayed as ########. I > have not experienced something like that before – and I use MS Excel way too > much! > > The cells are formatted as dates. > > Is there a way I can enter the dates without the slashes?

RE: Cell Format Issue Becky 27.09.2006 18:34:03 That did not work. If I enter 1220 I get 54Monday in the cell and in the function area it says 5/4/1903 If I enter 122006 I get 114Tuesday in the cell and in the function area it says 1/14/2234. I know what the m and d are for what are the aaaa? "Marcelo" wrote: [Quoted Text] > hi Becky, > > Format as custom mdaaaa > > hth > -- > regards from Brazil > Thanks in advance for your feedback. > Marcelo > > > > "Becky" escreveu: > > > I get unexpected results when date formatting a worksheet cell in a MS Excel > > spreadsheet - in other words it looks like a calculation is trying to take > > place such that entering 1220 in the cell returns a date of 5/4/1903 and > > entering 12202006 0r 122006 returns a negative date displayed as ########. I > > have not experienced something like that before – and I use MS Excel way too > > much! > > > > The cells are formatted as dates. > > > > Is there a way I can enter the dates without the slashes?

Re: Cell Format Issue Dave Peterson <petersod[ at ]verizonXSPAM.net> 27.09.2006 20:14:53 I bet it's a language difference. I'm guessing that the "a" in Marcelo's native language represents some variation of year (like Annual). Why the USA(?) excel treats it as a formatting character, I don't have a guess. But if you enter 12202006 into a cell, then excel has no way of knowing that you're entering a date. xl just figures you're typing a giant number 12,202,006. If you try to format that as a date, then excel will do what you say. But excel just uses a number to represent the number of days from a starting date (Dec 31, 1899 for most windows users). So if you entered a real date 12/20/2006 and format it with a General format, you'll see: 39071. And this is just the number of days since 12/31/1899. ==== But you do have some options to make data entry easier. You can use a worksheet event that Chip Pearson created that allows you to enter the data the way you want. But this event will convert it to a real date. http://cpearson.com/excel/DateTimeEntry.htm Or you could use another cell with a formula that converts it to a date. This works for me with my USA Settings (mdy): =--TEXT(A1,"00\/00\/0000") (but I have to format the cell as date, else I'll see that number (like 39071).) One more option is to preformat the cell/column as Text. then enter your values always using 8 characters. select the range (single column at a time) data|text to columns fixed width remove any lines that excel guessed and choose mdy and format it the way you like. Becky wrote: [Quoted Text] > > That did not work. > If I enter 1220 I get 54Monday in the cell and in the function area it says > 5/4/1903 > If I enter 122006 I get 114Tuesday in the cell and in the function area it > says 1/14/2234. > > I know what the m and d are for what are the aaaa? > > "Marcelo" wrote: > > > hi Becky, > > > > Format as custom mdaaaa > > > > hth > > -- > > regards from Brazil > > Thanks in advance for your feedback. > > Marcelo > > > > > > > > "Becky" escreveu: > > > > > I get unexpected results when date formatting a worksheet cell in a MS Excel > > > spreadsheet - in other words it looks like a calculation is trying to take > > > place such that entering 1220 in the cell returns a date of 5/4/1903 and > > > entering 12202006 0r 122006 returns a negative date displayed as ########. I > > > have not experienced something like that before – and I use MS Excel way too > > > much! > > > > > > The cells are formatted as dates. > > > > > > Is there a way I can enter the dates without the slashes? -- Dave Peterson

Re: Cell Format Issue Becky 27.09.2006 20:31:04 Thanks, Dave I will see if the user wants this code or not. This was very helpful. Thanks again "Dave Peterson" wrote: [Quoted Text] > I bet it's a language difference. I'm guessing that the "a" in Marcelo's > native language represents some variation of year (like Annual). Why the USA(?) > excel treats it as a formatting character, I don't have a guess. > > But if you enter 12202006 into a cell, then excel has no way of knowing that > you're entering a date. xl just figures you're typing a giant number > 12,202,006. > > If you try to format that as a date, then excel will do what you say. But excel > just uses a number to represent the number of days from a starting date (Dec 31, > 1899 for most windows users). > > So if you entered a real date 12/20/2006 and format it with a General format, > you'll see: 39071. And this is just the number of days since 12/31/1899. > > ==== > But you do have some options to make data entry easier. You can use a worksheet > event that Chip Pearson created that allows you to enter the data the way you > want. But this event will convert it to a real date. > > http://cpearson.com/excel/DateTimeEntry.htm > > Or you could use another cell with a formula that converts it to a date. > This works for me with my USA Settings (mdy): > =--TEXT(A1,"00\/00\/0000") > (but I have to format the cell as date, else I'll see that number (like 39071).) > > One more option is to preformat the cell/column as Text. > then enter your values always using 8 characters. > select the range (single column at a time) > data|text to columns > fixed width > remove any lines that excel guessed > and choose mdy > and format it the way you like. > > > Becky wrote: > > > > That did not work. > > If I enter 1220 I get 54Monday in the cell and in the function area it says > > 5/4/1903 > > If I enter 122006 I get 114Tuesday in the cell and in the function area it > > says 1/14/2234. > > > > I know what the m and d are for what are the aaaa? > > > > "Marcelo" wrote: > > > > > hi Becky, > > > > > > Format as custom mdaaaa > > > > > > hth > > > -- > > > regards from Brazil > > > Thanks in advance for your feedback. > > > Marcelo > > > > > > > > > > > > "Becky" escreveu: > > > > > > > I get unexpected results when date formatting a worksheet cell in a MS Excel > > > > spreadsheet - in other words it looks like a calculation is trying to take > > > > place such that entering 1220 in the cell returns a date of 5/4/1903 and > > > > entering 12202006 0r 122006 returns a negative date displayed as ########. I > > > > have not experienced something like that before – and I use MS Excel way too > > > > much! > > > > > > > > The cells are formatted as dates. > > > > > > > > Is there a way I can enter the dates without the slashes? > > -- > > Dave Peterson >

Re: Cell Format Issue Gord Dibben <gorddibbATshawDOTca> 27.09.2006 20:54:12 Becky You cannot format a cell to do this. Chip Pearson's site has event code that will look after the change as you enter the numbers. http://www.cpearson.com/excel/DateTimeEntry.htm Also check out his site to see how Excel handles dates. http://www.cpearson.com/excel/datetime.htm#SerialDates Gord Dibben MS Excel MVP On Wed, 27 Sep 2006 11:34:03 -0700, Becky <Becky[ at ]discussions.microsoft.com> wrote: [Quoted Text] >That did not work. >If I enter 1220 I get 54Monday in the cell and in the function area it says >5/4/1903 >If I enter 122006 I get 114Tuesday in the cell and in the function area it >says 1/14/2234. > >I know what the m and d are for what are the aaaa? > >"Marcelo" wrote: > >> hi Becky, >> >> Format as custom mdaaaa >> >> hth >> -- >> regards from Brazil >> Thanks in advance for your feedback. >> Marcelo >> >> >> >> "Becky" escreveu: >> >> > I get unexpected results when date formatting a worksheet cell in a MS Excel >> > spreadsheet - in other words it looks like a calculation is trying to take >> > place such that entering 1220 in the cell returns a date of 5/4/1903 and >> > entering 12202006 0r 122006 returns a negative date displayed as ########. I >> > have not experienced something like that before – and I use MS Excel way too >> > much! >> > >> > The cells are formatted as dates. >> > >> > Is there a way I can enter the dates without the slashes? Gord Dibben MS Excel MVP

Re: Cell Format Issue Marcelo 27.09.2006 21:50:02 Yes Dave you are right, Here in Brazil we speak portuguese and Year means ANO, Sorry for the mistake.. -- regards from Brazil Thanks in advance for your feedback. Marcelo "Dave Peterson" escreveu: [Quoted Text] > I bet it's a language difference. I'm guessing that the "a" in Marcelo's > native language represents some variation of year (like Annual). Why the USA(?) > excel treats it as a formatting character, I don't have a guess. > > But if you enter 12202006 into a cell, then excel has no way of knowing that > you're entering a date. xl just figures you're typing a giant number > 12,202,006. > > If you try to format that as a date, then excel will do what you say. But excel > just uses a number to represent the number of days from a starting date (Dec 31, > 1899 for most windows users). > > So if you entered a real date 12/20/2006 and format it with a General format, > you'll see: 39071. And this is just the number of days since 12/31/1899. > > ==== > But you do have some options to make data entry easier. You can use a worksheet > event that Chip Pearson created that allows you to enter the data the way you > want. But this event will convert it to a real date. > > http://cpearson.com/excel/DateTimeEntry.htm > > Or you could use another cell with a formula that converts it to a date. > This works for me with my USA Settings (mdy): > =--TEXT(A1,"00\/00\/0000") > (but I have to format the cell as date, else I'll see that number (like 39071).) > > One more option is to preformat the cell/column as Text. > then enter your values always using 8 characters. > select the range (single column at a time) > data|text to columns > fixed width > remove any lines that excel guessed > and choose mdy > and format it the way you like. > > > Becky wrote: > > > > That did not work. > > If I enter 1220 I get 54Monday in the cell and in the function area it says > > 5/4/1903 > > If I enter 122006 I get 114Tuesday in the cell and in the function area it > > says 1/14/2234. > > > > I know what the m and d are for what are the aaaa? > > > > "Marcelo" wrote: > > > > > hi Becky, > > > > > > Format as custom mdaaaa > > > > > > hth > > > -- > > > regards from Brazil > > > Thanks in advance for your feedback. > > > Marcelo > > > > > > > > > > > > "Becky" escreveu: > > > > > > > I get unexpected results when date formatting a worksheet cell in a MS Excel > > > > spreadsheet - in other words it looks like a calculation is trying to take > > > > place such that entering 1220 in the cell returns a date of 5/4/1903 and > > > > entering 12202006 0r 122006 returns a negative date displayed as ########. I > > > > have not experienced something like that before – and I use MS Excel way too > > > > much! > > > > > > > > The cells are formatted as dates. > > > > > > > > Is there a way I can enter the dates without the slashes? > > -- > > Dave Peterson >

Re: Cell Format Issue Dave Peterson <petersod[ at ]verizonXSPAM.net> 27.09.2006 21:57:28 Not a mistake--just an international difference. (I always assume USA settings. So it's not just your problem <bg>.) Marcelo wrote: [Quoted Text] > > Yes Dave you are right, > > Here in Brazil we speak portuguese and Year means ANO, > > Sorry for the mistake.. > > -- > regards from Brazil > Thanks in advance for your feedback. > Marcelo > > "Dave Peterson" escreveu: > > > I bet it's a language difference. I'm guessing that the "a" in Marcelo's > > native language represents some variation of year (like Annual). Why the USA(?) > > excel treats it as a formatting character, I don't have a guess. > > > > But if you enter 12202006 into a cell, then excel has no way of knowing that > > you're entering a date. xl just figures you're typing a giant number > > 12,202,006. > > > > If you try to format that as a date, then excel will do what you say. But excel > > just uses a number to represent the number of days from a starting date (Dec 31, > > 1899 for most windows users). > > > > So if you entered a real date 12/20/2006 and format it with a General format, > > you'll see: 39071. And this is just the number of days since 12/31/1899. > > > > ==== > > But you do have some options to make data entry easier. You can use a worksheet > > event that Chip Pearson created that allows you to enter the data the way you > > want. But this event will convert it to a real date. > > > > http://cpearson.com/excel/DateTimeEntry.htm > > > > Or you could use another cell with a formula that converts it to a date. > > This works for me with my USA Settings (mdy): > > =--TEXT(A1,"00\/00\/0000") > > (but I have to format the cell as date, else I'll see that number (like 39071).) > > > > One more option is to preformat the cell/column as Text. > > then enter your values always using 8 characters. > > select the range (single column at a time) > > data|text to columns > > fixed width > > remove any lines that excel guessed > > and choose mdy > > and format it the way you like. > > > > > > Becky wrote: > > > > > > That did not work. > > > If I enter 1220 I get 54Monday in the cell and in the function area it says > > > 5/4/1903 > > > If I enter 122006 I get 114Tuesday in the cell and in the function area it > > > says 1/14/2234. > > > > > > I know what the m and d are for what are the aaaa? > > > > > > "Marcelo" wrote: > > > > > > > hi Becky, > > > > > > > > Format as custom mdaaaa > > > > > > > > hth > > > > -- > > > > regards from Brazil > > > > Thanks in advance for your feedback. > > > > Marcelo > > > > > > > > > > > > > > > > "Becky" escreveu: > > > > > > > > > I get unexpected results when date formatting a worksheet cell in a MS Excel > > > > > spreadsheet - in other words it looks like a calculation is trying to take > > > > > place such that entering 1220 in the cell returns a date of 5/4/1903 and > > > > > entering 12202006 0r 122006 returns a negative date displayed as ########. I > > > > > have not experienced something like that before – and I use MS Excel way too > > > > > much! > > > > > > > > > > The cells are formatted as dates. > > > > > > > > > > Is there a way I can enter the dates without the slashes? > > > > -- > > > > Dave Peterson > > -- Dave Peterson