Date formatting

I am doing a spreadsheet on products with expiration dates.  When I
enter 7/08, indicating that the product will expire in July 2008, the
data in the cell comes up Jul-04.  All I want is the month and year.  I
have tried several format cell commands, as they relate to date.  But
the only way I have found to get it in the cell correctly is to type
7/1/08.  It then comes up Jul-08.  Isn't there a way to just type month
and year??


---
Message posted from http://www.ExcelForum.com/

0
1/21/2004 1:49:17 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
368 Views

Similar Articles

[PageSpeed] 1

Hi mkingsley!

Afraid not! You've noticed that 7/04 is interpreted as 4th July of the
current year (US Settings). There's no option that allows you to
change this date input interpretation.

Remember that Excel uses date serial numbers for dates. Each number
represents a day. July-2004 is ambiguous; which of the 31 days is
Excel to assume?

But you can enter (e.g.) 7/1/04 and format mmm-yy or some other
suitable format.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
1/21/2004 2:08:09 PM
Enter it as 07/2008 or use a temporary autocorrect

or you could use Chip Pearson's date entry macro and enter it as
7108

and format cells as mmm-yyyy

http://www.cpearson.com/excel/DateTimeEntry.htm

-- 

Regards,

Peo Sjoblom


"mkingsley >" <<mkingsley.10dws3@excelforum-nospam.com> wrote in message
news:mkingsley.10dws3@excelforum-nospam.com...
> I am doing a spreadsheet on products with expiration dates.  When I
> enter 7/08, indicating that the product will expire in July 2008, the
> data in the cell comes up Jul-04.  All I want is the month and year.  I
> have tried several format cell commands, as they relate to date.  But
> the only way I have found to get it in the cell correctly is to type
> 7/1/08.  It then comes up Jul-08.  Isn't there a way to just type month
> and year??
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
terre08 (1112)
1/21/2004 2:08:50 PM
Hi Peo!

Doh!! Forgot the four digit year interpretation.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
1/21/2004 2:16:36 PM
Reply:

Similar Artilces:

Date on sent messages incorrect
Outlook has seem to be quite selective regarding the date stamp on the sent messages. There are about one or two with the correct date and then the rest are about a week ahead. I have checked the time zone in the calendar option and the cpu clock and they are in the correct time zone. what else could it be? ...

Excel 2007
I find that if I create a conditional format at one cell and then copy that to a multitude of others, the variable references within the conditional format will not adjust accordingly. For example: At cell A1, I have the conditional format of "=IF(A1>0,1,0)". If I copy this to cell B2:B3 (in one stroke), I will find both cell B2 and B3 to have the same formula "=IF(B2>0,1,0)". This of course is fine for cell B2, but it is not fine for cell B3. I would expect cell B3 to reference itself; not cell B2. Another problem is that if the receiving cells should have...

text numbers as dates via OCR import
Here is a tough one... I have OCR scanned a printout from a database that contains sales information. The key column of data is the Product number which is in the format of nn-nn-nnnn. Of course upon import into Excel, it treats some (but oddly enough not all) of these entries as dates in the format mm/dd/yyyy. I had formated the sheet as Text prior to import. There seems to be no way now to properly sort this data as I get the "mixed numbers and text" box during a sort operation and of course it sorts in that order. I have tried several formulas, macros and formatting st...

Summing payment records in a query but restricting it to variable dates in another table
I have a complicated query I need to run and am not sure how to approach it...looking for some guidance. I have 2 tables: 1) tblPayments (contains a list of payments made to an Applicant) 2) tblAuditCheck contains a list of Applicants with a variable 'cut- off' audit date) tblPlayments has fields: ApplicantID, DateOfPayment, AmtofPayment tblAuditCheck has fields: ApplicantID, DateofAuditCheck Example data from tblPlayments is as follows: ApplicantID DateOfPayment AmtofPayment 1 17/07/2006 =A310,000 1 29/07/2006 =A32,500 1 19/09/2006 =A31,000 2 15/09/2006 =A31,200 2 19/09/2006 =...

