Changing date format of cells

Hi,
I have an excel sheet that contains some dates (sample given below),
entered in the "dd/mm/yyyy" format. But it seems excel is interpreting
them as "mm/dd/yyyy". Whenever i try to do any date related stuff, such
as change the format or group them together etc. It throws up on dates
like "15/1/2006" and interprets "11/1/2006" as November - 1st - 2006

Is there any way to change the way excel interprets these dates? Make
them read them as "dd/mm/yyyy" rather than "mm/dd/yyyy"? Failing that,
is there someway to automatically convert (using a macro for example)
from one date format to other by swapping the day month fields? I just
don't want to sit there and change/re-enter the dates manually

9/1/2006
10/1/2006
11/1/2006
15/1/2006
16/1/2006
16/1/2006

Any help is much appreciated.

0
2/20/2006 11:24:00 AM
excel 39879 articles. 2 followers. Follow

2 Replies
507 Views

Similar Articles

[PageSpeed] 4

The general format of dates on your system is defined in Windows,
system panel, regional and language settings. If you enter a date it is
interpreted this way. If your settings were: DD.MM.YYYY and you would
enter 02.25.2006 it would be treated as text, not date.

Once you have antered a valid date, the formatting (format, cells, date
or custom) defines how you want the date displayed.

Use these to find how your entered dates are interpreted:

=day(a1)
=month(a1)
=year(a1)

Try formatting an input field and choose the custom formatting option
and in the "type" box enter: DD/MM/YYYY (use capital letters)

If that doesn't help, have a look how your fields are formatted (text
or date) and you may have to come back.

Hans

0
hansyt (53)
2/20/2006 12:06:28 PM
The general format of dates on your system is defined in Windows,
system panel, regional and language settings. If you enter a date it is
interpreted this way. If your settings were: DD.MM.YYYY and you would
enter 02.25.2006 it would be treated as text, not date.

Once you have antered a valid date, the formatting (format, cells, date
or custom) defines how you want the date displayed.

Use these to find how your entered dates are interpreted:

=day(a1)
=month(a1)
=year(a1)

Try formatting an input field and choose the custom formatting option
and in the "type" box enter: DD/MM/YYYY (use capital letters)

If that doesn't help, have a look how your fields are formatted (text
or date) and you may have to come back.

Hans

0
hansyt (53)
2/20/2006 12:06:47 PM
Reply:

Similar Artilces:

excel date shows 01/00/1900!
I have a whole spreadsheet where when I type in the date i.e. 3/5/05 it then shows 1/00/00 as the date! I have tried changing the format to the many different ways to show the date, but I can't get it to show 3/5/05! Is there something I am missing? Thank you so much for your help! Katie I'm guessing that you entered the date in the cell like this =3/5/05 If so, a calculation is being done. I get 1/0/1900 when I enter it that way. Try just entering it as 3/5/05 with no equal sign. "KateZito" wrote: > I have a whole spreadsheet where when I type in the date...

How to put multiple data format on clipboard?
I need to put both my custom format and CF_TEXT format This is the code to copy custom format to clipboard, COleDataSource *pSource = new COleDataSource() UINT nCount = textList.GetCount(); int nSize = sizeof(TelopTextFormat) * textList.GetCount() + sizeof(long); HGLOBAL hClipMem = ::GlobalAlloc(GMEM_SHARE, nSize); LPVOID lpClipMem = reinterpret_cast<LPVOID>(::GlobalLock(hClipMem)); memcpy(lpClipMem, reinterpret_cast<LPVOID>(&nCount), sizeof(UINT)); ::GlobalUnlock(hClipMem); pSource->CacheGlobalData(CF_TELOPTEXT, hClipMem); pSource->SetClipboard(); What should I do then...

Format date in excel 2000 like '31st January 2005' #4
Trying to set up field in Excel Data Source file so that the current date can be input in letters typed in this format '31st January 2005' Is it possible or am I stuck with dd/mmm/yy format? Good afternoon Malcolm Agingwell Would the date in the format "31 January 2005" be of any use to you? If so, set up a custom format of dd mmmm yyyy. Excel doesn't handl ordinals (1st, 2nd, 3rd etc) on its own. HTH Dominic -- dominic ----------------------------------------------------------------------- dominicb's Profile: http://www.excelforum.com/member.php?action=getin...

Date changes back 4 years when copying
When we copy a date from an excel 2000 worksheet and paste it in an excel XP worksheet the year will move back four years. Any ideas? Go Tools > Options > Calculation and make sure both files have '1904 Date System' either checked or unchecked, ie both the same, Regards "Dave" <anonymous@discussions.microsoft.com> wrote in message news:328401c4a4e0$a7dacb20$a301280a@phx.gbl... > When we copy a date from an excel 2000 worksheet and paste > it in an excel XP worksheet the year will move back four > years. Any ideas? > > In article <328401...

