Date formatting won't work

I would like to enter dates in a field using dd/mm/yy, however I want it to 
display the DAY also ie: ddd dd/mm/yy. I have tried to format the cell in 
custom formatting however it displays exactly what I have typed, even though 
the formatting indicates it should read the way I want it to.
I have just purchased this copy of Office 2003. Any ideas would be 
appreciated.
0
Natman (2)
8/19/2005 10:15:05 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
114 Views

Similar Articles

[PageSpeed] 39

It is probably because you are entering a date like 31/12/2005 and excel is
trying to read it as mm/dd/yyyy and since there is no month = 31 it converts
the whole thing into text string.

Always enter as say 31 dec 2004, and whatever format you have used, it will
get converted automatically

Mangesh



"Natman" <Natman@discussions.microsoft.com> wrote in message
news:B22872E5-DE88-458A-AE41-FD2D83045B9D@microsoft.com...
> I would like to enter dates in a field using dd/mm/yy, however I want it
to
> display the DAY also ie: ddd dd/mm/yy. I have tried to format the cell in
> custom formatting however it displays exactly what I have typed, even
though
> the formatting indicates it should read the way I want it to.
> I have just purchased this copy of Office 2003. Any ideas would be
> appreciated.


0
8/19/2005 10:30:21 AM
Thanks for your reply Mangesh, you are correct it does work that way, however 
I think in previous versions of excel I have been able to enter it the other 
way (ie. the Australian way).
 
Natman

"Mangesh Yadav" wrote:

> It is probably because you are entering a date like 31/12/2005 and excel is
> trying to read it as mm/dd/yyyy and since there is no month = 31 it converts
> the whole thing into text string.
> 
> Always enter as say 31 dec 2004, and whatever format you have used, it will
> get converted automatically
> 
> Mangesh
> 
> 
> 
> "Natman" <Natman@discussions.microsoft.com> wrote in message
> news:B22872E5-DE88-458A-AE41-FD2D83045B9D@microsoft.com...
> > I would like to enter dates in a field using dd/mm/yy, however I want it
> to
> > display the DAY also ie: ddd dd/mm/yy. I have tried to format the cell in
> > custom formatting however it displays exactly what I have typed, even
> though
> > the formatting indicates it should read the way I want it to.
> > I have just purchased this copy of Office 2003. Any ideas would be
> > appreciated.
> 
> 
> 
0
Natman (2)
8/19/2005 10:41:02 AM
On Fri, 19 Aug 2005 03:41:02 -0700, Natman <Natman@discussions.microsoft.com>
wrote:

>Thanks for your reply Mangesh, you are correct it does work that way, however 
>I think in previous versions of excel I have been able to enter it the other 
>way (ie. the Australian way).

It may be a Windows setting and not an Excel format issue.

Check Start/Control Panel/Regional Settings.  Excel should parse your date
entry per the format of the short date at that setting.


--ron
0
ronrosenfeld (3122)
8/19/2005 5:59:18 PM
Reply:

Similar Artilces:

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Fractions Number format
Is it possible to do a custom number format for cells such that fractions are displayed. I'm guessing not because there'd be a limit on what fractions you could display, but I would probably only be interested in showing a half as the little 1/2 (alt,0189 I think) rather than .5 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.478 / Virus Database: 275 - Release Date: 06/05/03 Lee There are a limited number of fractions that can be shown under Format>Cells>Fractions. Gord Dibben Excel MVP - XL97 SR2 & ...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

Ctrl+D not working on outlook messages
Any idea why? alltimefav wrote: > Any idea why? And what are you expecting to happen? "alltimefav" wrote in message news:a95c52df-a387-4a8f-bf9d-269ff485451f@i29g2000prf.googlegroups.com... > Any idea why? So instead of hitting Ctrl+D to delete an item, what happens when the item is selected and you hit the Del key, or the "X" toolbar button, or right-click on the item and select Delete? Did you really expect a detailed response for such a vague question? You didn't even bother to say what "not working" means, like the item does not get delete...

how to make macro work even a sheet is hidden.
hello there I have macro assigned to a button in Sheet 1 which goes to sheet 2. Press a button to Refresh and then PRINT preview It does work in normal state. I do not want the user to see the Sheet 2. I hid sheets from Format-sheet-hide. but the macro is not working when sheet 2 is hidden. how to overcome this error "can't execute code in break mode" the macro code is Sub Print_Preview() Sheets("PaySlips2009-10").Select Application.Run "'Latest 2009Payslip.xls'!Sheet2.HURows" ActiveWindow.SelectedSheets.PrintPreview ...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

