default date format

[brannoncpa]

In a new worksheet, I enter the following inputs:
Input # 1: 08/26/08 and see 8/26/2008
Input # 2 (in a different cell): 08/26 and see Aug-26
Input # 3 (in the same cell as #2 because I didn't like the result I saw): 08/26/08 and I still see Aug-26



Question: The desired result I would like to see in all three cases is: 8/26/2008
Would someone please explain:
1) How do I set the default date to automatically assume current year if I merely enter 08/26 ?
2) Why entering 08/26 is such a grievous sin that I should so horribly point Excel in the wrong direction <forever> - or at least until I reformat that cell?



System info: Windows XP Pro & Office 2003
Regional Settings: short date format M/d/yyyy

Thank you so much.

 

[DonkeyOte]

Re: Input 1

I suspect this cell was formatted as date m/d/yyyy

Re: Input 2

Excel willl automatically assume year to be current year if not entered.
Format was as it was because the cell was not formatted as per cell 1.

 

[brannoncpa]

This was a new spreadsheet and no formatting had been done. It seems to me that no formatting would need to be done when a date format is entered. In both cases, Excel knew it was a date format. For Input #2, it just assumed (defaulted to) an un-inspired date format for some reason.

 

[RoryA]

When you enter a date into a cell that has the General format, Excel will try and guess the format you want. If you don't enter a year, Excel assumes you're not interested in the year so doesn't display it. The cell now has a specific date format applied however, so if you re-enter a date, you still have the mmm-dd format applied. If you want the second instance to show the year, you need to specifically format the cell(s) with a suitable date format.

 

[Gerald Higgins]

Excel made a guess at what you wanted. It may have guessed wrong for you, but its guesses were not that unreasonable.

1) Entering 08/26/06 and Excel displays 8/26/2008.
I assume this is what you want, yes ?

2) Entering 08/26 and Excel displays Aug 26.
Excel does actually assume that you want the current year, but it also guesses that you don't want to SEE the year. If you re-format this cell to show the year, you will see that it has actually entered the current year information, just decided not to show it.
Consider the dilemma facing the Excel designers.
Faced with a user who makes an entry that looks like a day / month combination, what should the default format be ?
They had two choices - either format it as day month year, showing the year, or format it as day month, and not showing the year. I guess they decided that, if the user didn't enter the year, the user probably didn't want to see the year. I think that's a reasonable judgement to make, although it won't be right for everybody. If it's not right for you, sorry, but get over it
And it's easy to get over it - just format your range with whatever format you want.

3) Entering 26/8/8 IN THE SAME CELL and Excel returns Aug 26.
Because you've previously made an entry in this cell, and Excel has made a guess at the format, and you haven't corrected it, Excel assumes you're happy with the format it guessed. After all, if you weren't happy with it, you would have changed it, right ? Again, I don't think that's unreasonable.

 

[brannoncpa]

Thanks for the thoughtful post. Yes, 8/26/2008 <is> is what I want. Agreed, the Designers did a pretty good job of anticipating what I wanted.

I concur that the Designers gave us full formatting capability and am familiar with much of the "format" feature.

The heart of the matter: Did the Excel Designers give us the ability to set the default date format to something that this user might prefer as each new spreadsheet is begun? I have been unable to locate this feature. (You may be aware that the Lotus 1-2-3 folks gave us a "global default" format feature to accomplish this task. I'm thinking that the Designers did not intentionally miss that very useful tidbit.)

(Why would I want to do this? I prepare a lot of spreadsheets, and tend to enter lots of dates in random places throughout a spreadsheet. Each mouse click and keyboard stroke costs time and $$$. It would be more cost effective for me if I could set my "user-preferred" date default if at all possible.)
</is>

 

[Gerald Higgins]

Maybe not exactly the right solution, but consider VogII's response in this thread. Takes a bit of time to set up, but it could be useful for you.
http://www.mrexcel.com/forum/showthread.php?t=333298

 

[brannoncpa]

I'll study that item. There are some new things for me to learn there. Thanks for the tip.

 

[brannoncpa]

Ok. I've looked at all the options so far and none have lead me to the right solution. I don't want Excel to decide or "guess" what I want as far as a date format, or any format for that matter. There should be a default setting so that whenever I enter a date in a brand new workbook/worksheet it will format 8/26/2008. Whether I enter 8/26 or 8/26/08 or 8/26/2008 or whatever strange format you can come up with. it should always be 8/26/2008. I shouldn't have to go to each cell and format each cell to that specific format. I should be able to change the automatic default formatting.

 

[Gerald Higgins]

Sorry, but I just don't think Excel does that.

There are several workrounds.
One way would be to select the entire worksheet by clicking on the empty grey box at the top left of the sheet, to the left of the A column indicator, and above the 1 row indicator, and set the format for the entire sheet to the date format of your choice.
BUT, this will have the possibly undesirable effect of making all cells date formatted, so that if you enter a number that shouldn't be date formatted (such as a cash value) then you'll have to manually change the format of that cell.

Another way - just get into the habit of entering dates including specifying the year number. This is probably a good habit to get anyway, especially at this time of year, when year to year confusion can easily happen.

There might also be VBA solutions.