?
Solved

Excel and cell formatting

Posted on 2009-12-25
14
Medium Priority
?
277 Views
Last Modified: 2012-05-08
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".
0
Comment
Question by:nerian
14 Comments
 
LVL 7

Accepted Solution

by:
cy_hung earned 340 total points
ID: 26122061
The things you're looking for may be purely about formatting.  For example, entering 12 to a cell, would allow excel to understand the required date correctly.

Here is my suggestion:

1) Setup proper formatting, in your case, I think you may use custom format, "m/d/yyyy".
2) Enter proper value ... 12/12/2009

Optional, add some logic to transform a number into a date in current month:
e.g. in cell A1, input 12
and then in cell A2, use this formula "=DATE(YEAR(NOW()), MONTH(NOW()),A1)"


 
0
 
LVL 3

Expert Comment

by:ElKerm
ID: 26122732
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.
0
 

Author Comment

by:nerian
ID: 26122805
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.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 4

Expert Comment

by:thsteph
ID: 26122970
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
0
 

Author Comment

by:nerian
ID: 26126415
It works but it shows my date as 13/01/1900.
0
 
LVL 4

Expert Comment

by:thsteph
ID: 26127570
you mean wrong year? what's the date you are trying to insert?
0
 

Author Comment

by:nerian
ID: 26129084
The current date, 2009. I have no idea why it keeps inserting 1900.
0
 
LVL 4

Expert Comment

by:thsteph
ID: 26130096
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.
0
 

Author Comment

by:nerian
ID: 26130987
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.
0
 
LVL 4

Expert Comment

by:thsteph
ID: 26131316
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?
0
 

Author Comment

by:nerian
ID: 26132237
Yep, i just want to enter 15 and have it autofill the rest to 15/12/2009.
0
 
LVL 4

Assisted Solution

by:thsteph
thsteph earned 160 total points
ID: 26133323
and what if the date should be set to a previous month or year? either way, I 'd suggest using cy_hung's way. I wouldn't like to echo on his answer
0
 

Author Closing Comment

by:nerian
ID: 31670631
Yeah, i'll just use his way, thanks for the replies though :D
0
 
LVL 4

Expert Comment

by:thsteph
ID: 26135986
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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question