How to save Japanese characters in CSV format of Excel ?
Hi, I would like to know, how to save the Doubly-Byte characters.For example, Japanese in CSV of Excel.Here are the steps, I tried. 1. Open Excel 2. Copy pasted the Japanese charactesrs in a cell.It is displaying the characters correctly. 3. Save as CSV(Comma seperated file) It is saving as ??????. Thanks in advance for the info. ...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

why does my spell check not work?
My spell check has never worked. What can I do to set it up? In what program / version "karen" <karen@discussions.microsoft.com> wrote in message news:DA8883BD-0958-4AB0-93FF-F43E4FB9D176@microsoft.com... > My spell check has never worked. What can I do to set it up? ...

date modified
I have two sheets Data and Summary The "data" sheets macro extracts data from external file and paste into "Data" sheet Everytime the m acro is run to get latest data... The macro delete all contents of the "data" and then paste new data into the "data" sheet. Is there a way.. I can put a date on the "summary" sheet, when was the time the macro was run ( or in other words.. the data updated) This little macro records the date in the selected cell and formats it: Sub Macro2() Dim d As Date Dim s As String d = Now() s...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

Word 2003: Will it work on Windows 7 Pro
The ads say there is virtual XP on Windows 7 that can run programs like Word 2003. In your experience is this true? On any Windows 7 machine? Does 64 versus 32 bit processor matter for either Word/Office 2003 or Office 2007? thanks. You don't need Virtual XP to run Office 2003 on Windows 7. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "removing all headers and footers" <removingallheadersandfooters@discussions.microsoft.com> wrote in message news:95298031-44F6-4E74-A608-C76987DFAB46@microsoft....

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

Selecting the Right Text Alignment for a edit box doesn't work
When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong Thanks Dan Dan, "Dan" <anonymous@discussions.microsoft.com> a �crit dans le message de news:DECFE605-A130-416B-9924-60BA0C79D684@microsoft.com... > When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong? > I've no idea :-))) You can open your RC-file as text, and make sure it has the ES_RIGHT style set, thus: EDITTEXT IDC...

Formatting in CSV
Hi I create a Comma Separated Value file (csv) from my VB application, then open that file with Excel. Excel recognises the commas, and splits the records into separate fields as expected. Can I set properties for rows or columns or cells ? Like bolding, or font size, etc. Thanks Robert -- RobertLees ------------------------------------------------------------------------ RobertLees's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26580 View this thread: http://www.excelforum.com/showthread.php?threadid=486488 Yes. Even though the .csv file contains no fo...

How to Identify Records with Overlapping Dates
I need to be able to systematically identify any instance where there are overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was < Term date TableA_1 and TableA_1 Begin Date < TableA End Date. Which worked great for me finding overlapping records where the product was different, bu...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

Format List Box!
Can I format a list box which has a query,so if one of the fields called [Spelling] had the word "Spelling" it will change the colour of the font for that row Thanks for any help....Bob It's not possible with the native Access ListBox control. Instead, setup a Subform control to resemble a ListBox and use ConditionalFormatting to achieve the desired look. I think Arvin has a sample on his site here: http://www.datastrat.com/Download2.html -- HTH Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Please respond only to the newsgroups so everyone can ben...

How can I sum only amounts that are in BOLD format within a column
Hi! I need to get a total from a worksheet that has hundreds of amounts in it. However, I only need the total of the amounts that were marked with have BOLD font. Please help me I don't have much experience with EXCEL. Thank you very much! W a n d a try this for column D Sub sumbold() x = Cells(Rows.Count, "d").End(xlUp).Row For Each c In Range(Cells(2, 4), Cells(x, 4)) If c.Font.Bold Then mysum = mysum + c Next MsgBox mysum End Sub -- Don Guillett SalesAid Software donaldb@281.com "Wanda" <Wanda@discussions.microsoft.com> wrote in message news:89A268A6-...

Outlook 2003
Hi Is it possible to order the deleted items by date of deletion in outlook 2003? Many thanks Lee Lee Atkinson <leeatkinsonlincs@hotmail.com> wrote: > Is it possible to order the deleted items by date of deletion in > outlook 2003? Use the Field Chooser to add the Modified time to the Deleted Items folder header bar (the one that says "From", "Subject", "Received", etc. and sort on that time. Deleting an item is a modification, so I would expect the modified date to be affected, giving you the most accurate value of the item's deleted time....

Newbie
I have a spreadsheet that is little more than a list of PO #'s that shows what orders are done and ready to ship for a particular customer. We upload the file every day to a website where the customer can view it whenever they want. So far, everything's fine. I use the "=TODAY()" function so that when I open and modify the listings, the date gets automatically updated. The problem is, when the customer accesses the file, the date also updates on THEIR end and the file looks more current than it really is. Is there a way to maybe lock in the date ONLY when I save the file...