Date format in Excel 07

[Guest]

Greetings! I am trying to create a spreadsheet using Excel 2007 that will
allow the end user to only enter the month (mm) and year (yy). I have
created a custom format of mm/yy but it still wants the user to enter the
date as mm/dd/yy. It really is not a big deal if the date is in this current
year but that will not always be the case. Am I going about this wrong?
Thanks in advance for any suggestions.

 

[Fred Smith]

One of the quirks of Excel is that the cell format has nothing to do with how
Excel interprets your input (except for Text format). If you enter xx/yy into a
cell, Excel will treat your entry as month and day, and will append the current
year to create a date.

Your options are:

1. Enter 1/mm/yy (or mm/1/yy depending on your regional settings), as you've
stated
2. Enter mm and yy in separate cells, then put them together using a formula.
3. Enter mm/yy, which will be interpreted as dd/mm (mm/dd), and use a formula to
reconfigure it, such as: =date(day(a1),month(a1),1)
4. Create a macro.

 

[Guest]

Fred,

Thanks for the information. I will try these different options to see what
will work best for our end users. )