Count of Weekdays Between Two Dates
Task: With a given start date and end date, print a list of Months showing a count of weekdays and weekend days in each. It seemed like such a simple request... but how to get started? If I already had a table of all dates between the start and end dates, I'd have it whipped. Any thoughts appreciated. -- croy Here are some ideas you can use for this. First, you need to know how may days are in the month. Then you can use the function below to return the number of work days in the month. The subtract the work days from the number of days, and it will return the week end days. ...

how do i convert excel to csv format
I am trying to convert multi tabs excel file into csv. Can I get all the tabs in csv format in a same file? Tks If you combine all the data into one worksheet in a new workbook, then you could save that workbook as your CSV file. Ron de Bruin has some code at: http://www.rondebruin.nl/copy2.htm that might get you started. baxterr wrote: > > I am trying to convert multi tabs excel file into csv. Can I get all the tabs > in csv format in a same file? > > Tks -- Dave Peterson ...

See if cell is in Range
Thanks for taking the time to read my question. I'm passing a string that is a cell reference to a function. In that function I want to determine if that cell reference is within a predetermined range. Not sure how to do that. Right now I have: Function CheckRange(TheSheet As String, TheCell As Range) As Boolean If TheSheet = "Sheet1" Then if TheCell In Range("B4:B30") then 'This line is red as it is incorrect CheckRange = True End If What do I use instead of "In"? Thanks, Brad Brad Use something like: If In...

Editing in a cell
I am having a problem with a newly created workbook. When I go to a cell that has data in it and try to add more data to the existing string I lose all the previous information. The work sheet that I am working in is protected and I have "edit directly in cell" checked in the options section. It is almost like my double click (as well as F2) are being treated as a single click. Thank you I figured it out. For some reason "Hide" is selected for the unlocked cells. "John English" wrote: > I am having a problem with a newly created workbook. > > Wh...

first value of the month from a list of dates
Hello gurus, I've read through a list of message thread, and you guys are wonderfu ! . Experts !. Anyway, I hope somebody can help me with this problem. I have a list of share prices together with the dates. (about 20 to 2 dates in a month). How do i extract out the first day of every month from a list o database of 3 years (day by day) ? I think hard, but no avail. Help... -- Message posted from http://www.ExcelForum.com Hi one way: - add a helper colum adjacent to your data list (lets say column C) - in C1 enter the formula: =DAY(A1) ->if column A stores your dates - copy do...

Money signs appear in my Cell and I don't want them there
Im trying to enter the numbers 2.9 in cell like AD and it turns it into $2.90. That is not what I need, how do I stop that? Format as General instead of currency -- Regards, Peo Sjoblom http://nwexcelsolutions.com "kate" <kate@discussions.microsoft.com> wrote in message news:E9EEA936-437C-486F-A2D3-2385EF6BD2D0@microsoft.com... > Im trying to enter the numbers 2.9 in cell like AD and it turns it into > $2.90. That is not what I need, how do I stop that? ...

