Date Entry Question

I want to enter JUST the month and year and have Excel 
recognize it as such.  Unfortunately, Excel decides the 
year I am entering is a day of a month and uses the 
current year to complete the date.  For example, I enter 
Feb-03 into a date-formatted cell (Mmm-yy) and Excel shows 
Feb-04 as the output.  If I click on the cell, the value 
is 2/3/2004.  Is there anyway around this?
0
anonymous (74722)
1/28/2004 10:13:51 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
317 Views

Similar Articles

[PageSpeed] 11

Excel will always treat a date as mm/dd/yyyy format (at least in the U.S.).
So if you enter 02/2004, Excel will show 2/1/2004 in the formula bar.  The
only thing you can do (I think) is format the cell as mm/yyyy or however you
want it to appear.

-- 
Michael J. Malinsky


"Dustin" <anonymous@discussions.microsoft.com> wrote in message
news:5ac501c3e5ec$02b477d0$a301280a@phx.gbl...
> I want to enter JUST the month and year and have Excel
> recognize it as such.  Unfortunately, Excel decides the
> year I am entering is a day of a month and uses the
> current year to complete the date.  For example, I enter
> Feb-03 into a date-formatted cell (Mmm-yy) and Excel shows
> Feb-04 as the output.  If I click on the cell, the value
> is 2/3/2004.  Is there anyway around this?


0
joeqcpa (67)
1/28/2004 10:28:16 PM
I think Trevor's response is true if you need to have 
Excel recognize the entry as a date. But if you don't 
need that, you could also format the cell as text. Then 
you can enter whatever you want.
>-----Original Message-----
>I want to enter JUST the month and year and have Excel 
>recognize it as such.  Unfortunately, Excel decides the 
>year I am entering is a day of a month and uses the 
>current year to complete the date.  For example, I enter 
>Feb-03 into a date-formatted cell (Mmm-yy) and Excel 
shows 
>Feb-04 as the output.  If I click on the cell, the value 
>is 2/3/2004.  Is there anyway around this?
>.
>
0
anonymous (74722)
1/29/2004 6:19:37 PM
Reply:

Similar Artilces:

Date recalculates
I need help with a date formula. I am using Excel 2007. I use the following formula to insert a date .... =IF(ISNUMBER(C3),+TODAY(),"") Works fine except I do not want the inserted date to recalculate each time I reopen the worksheet. Is there any way around this? TIA MamaBee Hi MamaBee, Am Mon, 12 Mar 2012 15:04:15 -0300 schrieb MamaBee: > I need help with a date formula. I am using Excel 2007. > I use the following formula to insert a date .... > =IF(ISNUMBER(C3),+TODAY(),"") > Works fine except I do not want the inserted date to recalculate each &...

DISP_FUNCTION_ID and ON_EVENT question
Hi, When I used: AfxConnectionAdvise (pSrcUnk, __uuidof (ISomeInterfaceEvents), GetIDispatch(TRUE), TRUE, &m_cookie); in a CCmdTarget derived class. I expected that I will be able to use ON_EVENT but it didn't work but DISP_FUNCTION_ID. Can any one explain this and what is the difference between DISP_FUNCTION_ID and ON_EVENT Thanks in advance ...

Vlookup question #8
Hi- I have a spreadsheet that looks at individuals and the amount they paid within a certain year for healthcare expenditures. Based upon this level of expenditure, I would like Excel to spit out the Category that the individual falls into. Here is what the table for looking up the category looks like: A B C Category Min Max 00 $0 $20 01 $20 $165 Thus, if the expenditure is $17, they would fall into category 00. How do I do this in Excel? I know its probably an easy VLOOKUP function but I'm not sure how to do the VLOOKUP function when telling Excel th...

Counting Number of Identical Entries in a Spreadsheet Column
I am using XP Home and Office XP. I use a spreadsheet that has about a hundred or so lines and a number of columns, one of which is "Agent Number" in which a four digit number is entered to identify the Agent for that particular line item. Is there an easy way for me to create a table showing a ranked order of the number of times each Agent is listed in the column (e.g., Agent 7818: 20, Agent 7820: 17, etc.)? Thanks in advance! -- Fred 2002 Hi use a pivot table for this . See: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.c...

Date lies between
I have 2 date column in sheet "DB" and I need to find if the date entered in sheet1 lies between these dates.. That is for Example - if I enter dates 12-May to 15-May it should say "True" or what ever else "false" Its should check the entire column and give the result. From To 12-May 15-May 25-May 26-May 28-May 01-Jun 05-Jun 08-Jun 'If there is only 1 query date then try the below with query date in cell E1 =SUMPRODUCT((DB!A1:A5<=E1)*(DB!B1:B5>=E2))<>0 If it is a date range; then try the below with E1 and E2 as start...

