Problems with entry of Dates
Posted by Elbech on August 16, 2001 1:37 PM
Hi, this is my 1st posting here, so sorry if I ask a recurring question.
I'm running Excel2000 on Windows98, and in my spreadsheet dates are formatted as dd-mmm-yyyy.
All date-fields are labelled and are used in and for VBA macros.
When I wish to alter a date before recalculating, say, 16-Aug-2001, or 16/8/2001, or 8/16/2001, or 16/8/01, or 8/16/01, or 16-08-2001, or 08-16-2001, or 16-08-01, or 08-16-01, I only get an error message reading an infinite number of ########.
My regional settings in Control Panel is dd-MMM-yy.
Could anyone help me out here, please?
Thanks in advance,
Søren Elbech
Posted by Barrie Davidson on August 16, 2001 1:41 PM
Widen your columns, the date is there it is just too wide to fit the column (I think).
Regards,
Barrie
Posted by Elbech on August 16, 2001 1:42 PM
Dear Barrie,
I did, but no, it displays an infinite number of #s ..?
Thanks,
Søren
Posted by Barrie Davidson on August 16, 2001 1:46 PM
Okay, (sorry if I seem redundant) what happens if you select "Format|Column|Autofit Selection" from the main menu?
Barrie
Posted by Elbech on August 16, 2001 1:54 PM
Dear Barrie,
It's quite alright, I appreciate you trying to help me. :)
It doesn't help at all. There is no date at all, just a bunch of #. The wider I make the column, the more #s I get, even when I use my entire screen for column width.
I've never encountered this problem before.
Maybe I should say that if I enter the date as a number, say, 37119, everything's fine!
But, the trick should be not having to guess on the date number ...
Cheers,
Søren
Posted by Barrie Davidson on August 16, 2001 2:02 PM
What happens if you enter this (minus the quotation marks) in to the cell?
"aug 14, 2001"
This should work (works fine for me in Excel97). Let me know.
Regards,
Barrie
Posted by Elbech on August 16, 2001 2:06 PM
Dear Barrie,
It doesn't. It displays the date as a text.
Cheers,
Søren
Posted by Barrie Davidson on August 16, 2001 2:10 PM
Hmmm, a mystery. When you right click on the cell, how is the cell formatted? I know that your previous post had stated formatting was dd-MMM-yy in your regional settings but this will not apply to Excel unless you format the cell as date. Am I in the game here?
Barrie
Posted by Elbech on August 16, 2001 2:12 PM
Dear Barrie,
I'm not sure.
When I right click on the cell, and pick Format Cells, it states a Date format, and specifies 14-Mar-1998, i.e. dd-mmm-yyyy.
Cheers,
Søren
Posted by Elbech on August 16, 2001 2:24 PM
Dear Barrie,
I'm not sure.
When I right click on the cell, and pick Format Cells, it states a Date format, and specifies 14-Mar-1998, i.e. dd-mmm-yyyy.
Cheers,
Søren
Posted by Barrie Davidson on August 16, 2001 2:45 PM
I think I may have stumbled upon your problem. Select "Tools|Options" from the main menu and then select the "Transition" tab. Is the "Transition formula entry" checkbox checked off. If it is, uncheck it.
Touchdown??
Barrie
Posted by Elbech on August 17, 2001 12:21 AM
TOUCHDOWN!
Dear Barrie,
You certainly touched down! I did as you suggested and WHOA, I can now enter dates!
Thank you ever so much.
Kindest regards,
Søren
