Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Month Year Date Format
I am using Excel 2002. I have a document that is protected. Cell B2 is
unlocked so the user can input the month. What I would like to do is have the date appear as: month year (e.g., February 2005). I need this to work not only for the current month, but for the previous month, if the user needs to input information for the previous month. I've tried formatting the cell, using the Custom Category, mmmm yyyy. However when I type February the year does not show. if I type just a 2 the date shows up as January 1900. If possible I would like the month and year to appear if the user types a number for the month (2 for February) or spells out the month (February). Thanks -- Jamie |
#2
|
|||
|
|||
Jamie,
You need to enter a valid date in that cell. Simply enter 2/1 (or 2/1/5, or any value from 2/1 to 2/28) and it will show up as February. Enter 1/1 and it will show up as January. HTH, Bernie MS Excel MVP "Jamie" wrote in message ... I am using Excel 2002. I have a document that is protected. Cell B2 is unlocked so the user can input the month. What I would like to do is have the date appear as: month year (e.g., February 2005). I need this to work not only for the current month, but for the previous month, if the user needs to input information for the previous month. I've tried formatting the cell, using the Custom Category, mmmm yyyy. However when I type February the year does not show. if I type just a 2 the date shows up as January 1900. If possible I would like the month and year to appear if the user types a number for the month (2 for February) or spells out the month (February). Thanks -- Jamie |
#3
|
|||
|
|||
Hi
Add an additional sheet, p.e. SetUp (later you can hide the sheet, as the user don't need the access to it). Into A1 enter the formula =DATE(YEAR(TODAY()),MONTH(TODAY())-1,1) Into A2 enter the formula =DATE(YEAR(TODAY()),MONTH(TODAY()),1) Format both cells in any valid date format displaying month (and year) - in this format you see later selections in drop-down list. Select both cells, and define the selected area as named range, p.e. Month Select the cell B2 on your original sheet, and format it as data validation list with source: =Month and format the cell in desired date format, p.e. "mmmm yyyy" Now you can always select between current and previous month in cell B2, or you can enter manually the 1st date of those months in any valid date format - no other entries are allowed at all. Arvi Laanemets "Jamie" wrote in message ... I am using Excel 2002. I have a document that is protected. Cell B2 is unlocked so the user can input the month. What I would like to do is have the date appear as: month year (e.g., February 2005). I need this to work not only for the current month, but for the previous month, if the user needs to input information for the previous month. I've tried formatting the cell, using the Custom Category, mmmm yyyy. However when I type February the year does not show. if I type just a 2 the date shows up as January 1900. If possible I would like the month and year to appear if the user types a number for the month (2 for February) or spells out the month (February). Thanks -- Jamie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get only the year in the date format in Access | New Users to Excel | |||
Format an excel column as a date for a 5 day week for a year. | Excel Discussion (Misc queries) | |||
How can I format a cell so date field only displays the Month? | Excel Discussion (Misc queries) | |||
create a date from year, day and month | Excel Worksheet Functions | |||
Combine & Display “Fixed” & “Automatically Updated” Date Parts | Excel Worksheet Functions |