Conditional formatting formula for when 1 cell if blank _and_ another cell is not?
I know how to do conditions all by themselves. In this case, if J2 is blank I'm using this to set the cond form for the cells K2 and L2, respectively: =(ISBLANK($J2)) But I've never used 2 conditions about 2 different cells. If J2 is blank but B2 is not blank, _then_ is when the cond form should take place. The 2nd condition by itself would be something like this: =NOT(ISBLANK($D2)) But I don't know how to put the 2 together. Thank you! :oD Try this... =AND(ISBLANK($J2),NOT(ISBLANK($D2))) Rick "StargateFanNotAtHome" <IDon'tAcceptSpam@NoJunkMail.com>...

Is there any way to organize this forum by date?
Hi. My listing for this forum (All threads) shows the last three responses (at the top) as variously 4/30/2006, 11/4/2009 and then 1/14/2004. Entries for 2010 come further down. Is there a way to organize this more helpfully? Thanks, Ian chromian wrote: > Hi. My listing for this forum (All threads) shows the last three responses > (at the top) as variously 4/30/2006, 11/4/2009 and then 1/14/2004. > > Entries for 2010 come further down. > > Is there a way to organize this more helpfully? > > Thanks, > > Ian Yes but you need to cl...

Label Formatting Question
The example below works. but it stays yellow for all records. I need it only to turn yellow if checked on a specific record. How do I write this so that it only works on specific record. Thank you in advance for any help provided. If CheckBoxName = True Then LabelName.ForeColor = vbYellow Else LabelName.ForeColor = vbBlack End If Change the label to a textbox, and change the properties of the textbox to make it look like a label. Be sure to set its Enabled property to No and its Locked property to Yes. To show the text that the original label has as the caption, u...

automate the sending of an email at a set date each month
Is it possible to set Outlook to automatically send an email without prompt on a set date each month? Matt T <MattT@discussions.microsoft.com> wrote: > Is it possible to set Outlook to automatically send an email without > prompt on a set date each month? See if this helps: http://www.outlookcode.com/d/forms/skedrpt.htm -- Brian Tillman Doesn't use Outlook but please try our email scheduler (http://www.lbetoolbox.com/scheduleemail.htm) which will allow you to send multiple individual emails, html or plain text, with attachments, either as a one-off or regularly at a sp...

Khmer format number
Hello All, How to make Excel shows number in Khmer format? How to sort Khmer Unicode text in Excel 2003? Thanks Rithy ...

"Automatic Formating" (colored bars)
I can't get the auto format for the colored bars to show. I have several set previously with no problem but I am trying to create one with "the word 'Week' in the subject field" to turn the orange color (or what ever color) but it won't. I've looked for other formats that may be overiding it but none. I have cleared form cache and emptied the Deleted items folder how many autoformatting rules are in the view? Someone else recently had problems with more than 4 autoformatting rules in a view and opening outlook with the /cleanviews switch fixed it - but u...

formatting text as active hyperlinks
I have tried to format cells containing valid urls as hyperlinks. I can copy the format from a valid and active hyperlink; but when I paste it in the cell (I have tried pasting format), the contents look like a hyperlink, but the mouse cursor does not change when I hover over it, and I can't open the web site by clicking on it? Any ideas? -- dpimental ------------------------------------------------------------------------ dpimental's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14848 View this thread: http://www.excelforum.com/showthread.php?threadid=26...

Can't see the number tab when formatting the X axis on a Pivot cha
My x axis has dates like 08/31/07. I want to change the formatting to Aug-07. For some reason when I format the X axis I can't see the "Number" tab. I can see it fine for the Y axis or when I format the X axis on a regular chart, but not the X axis on a pivot chart. I also tried changing the formatting on the source data and the source pivot table. Nothing works for the pivot chart except changing the source data to text which I don't want to do because I need them in number format for other calculations. Please help. have you set the cell format? right-click ce...

Creating a Formula to Format Column automatically? #5
They're just name values, pulled from a database. What was strange i that some of the formats tok correctly, others applied another condito (i.e. Sally Smith was supposed to be green but came out red)P -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26434 ...

Delet data from a field using a timer based on a date range
I would like to setup a code that would delete data from a table based on a date range entered. Here is the scenerio, I have training database. Two fields one for training hire date and the other trainer, I would like to put in a time that says 90 days from hire date the trainer's name is deleted from the trainer field. Is this possible. Please advise. It's easy to create a query that can do that, but there's no real way to make Access run the query automatically. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) ...