Automatic date changes
When copying/pasting in excel, excel automatically updates numbers like 3/3 or 2/6 into dates. Hwo do I stop it doing this? as I would prefer it to just be '3/3 or '2/6 as a text. Thanks Jonathan Jonathan, like you mentioned in your post, you can force Excel to treat the numbers as text by putting an apostrophe ' in front of the numbers. Alternatively, you can format the cells as fractions (if this works for you) and then enter your numbers and Excel will treat them as such and keep them as 2/6. Please note that this won't work for 3/3 as Excel will convert this to 1. A...

Date on a word document
How do you format a date to be at the end of a document each time you update it? -- Thanks Add a docvariable field at the end of the document {DocVariable varDate} Use the following macro to update the field.and save the document. By naming it FileSave it will intercept Word's built-in Save command. Sub FileSave() Dim oVars As Variables Dim oField As Field Set oVars = ActiveDocument.Variables oVars("varDate").Value = Format(Date, "dd/MM/yyyy") For Each oField In ActiveDocument.Fields If oField.Type = wdFieldDocVariable Then oField.Update ...

SetActiveView question
Got a problem with program control of the active view. I've got a magnify input in the main frame which only applies to one of the 4 views on the screen. So I have to make sure that it is active before I apply the magnification. Seemed reasonably straight forward, I store the view pointer, pview, when it is created at startup, in my root program and then when the user types in the magnification box, get the pview , do a SetActiveView(pview) and then call the pview->DoMagnify() routine I wrote. Things don't behave the way I expected. 1) none of the values I set in the view creation ...

Database design question
Just curious about any drawbacks to this type of database design. There is an organization table that has many types of organizations and structures dealing with organizations in it such as: Company, Region, Market, etc There is another table that is the linking table for all the organizations. The type of relationship you are looking at is by a RecordTypeID. So in essence, it is like having 6 diffent tables in one table with about 12+ different mapping tables in another table. So your queries would look something like: SELECT * FROM Organization Company JOIN Organ...

Template pagte date and time Stamp
On the default new page a current date and time stamp is created. Is there a way to use these stamps on a user created template --say located them elsewhere on the page -- for example a footer area? Stuball wrote: > On the default new page a current date and time stamp is created. Is > there a way to use these stamps on a user created template --say > located them elsewhere on the page -- for example a footer area? No, this is not possible. But you may insert date & time wherever you want (in addition). Rainald ...

Capability questions
We are currently evaluating MSCRM and are wondering if it is possible to do a couple of things. 1) Mailmerge/emailmerge related party information. ie if we have a referral relationship can I send an email like... Hi {Lead:firstname}, you were referred to use by {referal:firstname} 2) Can you have relationship roles for a lead? 3) Can I run a workflow rule on a closed opportunity? I want to schedule a task based on a maturity date stored in the opportunity entity 4) Can I use attributes from a custom entity in an email or word merge? Can I create custom placehol...

Outlook Connector question
I'm using this with Outlook 2007; is there any way to get new messages to show up in Unread Mail instead of just in the email address's Inbox? Thanks, John No, not with the other unreads in your mailbox. You can have a search folder for the msn inbox though. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Outlook Tips: http://www.outlook-tips.net/ Out...

Date format
I have set my MBA to European date format but I notice in Mac Office 2008 it still uses American date format. Can someone advise me if Mac Office 2008 does not follow the system date format. If so, please advise the required additional configuration. Thanks, Scott Scott <NoSpam-Scott.Xe@GMail.com> wrote: > I have set my MBA to European date format but I notice in Mac Office 2008 it > still uses American date format. Can someone advise me if Mac Office 2008 > does not follow the system date format. If so, please advise the required > additional configuration. It ...

Questions on EdgeSync in a forest
Hi all, I have E2K7 installation in a forest of 4 domains, all of them are in one site. My Hub Transport server is a member of root domain of the forest. I have subscribed Edge Transport server, the mail is going fine, synchronization goes without any error, BUT only recipients from root domain are synchronyzed. So I have two questions: 1) Can EdgeSync synchronyze recipients from entire forest? 2) What could be the reason for such a "narrowed" synchronization scope as I described? -- Regards, Dmitry 1. Yes, of course. 2. Good question. Are your GC's correctly replicati...

Question on MDI Apps #2
I am creating an MDI app that will have a variety of dialog boxes that will be displayed from clicking on Menu items. ex. Customers, Vendors, Inventory etc. I want to have the ability to have a single copy of each of these open at the same time. ex. both the customer and inventory dialog box open at the same time in the app. I also want to have them both listed in the Window menu. Should I use a Modeless Dialog Box or should I use a FormView? If you say a FormView, I know how to display a dialog box from a menu click but how do you display a formview based on a menu click? Also if you say...

Totalling by referring to 2 date ranges
I have a spreadsheet that has 2 columns of date ranges. I want to refer to the first column and count how many entries meet a specific date range criteria. If there is no entry in the first column, I want to refer to column 2 to see if the range matches. Can somebody help???? Thanks ...

