Fixing the backwards default formatting of a date

I entered a date into Excel (2001) in the proper month-day format and
not only did Excel decide on its own to reformat this cell it created
a string using the incorrect order for the date and month.  In my
example after I typed "5/16" Excel decided to change it to "16-Mar". 
I did not want it formatted, and I certainly didn't want it formatted
in that obnoxious manner.  I went into Format-->Cells and the date
format option list didn't seem to have anything for just
<month>-<day>.  The only available option was to include the year as
well.  Aside from tricking Excel to leave my text alone, how do I get
this date field formatted properly?

Thanks
0
sp1974 (1)
5/19/2004 5:54:23 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
322 Views

Similar Articles

[PageSpeed] 45

Hi
apply a custom format like
MM/DD
for these cells prior to entering data

--
Regards
Frank Kabel
Frankfurt, Germany


sp1974 wrote:
> I entered a date into Excel (2001) in the proper month-day format and
> not only did Excel decide on its own to reformat this cell it created
> a string using the incorrect order for the date and month.  In my
> example after I typed "5/16" Excel decided to change it to "16-Mar".
> I did not want it formatted, and I certainly didn't want it formatted
> in that obnoxious manner.  I went into Format-->Cells and the date
> format option list didn't seem to have anything for just
> <month>-<day>.  The only available option was to include the year as
> well.  Aside from tricking Excel to leave my text alone, how do I get
> this date field formatted properly?
>
> Thanks

0
frank.kabel (11126)
5/19/2004 6:01:26 PM
You could also just format the cell to "text" and it would leave it alone.  This only works if you are not going to refer to the date in any other fomulas

Brandt
0
anonymous (74722)
5/20/2004 1:31:04 AM
Reply:

Similar Artilces:

OL2000: Propotional / fixed-width font settings
What is the significance of the proportional / fixed-width font options under International Font settings in Outlook? Outlook always chooses the proportional option anyway. Furthermore, how does Outlook reconcile the setting under "when composing and reading plain text" vs. the settings under International Fonts? Say I have a plain text message in ISO 8859-1. How does Outlook decide what font to use to display the text? ...

formatting #2
I have a large worksheet that I need to change the text to all caps. I have tried using the UPPER function but it will only work in one cell. How to I get the entire worksheet to become all caps? Help! Hi see http://www.cpearson.com/excel/case.htm -- Regards Frank Kabel Frankfurt, Germany Jackie wrote: > I have a large worksheet that I need to change the text to > all caps. I have tried using the UPPER function but it > will only work in one cell. How to I get the entire > worksheet to become all caps? Help! Hi Jackie If you have trouble using macros you can do this ...

Changing the default folder when inserting files.
Does anybody know how to change the default folder Outlook opens when inserting files? I have already tried the registry with no luck. Thanks. you can change the default folder - use the places bar to select the folder you want. http://www.poremsky.com/p/places_bar.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com "Kristin" <anonymous@discussions.microsoft.com> ...

Formatting unprotected sections
Hi, I'm currently working on a template (.dotx) for Word 2007 where I want to protected several sections with the 'Filling in forms' protection. So far it's ok - but in the unprotected section I'm unable to use things like 'Bullets' and 'Numbering' - why is this, and mere importantly how do I solve this? Any help would be greatly appreciated /JEN As you are using Word 2007, consider using Content Controls rather than formfields. Alternatively, use a UserForm (Custom Dialog) to get the user's input See http://gregmaxey.mvps.org...

