nerian
asked on
Excel and cell formatting
I seem to have some trouble getting excel to format certain cells to show a date by "m/d/yyyy". No matter how i enter the number, eg: "12-11" or 12/11" , it won't auto format it. It just shows me "12-11". I tried a custom format but still no dice.
Weird problem... but how can i make it auto format every number i enter and assume it to be a date and just auto add the current month and year in? So if i enter "12" Excel knows i meant the 12th of this month and year and correctly formats it to "12/12/2009".
Weird problem... but how can i make it auto format every number i enter and assume it to be a date and just auto add the current month and year in? So if i enter "12" Excel knows i meant the 12th of this month and year and correctly formats it to "12/12/2009".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It also often helps to just copy/past the whole date as values into a new excel sheet and reset then your properties onto the date fields.
ASKER
Just thought of it, is there a way to just enter a letter or number and have Excel automatically recognize it as a keyword and replace it with something?
Say i enter "a" in lowercaps in a cell and Excel recognizes it and changes it to "Pinnacle". Same with dates, if i enter "12", Excel knows it's the day of the month and automatically means 12th of current day + month.
Say i enter "a" in lowercaps in a cell and Excel recognizes it and changes it to "Pinnacle". Same with dates, if i enter "12", Excel knows it's the day of the month and automatically means 12th of current day + month.
ok, try this; change your regional settings to english (UK)
then select customize and go to date
select the desired one
re-open excel and select the formatting as english (UK)
see if it works and post back reply
cheers
then select customize and go to date
select the desired one
re-open excel and select the formatting as english (UK)
see if it works and post back reply
cheers
ASKER
It works but it shows my date as 13/01/1900.
you mean wrong year? what's the date you are trying to insert?
ASKER
The current date, 2009. I have no idea why it keeps inserting 1900.
did you insert a complete date with year and showed the year as 1900? can you attach a part of the excel you are trying to make?
also search for any windows upadtes for excel 2000/2003 as there is a bug with leap date for 1900 hence a problem with weeks etc.
I'm not so sure if that's a general bug in the VB code of excel. I don't have any problem with that but then I got all the windows updates on.
also search for any windows upadtes for excel 2000/2003 as there is a bug with leap date for 1900 hence a problem with weeks etc.
I'm not so sure if that's a general bug in the VB code of excel. I don't have any problem with that but then I got all the windows updates on.
ASKER
I don't understand the attach a part of excel.
I didn't try to insert a complete date before or after. All i did was format the cell with "m/d/yyyy" and when i enter "12" in to the cell, it auto formats to 12/1/1900.
This is in Office 2007 too.
I didn't try to insert a complete date before or after. All i did was format the cell with "m/d/yyyy" and when i enter "12" in to the cell, it auto formats to 12/1/1900.
This is in Office 2007 too.
ok; that's the default date it gets for excel.
what happens if you insert 23/12/2009? does it keep the format and is this the correct format you'd like to use? do you want to insert just a number (e.g. 15) and autofill the rest 15/12/2009?
what is it that you wonna do exactly?
what happens if you insert 23/12/2009? does it keep the format and is this the correct format you'd like to use? do you want to insert just a number (e.g. 15) and autofill the rest 15/12/2009?
what is it that you wonna do exactly?
ASKER
Yep, i just want to enter 15 and have it autofill the rest to 15/12/2009.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yeah, i'll just use his way, thanks for the replies though :D
it's a good think that ironies were avoided! especially after so many replies to your basic question. Just out of curiosity though; why didnt you assign the points right after you got the reply from cy_hung? I just hope youll never need my help again! I dont mind that you accepted cy_hung's answer or gave him more points than me. I dont even want your points; I do care about your comment after accepting the solution. Honestly, you dont deserve anyones time on here