Date format in Excel 07
 

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.

Date format in Excel 07
 

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 a

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.

--
Regards,
Fred


"Chimelle" wrote in message
...
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.

Date format in Excel 07
 

Fred,

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

"Fred Smith" wrote:

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 a

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.

--
Regards,
Fred


"Chimelle" wrote in message
...
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.