Format Shape With ThemeColors
I can format shapes with RGB ActiveSheet.Shapes("Test").Fill.ForeColor.RGB = RGB(128, 128, 128) Is there a way to format a shape using themecolors in the same way you can format a cell With Range("A1").Interior .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.799981688894314 End With I have tried every variation of the code & syntax to no avail - could someone give me an example of the code to do this please - thankyou in anticipation This is the sort of syntax you are looking for: ActiveSheet.Shapes.Range(Array(&qu...

default NTFS permissions on system partition : everyone group in or out
When setting up a new server, is the Everyone group going to have full control to the C: drive (system partition) by default? This is a big problem when people who are not part of the domain can connect to C$ (if they know what they're doing). Can anyone think of a single good reason why Everyone should be allowed full access to the C: drive? (When we took the Everyone group out on the Exchange2k server, we ran into instant problems - users called to say that they had lost their connection to the server.) Thanks in advance. That is not true. NTFS permissions may be set to give ever...

Im desperate to fix my fragmented memor...will advanced server save me with the 3 gb switch?
Im desperate to fix my fragmented memory, will advanced server save me with the 3 gb switch? The Situation: -Server=Windows 2000 Server sp4. -2.5 gb of RAM -Exchange=Enterprise Ed with all service packs and hotfixes. -Group Shield virus scanning Every month I recieve eventid 9582's errors, and then 12800 mail processing errors. THIS IS FREAKING KILLING ME AT WORK. Here is what I have tried. -Regedit ajustment to the heep. -ADSI ajustment to the IS (which oddly removes the errors from the event log, however MOM still detects low virtual memory errors). -sp3 (which claims to have fixed ...

Conditional Formatting #89
I need to know how to make a block turn RED if the date exceeds a year. Thanks! Do you mean if the data exceeds one year from today? If so, select the target cell, go to Format > Conditional Formatting, choose Formula Is, and put: =$A$1<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) Change $A$1 to your actual cell reference. HTH Jason Atlanta, GA >-----Original Message----- >I need to know how to make a block turn RED if the date exceeds a year. Thanks! >. > Try this in - Conditional Format - Formula Is: =DATE(YEAR(E1)+1,MONTH(E1),DAY(E1))<=TODAY() If E1 was ...

Zoom percentage and formatting palette
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel 1. How do you make sure that Word opens to a certain zoom percentage (140% for me) no matter what Word document I open? <br><br>2. How can I get the formatting palette to not be a floating box but to be incorporated right into the toolbar? <br><br>Thanks from a new user On 24/05/2010 03:00, Jerdog201@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel 1. How do you make sure that Word opens to a certain zoom > percentage ...

report format portrait and lanscape
Is it possible to have one command to change report between portait or lanscape? If not, probably the best solution will be 2 reports. Your information is great appreciated, Your report is probably designed in portrait. This means it is designed to print 8-1/2" - the left and right margins wide. If you change the printout to landscape it is still going to print less than 8-1/2" wide on the left side of the paper leaving more than 3-1/2" blank on the roght side of the paper. To fill the paper you would have to write code to reposition and stretch out each con...

Community bank's new software doesn't export in my format
I am still using Money98 which does all of the little tasks I need (no investments at my age). Two national banks provide exports in .qif format as a matter of course, but when my local community bank went to new (better) software, ".qif" was missing from the list of available export formats. I wrote the bank and received word that the bank's vendor didn't support Money98. The bank officer said: "My guess is the contract they have with Intuit and Microsoft stipulates they not support it as those companies want consumers to always upgrade their software.&...

Question on Formats...
This ought to be quite simple to answer. Is it possible to format the contents of a combobox? On sheet1 I have text in cells A1 to A8 e.g A1 = John, A2 = Steve, A3 = Claire etc. I have a combobox on a chart that displays those names when I click the down arrow. The default is lower case, black lettering. Can I make the names appear in bold and green for example? I have tried modifying the original names but that makes no difference. If I right click the combobox and select <Format Control> I cannot see a way to format my data entries. Maybe you can't do it. And I want to ...

Excel page format changes
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hello, <br><br>My client creates spreadsheets for her boss. She pushes the boundaries to get things to fit on the screen. <br> Uses A4, margins at 1.5cms and scales to 90%. <br><br>When her boss receives it by email the margins go to 2.5cms, scales to 100%. They have the same versions 12.2.4 and Leopard. They are just about to be upgraded to Snow Leopard. Any idea why this could be happening? Thanks. ...

two decimals - remove percentage sign
Hello everyone, I have percentages that i'm displaying in graphs with data labels. I'd like to remove the % sign so it is easier to view the data labels because the graphs are small, and i'd like to do it with custom formatting because there is quite a lot of data that i dont want to convert to whole numbers. I think the best way to do this is to somehow use custom formatting in format cells -->number-->custom to multiply by 100 and display it that way. is that possible? Thanks for the help! Adam ...

Date on Template
I created some templates and am choosing to bring over a date to be shown in the template when it is run. I have selected this attribute to be date only and not date & time. Every time I run the template it still shows date+time+time zone. Is there a way to show only the date and not the rest of the data. Thanks for any help anyone can provide. ...

Calculate Dates with If Then Else Statements
I need to calculate total years’ full time experience (i.e. 2.5 years, 3.25 years, etc.) in an Access 2003 database form. This is what I am trying to do: If the Full-Time End Date is Blank, use Today's Date, ELSE if the Full-Time End Date has a Date, use that Date. Separately, these two formulas work: =DateDiff("m",[FullTimeStartDate],Now())/12 =DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12 My problem is writing a statement to calculate using the END DATE if one is entered, or NOW, if there is no end date. I have tried many combinations, but can’t ge...

OL 2003; Question about two default attachments in HTMLMessage Format
This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C51E85.7F26CCA0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0010_01C51E85.7F26CCA0" ------=_NextPart_001_0010_01C51E85.7F26CCA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Outlook 2003, XPP SP2=20 Hello, When I send an e-mail message in HTML message format, the recipient = (even when I send to myself) also receives two default attachments, = image001.png and image002.png. The first image is a what I would guess a = Microsoft ...

Filter Date
Hi, I have a form which contains a listbox (holding the values Process Date and Quantity) and I have a textbox which displayed the current month. What I want to do is when I click on a command button (cmdMonthDown) and the textbox value changes from May to April I want the values in the listbox to be filtered so that only those values are disiplayed that were entered in April. If I'm not making sense please let me know otherwise your help would be appreciated. Thanks Reference the text box as a parameter in the list box's RowSourcee, e.g. if the text box shows the month name in fu...

Task start/end dates from MS project to Workspace site
We are currently running MSPS 2007 with WSS 3.0. I am trying to create a list in one of the project work spaces. I would like to auto-populate two columns in that list with the start and end date of certain tasks from the Project Plan in MS project. I am thinking we can do this by linking these columns to the the project SQL database fields. So, 1) Do I use the published SQL databse for this? 2) Which fields in the SQL database would I have to link to get the Start and End dates? EggHeadCafe - Software Developer Portal of Choice AutoList in ASP.Net http://www.eggheadcafe.com/tutorial...

