date cell format

i am working with an external data set (european) in which
the date cell is formatted as this:
dd.mm.yy
when i sort by date, excel does not recognize the format
and thus the initial sort is by day instead of by month.
is there a way to fix this or get around it so i can sort
by date and the result will be sequential?
thanks!
acb
0
anonymous (74722)
12/10/2003 3:05:34 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
444 Views

Similar Articles

[PageSpeed] 33

I'm not sure what's happening for you...but I have 10 cells with 
custom format of dd.mm.yy When I sort them, they do sor
sequentially...I'm using Windows 2000 and Office XP.

Wish I could be more of a help.

Good luck,

Dave M

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

0
12/10/2003 3:47:29 PM
It sounds like they're not really dates--just text pretending to be dates.

Try this against a test copy
first close excel
windows start button
settings|Control panel|regional settings
Date format
make sure your short date is the same order as your data (dmy).
Apply and back to excel

Open your workbook.
select your range of cells and 
edit|replace
..
with
/

Excel will see this as a date.

You can give it a custom format of dd.mm.yy and it'll look the same, but sort
nicely.

(don't forget to change the windows regional settings back when you're done.)



acb wrote:
> 
> i am working with an external data set (european) in which
> the date cell is formatted as this:
> dd.mm.yy
> when i sort by date, excel does not recognize the format
> and thus the initial sort is by day instead of by month.
> is there a way to fix this or get around it so i can sort
> by date and the result will be sequential?
> thanks!
> acb

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/11/2003 2:27:16 AM
Reply:

Similar Artilces:

Save copy of active sheet
I use the code below to save a copy of my Workbook in a specific file path (testing to see if the folders already exist along the way) with a specific, data dependent file name. It works, but I'd like to tweak it a bit and I'm not sure how to write the code. I'd like to save only the active sheet instead of the whole workbook. I'd also like to save only the values and formats to the new file, not the formula and macros. Anyone have any advice on how to accomplish this? Here's my current code: Sub SaveName() If Not Len(Dir("g:\users\one\" & Range("h6&...

Date conversion question
Am trying to understand why is that when I have a variable MaxDate (undeclared) and is has a value of 38697 and if I use =TEXT(MaxDate,"mm/dd/yy") I get the correct value of 12/12/09 but if I use function =CDATE(MaxDate) I get 12/11/2005 as an output? Also the format command didn't work either to convert MaxDate with the code =format(MaxDate, "mm/dd/yy") as it also gave 12/11/2005 when the correct output was 12/12/09. I am trying to understand this. Thanks, Chet Assuming you are using XL2003 or lower... if you click to Tools/Options = on the menu bar, th...