How do I get a cell to remember the last entry and add to it?
I am using Excel 2000. I want my cell to remember the number in it and add an additional number each time I need to retotal. Jab Sounds like you want that cell to be an accumlator cell. Simple question with complex results. You can have a cumulative total in a cell if you have a separate source cell for adding a new total to the original. Use at your own risk. I am Posting this just to show you how it can be done, not as a good solution. You would be much better off to have another column so you can keep track of past entries. Goes like this: =IF(CELL("address")="$C$4&...

File is not in a recognizable format.
Everytime I open Excel 2000, I get "This file is not in a recognizable format". I wonder if I have a corrupt default file. I tried to replace book.xlt with a newly saved book.xlt, it didn't help. I'd appreciate any help. Thanks, Liz Sounds like you have a file in your XLSTART directory that doesn't belong there. Use Windows' Find to locate the directory. If the rogue file isn't obvious, move all files out of that directory, then move them back one at a time, restarting Excel and quitting each time, to see which one is causing the problem. On Wed, 22 Sep 2...

Conditional Formatting Anomaly #2
No, because I want it to say in the B column, but when I copy it down want the row to change. Any ideas -- Rutgers_Excel ----------------------------------------------------------------------- Rutgers_Excels's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=897 View this thread: http://www.excelforum.com/showthread.php?threadid=27432 Ok, lets start over. Conditional Formatting is based on the active cell. You can copy the formatting using the format painter -- though it would actually be better to do all of your formatting even if for the entire column ahead ...

Change Quote.dot
Can someone please tell me how the make changes to the Quote.dot. There is some text on the last page I need to change and for some reason I can not get past the merge fields to do this. Hi Valerie, Can you please help me understand exactly what text you need to change? -- Manisha Powar (Madhusudanan) Program Manager Microsoft Dynamics - CRM This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use. � 2005 Microsoft Corporation. All rights reserved. "Valerie" <Valerie@discussions.microsoft.com> wrote in mess...

Date prompt appearing twice due to chart in report
Hi All, I am creating a report based on a query. I have a prompt to ask for the Beginning Date and the Ending Date. When I run the query I get the correct results. I also get the correct results when I run the report. However, I have just added a pie chart in my report footer, and because of this, I am always forced to enter the beginning and ending dates twice when running the report. What do I need to do to make the prompt only appear 1 time for each date? Thank you! decklun The prompt appears each time Access needs to run the query. Once for the report and a second time for ...

POP connector
Hi, I'm a total newbee to Exchange! I've just installed SBS 2003 and configured the POP3 Connector in Exchange to retrieve mail messages from the POP mailbox to my Exchange mailbox. Everything works fine besides that all mail messages get the date from synhronization in stead of the actual mail message date. Can this be configured so the date of a mail message in the Exchange mailbox is the same as the actual date of the mail message in the POP mailbox? Thx, Dennieku On Fri, 22 Jul 2005 08:13:50 +0200, "Dennieku" <dennieku@hotmail.com> hired a team of monkey...

How do I enter the date last modified in the footer of an Excel d.
How do I enter the date last modified in the footer of an Excel document? I want to be able to distribute a monthly or weekly report in Excel to users and have it automatically update the date last modified in the footer each time I update the report. You need to do that with a Before_Print event macro. If you search the newsgroups from Google you will find lots of examples. On Fri, 4 Feb 2005 09:33:04 -0800, "Last modified date in footer" <Last modified date in footer@discussions.microsoft.com> wrote: >How do I enter the date last modified in the footer of an Excel doc...

Changing the user password without the use of SA or DYNSA accounts
Is there any way to save a user password in GP v10 without the use of the SA or DYNSA login? I would like to grant security to one user to do this. Currently the save button is greyed out unless they log in as SA or DYNSA. Thank you You can do this by granting that user sysadmin rights in SQL management Studio. In SMS, expand the Security folder, then logins, then find that user, right click the user, go to properties, click server roles and check the box for sysadmin. "Junior De Alba" wrote: > Is there any way to save a user password in GP v10 without the use of the S...

Oops! can I change border now?
Well I've just spent months working on my first Publisher document. I've created a 34 page catalog which looks quite nice... I spent a lot of time making sure the master page was nice and even. I did a test print out and it looked fine...however... I forgot that the pages will be stapled...and that though the catalog is acceptable the way it is...I should really move the entire printed area over slightly to the right. Since all pages have used the same master page layout...is there some simple way to get all objects shifted over...on all pages? It would be way too time consuming to mo...

userform number formatting
Hi, having entered a number into a textbox in a userform how does one then format it to have 2 decimal places? its not too important as they are formatted in the cells they end up entered into, it would just make it easier on the eye. Rick Tom Ogilvy just posted this for someone else. It does a little more than you ask, but may be what you want <bg>. Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If IsNumeric(TextBox1.Value) Then If Len(Trim(TextBox1.Value)) = 1 Then TextBox1.Value = Format(CLng(TextBox1.Value), "0000") Else M...

Excel graphs should allow copy & paste of individual bar formats
In excel graphs - bar charts, I make the last bar a hashed shading as it is a forcast. To do this I select only the last bar and change its formating. At the end of the month I want to change the format back to the same as all the other bars and make the new coming month's predicted value that hashed shading etc. Unfortunately excel will not allow copy and paste in this situation - so I have do open the format tab etc and then change. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggest...

Date function help
I have order received date. I will start the order on the next comin THURDAY always. My delivery date is 9 days from the start. eg. Received date = 5/7/2004. Start Date is = 8/7/2004. Delivery dat will be 17/7/2004. Can somebody help with a formula to calculate delivery date. By the wa we are into furniture manufacturing. Thanks in advance, Itt -- Message posted from http://www.ExcelForum.com > ... My delivery date is 9 days from the start. > .. a formula to calculate delivery date. If col B contains the start dates, from row2 down just put in say, C2: =B2+9 (think Excel will fo...

looking for range of text in a single cell
I just started a new job and my company already had a file with abou 16,000 records in it (called Products). This file contains all of th parts numbers that they carry. In this file there is one column calle "description". This cell contains the dimensions, color name and som other information such as if the piece is flat or rounded. I hav another file that has about 400 records and this file tells me "colo name" as well as the "type" of stone that it is (called Stone_Type). What I am trying to do is add a field to the Products file that wil say stone type. ...

change tab colour of a worksheet
How do I change the colour of a worksheet name tab so that I can identify groups Sally, right click on the tab, but only if you have excel 2002 or above -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 ** remove news from my email address to reply by email ** "Sally" <Sally@discussions.microsoft.com> wrote in message news:6FA9969D-F3C6-47EE-B02A-E8BCC3706F0E@microsoft.com... > How do I change the colour of a workshee...

Multiple reports in range date
I have multiple reports using the same range of start and end date weekly. The date is display in a tab form to remind me the date range. I like to print weekly multiple reports using these range of date. Can anyone help me. Thanks. -- Message posted via http://www.accessmonster.com Create a form with two unbound textboxes to enter your dates. Use the textboxes as criteria in the query for the reports like this -- [Forms]![YourFormName]![TextBox1] and [Forms]![YourFormName]![TextBox2] Before running the reports open the form, enter the dates, leave form open...