Question 2:Color cell using conditional formatting
Hi, Using conditional formatting to change color of cell if something is entered works! (thanks Anonnymous!) Now, how woudl I go about to change the color to red if data entered starts with a 1, and change to yellow if if it starts with a letter. I have seen that you can add a 2nd condition in conditional formatting, but I cannot find what formula to use. Or should I use cell value is? Thanks again!! For the 1st condition, use: =LEFT(A1)*1=1 For the 2nd: =OR(AND(CODE(LEFT(A1))>=65,CODE(LEFT(A1))<=90),AND(CODE (LEFT(A1))>=97,CODE(LEFT(A1))<=122)) HTH Jason Atlanta, GA ...

Saving Question
Why does Excel sometimes ask me if I want to save my spreadhseet even though I haven't made any changes? For example, I just brought it up, made no changes, did nothing, and when I close it out I'm asked if I want to save. Never understood that. Some functions are "volatile" and force a recalculation upon opening the workbook. These commands include TODAY(), NOW(), AREAS() and some others I can't think of right now. Once the workbook is recalced, Excel assumes that it has changed, even if you didn't really change anything. -- Cordially, Chip Pearson Microsoft...

Need to export from outlook 2007 into Excel, include date and tim
Exporting from Outlook 2007 to either Excel or Access Need email information date sent not modified subject line to from http://www.codetwo.com/freeware/outlook-export/ Karl -- ____________________________________________________________ Karl Timmermans - The Claxton Group ContactGenie - QuickPort/DataPort/Exporter/Toolkit/Duplicate Contact Mgr "Contact import/export/data management tools for Outlook '2000/2010" http://www.contactgenie.com "MicroMacroMicro" <MicroMacroMicro@discussions.microsoft.com> wrote in message news:3B2AD2A0-7A64-45D7...

SUM of values within date range which equals another field
Hi, Can someone please help me out.. I have gone thru this forum and hav found a kinda right solution but mine is abit different. Basically its like for hospital data: I have say a date range say between 01/01/2002(start_date) an 01/04/2004 (end_date) Need to know how many patients (patient_ID) are admitted into parituclar department (heart_dept) within the above date range. If someone can help me would be really great : -- Message posted from http://www.ExcelForum.com Hi if column A stores the date and column B the department use =SUMPRODUCT(--(A1:A1000>=DATE(2002,1,1)),--(A1:A100...

auto date #2
I need a formula shen I input a dollar figure in cell A1 Cell A2 will show the date it was added. Thanks to all who help in advance. disregaurd my question I see it was answered earlier thanks anyways "Bob Bob" <NOeMAILl@mail.com> wrote in message news:Imq%i.2878$fD.1166@pd7urf3no... > I need a formula shen I input a dollar figure in cell A1 Cell A2 will show > the date it was added. > Thanks to all who help in advance. > > ...

How to preserve file date stamp when using XP to UNzip
Does anyone know how to use XP unzipping utility in such a way that it preserves the file date/tine stamps? In my experience (and stranger, it may be "intermittent"), it seems that it assign TODAY"S date stamp to the dearchived files. I say "intermittent" in that, id memory serves, it seems that at random tines (circumstances unknown), some compressed zip files render their contained files with their original production dated. -- TommyC ------------------------------------------------------------------------ TommyC's Profile: http://forums.tech...

Input Mask for Dates
Can I apply an input mask or custom format to cells so that users do not have to type the slashes for date entry? Thanks! Hi have a look at http://www.cpearson.com/excel/DateTimeEntry.htm This requires VBA as this can't be done with formating only. -- Regards Frank Kabel Frankfurt, Germany Allison wrote: > Can I apply an input mask or custom format to cells so > that users do not have to type the slashes for date > entry? Thanks! You can use some VBA code that will interpret a date that is entered with out "-" or "/". See Chip Pearson's site: ...

Date Format turn to Year #2
From: learning_codes@hotmail.com - view profile Date: Thurs, Nov 16 2006 9:24 pm Email: "learning_co...@hotmail.com" <learning_co...@hotmail.com> Groups: microsoft.public.excel Not yet ratedRating: show options Reply | Reply to Author | Forward | Print | Individual Message | Show original | Remove | Report Abuse | Find messages by this author Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =3DYear(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated...

Use combo to print report between two sets of dates
I am trying to print a report that shows timesheet entries for two sets of dates. The report also prints out based on the data entry person selected. The user selects their name and then uses another combo box to select pre entered dates. My code is as follows: Private Sub cboDates_Click() Dim stDocName As String Dim FromDate As Date 'Name of criteria start timesheets date field. Dim ToDate As Date 'Name of criteria end timesheets date field. Dim FromOTDate As Date 'Name of criteria start OT date field. Dim ToOTDate As Date 'Name of criter...