Date Format when incoming date can be 0
I have a date fiels yyyymmdd which can have a value of 0 or a valid date in recent past. Excel displays the 0 date as 01/01/1900 and the non zero dates as expected. Cant figure this out , any help would be appreciated. Excel uses a sequencial number from either 1/1/1900, or 1/1/1904 (depending on your options) as the date value for Example 6/3/2005 is 38506 using the 1/1/1900 if the cell is formatted as a date it is responding with the date it thinks you mean. If you want the cell to display a zero =if(Date=0,"0",date) "DP NY10601" wrote: > I have a date fiels y...

Publisher defaults
Is there a way to change the document defaults for (1)zoom size at startup, and (2) do not hyphenate? I would like to have the zoom defaulted to 100% and they hyphenation defaulted to "off." Tools, language, hyphenation The Zoom cannot be defaulted. -- Mary Sauer MS MVP http://dgl.microsoft.com/ http://mvps.org/msauer/ "Rebecca" <rebecca.bogorad@kpchr.org> wrote in message news:09a401c34d4e$a7fed730$a001280a@phx.gbl... > Is there a way to change the document defaults for > (1)zoom size at startup, and (2) do not hyphenate? I > would like to have the ...

how to calculate the substraction between two dates
hi, i have two cells ,in which there are two dates. (for instance ,one cel l 08/02 ,the other 07/30) if the gap of the two dates is 2 days , something will be set to do. but it is difficult to judge the the substraction between two dates any good ideas. thanks Perhaps this might be of help .. Assume the *dates* "2-Aug-2004" and "30-Jul-2004" are input in A1 and B1, and A1:B1 is custom formatted as: mm/dd viz. it'll show as: In A1: 08/02 In B1: 07/30 Try in C1: =DAY(ABS(A1-B1))-1 Format C1 as: General or Number C1 will return: 2 i.e. the number of days...

Date Range
Hi, When I want the date rage appeared on the report header, I use textbox and put (qry criteria) in its recordsource. It works fine. However, For this time, I got a message like this ‘First([Between [From this date] And [To this date]])" Is there something wrong with the qry criteria? Would you please show me how to fix the problem? Thanks Chi It's very hard to understand the crtieria, can you post the full SQL What is the First in the beginning? Why there are to square brackes in the end And in the beinning? First([Between [From this date] And [To this date]])" T...

Word paragraph default
I can't seem to be able to change the default paragraph style. i have gone into word options, advanced and selected no spacing as default style. But it doesn't hold. Does anyone know how to fix this? If you are using Word 2007 and you want the look of Word 2003, do the following: Create a new, blank document. On the Home tab, click Change Styles | Style Set | Word 2003. Then click Change Styles | Set as Default. If/when you are prompted to save your Normal template, choose to do so. -- Stefan Blom Microsoft Word MVP "zoriana" <zoriana@discussion...

conditional formatting: separating rows with differing values
Hi, Need help with conditional fomatting. What I need: I got a worksheet with 950 rows and 5 columns. The first row contain the headers. Columns A, B, D and E contains unique values. But the column C contains text values which repeats sequentially for 6-7 rows and changes thereafter. Now I want to put up a line (using border) through the whole row dividing this transition row. My Idea: Compare C2 with C3, if equal do nothing. Then compare C3 with C4 and so on till 2 consequtive values differ. Now divide the 2 rows using the border formatting option in conditional formatting dialog box. So...