Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
m/yy or m/yyyy date format
I am having problems with some date ofrmat, date entry.
I have a column in which I want to enter stricktly the month and year (NO day of the month). If I enter 3/11 to me it means March 2011, but to Excel it is March 11, 2008 (or Mar-08, depending on the format). Any suggestions. I have others that will be doing the data entry, and I do not want to train them to 'fudge' to get the format correct. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
m/yy or m/yyyy date format
I should add that I will be oding some searches and formula calculations on
the month/year. "Bigfoot17" wrote: I am having problems with some date ofrmat, date entry. I have a column in which I want to enter stricktly the month and year (NO day of the month). If I enter 3/11 to me it means March 2011, but to Excel it is March 11, 2008 (or Mar-08, depending on the format). Any suggestions. I have others that will be doing the data entry, and I do not want to train them to 'fudge' to get the format correct. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
m/yy or m/yyyy date format
Train them to use 4 digits for the year!
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Bigfoot17" wrote in message ... I am having problems with some date ofrmat, date entry. I have a column in which I want to enter stricktly the month and year (NO day of the month). If I enter 3/11 to me it means March 2011, but to Excel it is March 11, 2008 (or Mar-08, depending on the format). Any suggestions. I have others that will be doing the data entry, and I do not want to train them to 'fudge' to get the format correct. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
m/yy or m/yyyy date format
Bigfoot17,
If you enter 3/11 in cell A1 it will return 11-Mar. From there, go to Format | Cells (or hit Control+1). Now you can modify how the date looks. It will still retain the day, month and year, but will only show what you want it to. In the "Number" Tab, select either Date or Custom in the Category window. Scroll in the type window for how you want it to appear. For Mar-08, select mmm-yy. for March 2008, type in Mmmm yyyy Hope that helps. "Bigfoot17" wrote: I should add that I will be oding some searches and formula calculations on the month/year. "Bigfoot17" wrote: I am having problems with some date ofrmat, date entry. I have a column in which I want to enter stricktly the month and year (NO day of the month). If I enter 3/11 to me it means March 2011, but to Excel it is March 11, 2008 (or Mar-08, depending on the format). Any suggestions. I have others that will be doing the data entry, and I do not want to train them to 'fudge' to get the format correct. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
m/yy or m/yyyy date format
I couldn't figure out a format that would work, but this would. Format all
the cells as text. Then when the user enters the date as 3/11, it displays 3/11 in the cell. Then you could, after the fact, create a formula that would make it into a date: If cells with value is in E10: =DATE(RIGHT(E10,2)+100,IF(MID(E10,3,1)="/",LEFT(E10,2),"0" &LEFT(E10,1)),1) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
m/yy or m/yyyy date format
You can fight excel as much as you like, but you're not going to win.
You can use 3/2011 And excel will see it as March 1, 2011. Bigfoot17 wrote: I am having problems with some date ofrmat, date entry. I have a column in which I want to enter stricktly the month and year (NO day of the month). If I enter 3/11 to me it means March 2011, but to Excel it is March 11, 2008 (or Mar-08, depending on the format). Any suggestions. I have others that will be doing the data entry, and I do not want to train them to 'fudge' to get the format correct. -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
m/yy or m/yyyy date format
I had a similar problem in that I was comparing information for a vlookup and
even though I changed how it displayed, when you clicked in the cell it STILL showed a full date, meaning I couldn't compare the info - it truly wasn't the same. Is there not a way to format it to show AND recognize only a month and year without forcing it to give an actual calendar date such as March 1, 2011? "Dave Peterson" wrote: You can fight excel as much as you like, but you're not going to win. You can use 3/2011 And excel will see it as March 1, 2011. Bigfoot17 wrote: I am having problems with some date ofrmat, date entry. I have a column in which I want to enter stricktly the month and year (NO day of the month). If I enter 3/11 to me it means March 2011, but to Excel it is March 11, 2008 (or Mar-08, depending on the format). Any suggestions. I have others that will be doing the data entry, and I do not want to train them to 'fudge' to get the format correct. -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
m/yy or m/yyyy date format
Not if you want a real date.
A real date must have a day, month, year Gord Dibben MS Excel MVP On Tue, 1 Jul 2008 10:32:07 -0700, smartgal wrote: I had a similar problem in that I was comparing information for a vlookup and even though I changed how it displayed, when you clicked in the cell it STILL showed a full date, meaning I couldn't compare the info - it truly wasn't the same. Is there not a way to format it to show AND recognize only a month and year without forcing it to give an actual calendar date such as March 1, 2011? "Dave Peterson" wrote: You can fight excel as much as you like, but you're not going to win. You can use 3/2011 And excel will see it as March 1, 2011. Bigfoot17 wrote: I am having problems with some date ofrmat, date entry. I have a column in which I want to enter stricktly the month and year (NO day of the month). If I enter 3/11 to me it means March 2011, but to Excel it is March 11, 2008 (or Mar-08, depending on the format). Any suggestions. I have others that will be doing the data entry, and I do not want to train them to 'fudge' to get the format correct. -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
m/yy or m/yyyy date format
And if you don't want excel to convert your entry to a real date, you could
either: Preformat the cell as Text, then do the data entry or Prefix your entry with an apostrophe: '03/11 Both of these will mean that your entry is text--not a date. smartgal wrote: I had a similar problem in that I was comparing information for a vlookup and even though I changed how it displayed, when you clicked in the cell it STILL showed a full date, meaning I couldn't compare the info - it truly wasn't the same. Is there not a way to format it to show AND recognize only a month and year without forcing it to give an actual calendar date such as March 1, 2011? "Dave Peterson" wrote: You can fight excel as much as you like, but you're not going to win. You can use 3/2011 And excel will see it as March 1, 2011. Bigfoot17 wrote: I am having problems with some date ofrmat, date entry. I have a column in which I want to enter stricktly the month and year (NO day of the month). If I enter 3/11 to me it means March 2011, but to Excel it is March 11, 2008 (or Mar-08, depending on the format). Any suggestions. I have others that will be doing the data entry, and I do not want to train them to 'fudge' to get the format correct. -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
m/yy or m/yyyy date format
And you cannot do calculations off of this because it is text.
$P$2:$P$400<TODAY() "Dave Peterson" wrote: And if you don't want excel to convert your entry to a real date, you could either: Preformat the cell as Text, then do the data entry or Prefix your entry with an apostrophe: '03/11 Both of these will mean that your entry is text--not a date. smartgal wrote: I had a similar problem in that I was comparing information for a vlookup and even though I changed how it displayed, when you clicked in the cell it STILL showed a full date, meaning I couldn't compare the info - it truly wasn't the same. Is there not a way to format it to show AND recognize only a month and year without forcing it to give an actual calendar date such as March 1, 2011? "Dave Peterson" wrote: You can fight excel as much as you like, but you're not going to win. You can use 3/2011 And excel will see it as March 1, 2011. Bigfoot17 wrote: I am having problems with some date ofrmat, date entry. I have a column in which I want to enter stricktly the month and year (NO day of the month). If I enter 3/11 to me it means March 2011, but to Excel it is March 11, 2008 (or Mar-08, depending on the format). Any suggestions. I have others that will be doing the data entry, and I do not want to train them to 'fudge' to get the format correct. -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
m/yy or m/yyyy date format
The display can be however you want it, the concern is ensuring the date will
always match, if you have someone always enter the year as a 4 digit number, as previously suggested, this can be achieved. In addition, you can use data validation to help you. What I suggest: Select the first cell you will expect entry on. Go to Data--Validation. Settings: Allow should be: CUSTOM. Formula should be: =DAY(A2)=1, where A2 is the initial cell you are setting up. This will 'force' the entry person to have the day being entered as a 1 (which is the default if they enter in format of m/yyyy). In addition, you can use the Input Message tab to instruct the entry person of the proper format for entry. Title could be: Enter Date. Input message could be: Enter in format of m/yyyy just my 2 cents -- John C "Bigfoot17" wrote: I should add that I will be oding some searches and formula calculations on the month/year. "Bigfoot17" wrote: I am having problems with some date ofrmat, date entry. I have a column in which I want to enter stricktly the month and year (NO day of the month). If I enter 3/11 to me it means March 2011, but to Excel it is March 11, 2008 (or Mar-08, depending on the format). Any suggestions. I have others that will be doing the data entry, and I do not want to train them to 'fudge' to get the format correct. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format date dd.mm.yyyy to dd/mm/yyyy | Excel Discussion (Misc queries) | |||
change date format from dd/mm/yyyy to mm/yyyy | Excel Discussion (Misc queries) | |||
Date format yyyy/mm/dd | Excel Discussion (Misc queries) | |||
Date format from yyyy-mm-dd-hh | Excel Discussion (Misc queries) | |||
how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel | New Users to Excel |