Testing for an error in a range of cells...
I searched through the previous posts on this topic, but have almost no experience using VBA in excel, so I was wondering if there was a simpler way to achieve this: I want to write a formula in B2 that will basically say, "If the range of cells B3 to B24 contains an error, return x if true, and return y if false." I know how to do this for a specific cell rather than a range, for example (=IF(ISERROR(B2),"x","y"). Any help in achieving this without delving into VBA would be much appreciated (I just don't have the time to start learning this stuff as I am w...

Combine Text from Multiple Cells
How do I combine text from mutliple cells into one cell? Hi Carter one option - well two really: if the information is in cells A1, B1 & C1 and you want it combined into D1 then in D1 type =A1 & " " & B1 & " " & C1 this will give you the contents of each with a space between OR =CONCATENATE(A1," ",B1," ",C1) which will give you exactly the same thing Hope this helps Cheers JulieD "Carter" <anonymous@discussions.microsoft.com> wrote in message news:5fc401c48ac0$9cc86830$a301280a@phx.gbl... > How do I combine tex...

Report Writer and effective dated tables
Hi, I am new to using report writer. I am trying to write a report that will just pull back the latest effecitve dated row by employee. A SQL statement that can do this would be something like: SELECT MAX(effective_date) FROM myTable WHERE effective_date <= Now(); I can not figure out how to do this in Report Writer. Any suggestions? Anyone know if this is possible? "badabing" wrote: > Hi, I am new to using report writer. I am trying to write a report that will > just pull back the latest effecitve dated row by employee. > > A SQL statement that can do thi...

Paste to adjacent cells
Hi All If I have Data in cells A1 to A10 or C1 to C23 etc and I want to paste more Data along side, to B1 to B10 or D1 to D23. Even better would be if I could paste to either side of the data. Is there away to do this without having to highlight those cells, this would save me allot of time. Thanks in Advance Dave, When you paste, it pastes the amount of stuff you've copied. If you've copied 10 vertical cells, that's what it's gonna paste. You need only select the top-left-most cell before you do a paste. -- Earl Kiosterud mvpearl omitthisword at verizon period ne...

Due Date in Statement of accounts??
Hi , Can i print the sales statement of account showing the DUE DATE ? If yes, kindly please help me to create the field. Thanks in advance. ...

Invisible Formula in Cells !!!!!!!!!!!!!!!!!!
I have this strang excel file. When I enter formular into it, it don't show thae value but when I enter anything else in the cell, that is visible. The formula is in the cell, its just not visible. On Apr 21, 7:41=A0am, Ayo <A...@discussions.microsoft.com> wrote: > I have this strang excel file. When I enter formular into it, it don't sh= ow > thae value but when I enter anything else in the cell, that is visible. T= he > formula is in the cell, its just not visible. Probably the sheet is protected and Hidden check box of Protection tab of Format Cell dial...

how do I free units cells?
I try to put in alphabetical order a column but appear a messagge about cells joined togheter. How can I free the whole page ? thanks and sorry for my english Try selecting the whole sheet Ctrl A then Format Cells Alignment and make sure the merge cells box is unticked. then try your sort Reagards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27107 View this thread: http://www.excelforum.com/showthread.php?threadid=513559 ...

format numbers #2
I have a list of audio books in an excel 2003 spreadsheet. One column should show the duration of the book in this format: 8 hrs 23 mins. But what shows in the column is a 5 digit number, such as 54463 as an example. I cannot figure out how to format this column to translate this number into the hrs and mins of the actual book. Could someone please tell me how to do this? Thanks for your help Joanne Joanne Where does this number come from? It does not seem to relate to 8 hours 23 minutes at all (eg it isn't the total minutes for example or seconds). What is the source of this informa...

Outlook 2007 Edit Business Card Format
I've just started using Outlook 2007 with the Business Contact Manager. I'd like to edit the format of the Business Card in both Outlook and Business Contact Manager. I have discovered how to edit an individual card but not how to edit all the existing cards and future cards. Can this be done and if so how? Thanks, Bob It can't be done; that functionality just isn't built into Outlook. I = expect, though, that some enterprising programmer will produce an = inexpensive tool or sample application to apply a particular format to = all EBCs.=20 --=20 Sue Mosher, Outlook ...

sort dates
hI My dates use the system dd/mm/year if i press the sort button.. it will sort by day->month->year I want to sort by year->month->day how do i do this?? thank -- Message posted from http://www.ExcelForum.com The best thing to do is to store your data as real Excel dates. Then they will always sort properly, regardless of which format you display the date in. As you have numbers, rather than dates, sorting isn't so easy. The choices are: 1. Create a Date field (=Date(year,month,day)) and sort on that. 2. Split out the day, month and year, then sort on the three separa...

Custom Format text XX:XXXX:XX
I would like to have text custom formatted so that if a 8 character words is typed, it is automatically broken up like this XX:XXXX:XX. This works just fine with numbers with a ##\:####\:## custom format, but it will not work for text. Any suggestions? -- mustard ------------------------------------------------------------------------ mustard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20017 View this thread: http://www.excelforum.com/showthread.php?threadid=346023 Number formatting only works on numbers. But you could use a little macro waiting for y...

Date in the query is always short date
I want to capture the LONG date from a query, so I create a parameter for the date (mm ddd yy) . In its properties, it's format is long date, BUT it always shows up the short date. WHY? Dates are stored as decimal number counting days from 12/31/1899 midnight with time as the decimal fraction of a day. Formats are just different ways to display the information. So you got to set the format. In design view of the query click the field of the grid that has the datetime field, right click, scroll down and select Properties. Click 'Format' and then in the pull down...

format of emailed excel file
In excel 2003 when I email a a particulat worksheet in a workbook I lose all the formating when it is recieved in Outlook 2003. Is there anyway to preserve the excel "look"? Thnaks, Craig S ...

Conditional format based on data from another column
Hi Yesterday I asked about the possibility of conditional formatting a range of 20 random numbers into 2 different formats. Thank you for that, but now it seems that 3 of my 20 numbers are now requiring a different cell to be highlighted in yet another colour. Currently the situation is as follows:- Column G has a list of random numbers ranging from 1-20, with columns either side pertaining to the random number located in the relevant cell in Column G. It now appears that 3 numbers in this random list do not require highlighting in Column G and have amended the condition formattin...

cell won't wrap text
Very simple Excel file. 3rd column over is is formatted as Text | Wrap Text. All other cells seem to wrap text fine. One cell in particular won't wrap text. It displays all XXXX instead. I did an Edit|Clear and re-entered the text and it did it again. I adjust the height of the cell to no avail. 70+ other cells in this SS work just fine with the same amount of text. Any thoughts? Try changing the format from Text to General -- Gary''s Student "GrHopp" wrote: > Very simple Excel file. 3rd column over is is formatted as Text | Wrap Text. > All othe...

Format a cell to display as all caps
Is there any way to format a cell so that text entered will always display in caps? Hi Not Directly !......but you can use the UPPER function. If your data is in A1, put =UPPER(A1) in A2. This will convet whatever you type in A1 to Uppercase in A2 HTH Michael "WAF" wrote: > Is there any way to format a cell so that text entered will always display in > caps? No. You cannot format a cell to display caps. You can use event code in the worksheet to change the text to caps when you enter it. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column &g...

Extract data in the right part of a cell
I have some cells contains "*" , I want to extrat the text to the right of the "*". For example, if A2 contains "txt*123", I want it turn out to be "123". Thanks. Try the following formula, =MID(A1,FIND("*",A1)+1,LEN(A1)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "plumstone" <plumstone@discussions.microsoft.com> wrote in message news:731CB03C-AD8F-43D8-BFAB-6AD50DEBE799@microsoft.com... > I have some cells contains "*" , I want to extrat the text to th...

Excel should have the option to merge contents when merging cells.
When Excel merges cells, it keeps only the upper-left-most contents and deletes the others. It would be nice to have the option (maybe a toggle button in the options menu) to concatenate the contents of all the merged cells, so that the information in the lower-right cells is tacked onto the end of the information inthe upper-left-most cell. This is not a big deal, but it would be a nice bell/whistle to save some steps. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I...

Compare cells for identical values
Hi I need to be able to compare a range of cells and highlight if the same value appears to 2 cells in the same row. Does anyone know the VB code that will help me do this? Thanks -- red_debs ------------------------------------------------------------------------ red_debs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14753 View this thread: http://www.excelforum.com/showthread.php?threadid=263746 Hi check out Chip Pearson's website he has lots of stuff on dealing with duplicates http://www.cpearson.com/excel/duplicat.htm Cheers JulieD "re...

recurring dates
I need to be able to set set a deadline based on prior dates. These can go back years. I would like to set up a current 'next appointment, for instance ever 90 days, based on a date of arrival. For example, if date of arrival is 2/13/03, how do I make a formula that is effective now. In other words, the cell updates to a new deadline every ninety days, and stays current. Thanks a bunch. Maybe this which calculates the number of 90 day periods that have elapsed since the start date. =(FLOOR((A1-TODAY())/90,1)+1)*90+A1 Understand though that working with dates is tricky. There i...

Modify general format to date format.
When importing from payroll, it gives me all dates formatted as general numbers (i.e. 07/17/2009 is presented as 7172009. Is there any way to convert these numbers into dates? If your Windows Regional Settings recognise a format of mddyyyy, you can apply the below formula and format the formula cell to date.. =--TEXT(A1,"00\-00\-0000") -- Jacob (MVP - Excel) "SherryS" wrote: > When importing from payroll, it gives me all dates formatted as general > numbers (i.e. 07/17/2009 is presented as 7172009. Is there any way to > convert the...

Conditional Formatting OR Expression??
I am using all 3 conditions so is there a way to combine these 2 lines of code into 1 condition? They both result in turning the cell red: '(this line looks to see if there is a percent other than 0 or 100 and calculates the percent where we should be versus the actual, cell H33) =IF(AND(H33>0%,H33<100%),(H33<=((H32-$C$2)/$H$8))) OR =IF(H33=0%,(H32-$C$2)<$H$8) '(this line looks to see if there are still enough days to complete) Cell H33 is a % Comp, H32 is End Date, $C$2 is Today's Date and $H$8 is # days to complete, just FYI. THANKS!! Stacey Do an OR =OR(cond1...

How do you change the data type of a cell using VBA?
For instance, with VBA I am trying to place "February 2007" in a cell but it keeps showing up as Feb-07. I want it to show up as literal text. -- justme0010 Sub text_it() Range("A1").Value = Chr(39) & "February 2007" End Sub -- Gary''s Student - gsnu2007c You could keep the value a date and just format the cell the way you like: with activecell .numberformat = "mmmm yyyy" .value = dateserial(2007,2,1) end with You could also format the cell as text first: with activecell .numberformat = "@" 'text .value...