conditional formatting
I have a column of dates and I want to highlight in red all dates that are equal to and more than 3 years old For example if the date is 02/10/04 this should be highlighted in red. I would appriciate anyones help! say your date is in A15 ----conditional formal / formula is =and(A15<today()-1095) and format to your colour -- grizz "Teeny" wrote: > I have a column of dates and I want to highlight in red all dates that are > equal to and more than 3 years old > > For example if the date is 02/10/04 this should be highlighted in red. > ...

Wrong Date
Hello, I dont know if anyone has asked this question before as i am new to this. I have Outlook 2000 and when I receive an email it is showing the wrong time by an hour but the date is correct, can someone please help me to ressurect this ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ "forro5" <forro5.100ce0@outlookforum.com> wrote in message news:forro5.100ce0@outlookforum.com... > > Hello, > > I dont know if anyone has asked this quest...

can I make today() static in excel. I need a date formula that ca.
I need a date formula that calculates a date when a cell is populated and then is static. I need to know the date a cell is populated. ...

Conditional formatting 11-15-09
I seem to have problems writing formulas for conditional formatting. H J K 5 98 103 I want to write a rule such that if K117 is greater than j117 and also if H 117>0, then the cell turns yellow or whatever. Can't get it to work. Thanks =3DAND(K117>J117,H117>0) On Nov 15, 4:36=A0pm, JimS <jim...@msn.com> wrote: > I seem to have problems writing formulas for conditional formatting. > > H =A0 =A0J =A0 K > > 5 =A098 =A0103 > > I want to write a rule such that if K117 =A0is greater than j117 and > also if H 117>0, t...

Formatting a 4 Series Stock Chart
The line in my 4 series stock chart is positioned behind the bar, thus partially hidden. Is there any way I can make the bar semi-transparent or move the line to the front of the bar? ...

Querying results for two recent dates
I have a table "DETAILS" in which fields are Name Id TestDate Grade (all fields can be duplicated) Now I want a query which should return me 'Name' 'Id' ' TestDate' 'Grade in 2nd Last Test' 'Grade in Latest Test' It should be noted that every person appears the test many times and i just want the recent two results. The query should return names of only those people who have appeared in either or both of the last two tests I was trying to do this by running a query on a query but the results were ...

Add Requested Ship Date to Allocations_Scroll window
Hi- Would someone please tell me how to replace add SOP_LINE_WORK 'Requested Ship Date' to teh Allocations_Scroll windows? I added a 'Requested Ship Date' to the ivItemAllocationInquiryTemp table. What else should I do? Thanks for any help- Roxy ...

Number Format #7
Is it possible to have a number format for thousands of pounds? eg format '10,000' as '10K' '100,000' as '100K' etc I know this could be done with a formula, but am wondering if it can be done with a custom number format. Thanks Damien One way... Format, Cell, Custom and enter #,K Eamon "Damien" <youwish@nospam.com> wrote in message news:063401c47ad3$1d124040$a301280a@phx.gbl... > Is it possible to have a number format for thousands of > pounds? > > eg format > '10,000' as '10K' > '100,000' ...

Excel format in selected cells have changed to DATE format!!!!
OMG! This is destroyed my spreadsheet, it has happened before... randomly certain cells are changed to DATE format, the NORMAL style changes to DATE format. Changing the normal style back to 'General' number format worked last time, this time, it has ravaged my spreadsheet. What is causing this? I saw this bug mentioned by other people in other posts. What is causing this bug, and how can i avoid it from happening again? You are placing text in the cells that look exactly like dates. Format the cell as text or place a ' in the cell before the text when you place ...

How to determine the what quarters are between 2 dates
Hi, I need to determine how many days fall between a start date and an end date. I am actually able to find that information, but how do I take that total number of days and determine how many days (of the total) fall within Q1, how many fall within Q2, how many fall within Q3, etc. For example if the total number of days between 2 dates is 243, then 66 working days fall in Q1, 66 days in Q2, 66 days in Q3 and 45 days in Q4. I need a function that can capture this information. Please advise. Thanks, MJ A bit unclear but perhaps sumproduct can help =sumproduct((a2:a200>startdate i...

Date formatting issue
In my query I used: Month: Format(Month([DA Peer Review]![Start Time]),"mmmm") I created a query using the above formula so that I could add a month field in my query (i want to be able to generate reports by month) The current test data in my tables are all march dates and the start time field is build off of a command button using the Now formula. My new Month field as written above populated all with January. Since All of my test entries are dated with march dates, it should have returned March. I am wondering what I am doing wrong? Does it matter that my current star...