Excel 2003: date display issue

[Guest]

I saved data from a CVS file to Excel. I have a list of dates which display
as mm/dd/yyyy. However, when I try to sort the dates, they are organized by
month first, then day, then year as opposed to year, month, day, which is how
I would like it to sort the info.

I have another list of dates which is being read by Excel as dd/mm/yyyy but
in fact, the way it is organized, should be read as mm/dd/yyyy. So the
digits of 10/03/1962 should stay in the same order and be read as October 03,
1962. But Excel is reading it as March 10, 1962 because when I format the
date to m/dd/yy it reverses the 3 and the 10.

 

[jalcock]

This formula should fix your problem (where your back-to-front date is
in cell A1):

=DATE(YEAR(A1),DAY(A1),MONTH(A1))

J.

 

[Dav]

In the first instance it seems they are being sorted alphabetically, so
are treated as text rather than dates. Does the cell display change if
you format the cell with another date format or change it to a number
format?

what is the default date format on the computer you are using within
your operating system? Is this effecting you.

You probably need to look at the datevalue or date functions to convert
your dates to the required format

Regards

Dav

 

[Guest]

Thanks for your reply. However, I'm not sure what you are suggesting I do.

 

[Guest]

In the cell which contained the date, 08/31/1979, I inserted the following
formula:

=DATE(YEAR(79),DAY(31),MONTH(8))

Here is what I got: 01/07/1902

 

[Guest]

Hi Andrew,

is not necessary to use Year, Day or month function on this formula:
use date=(79,8,31)

hth
regards from Brazil
Marcelo

"Andrew" escreveu:

 

[Ardus Petus]

=DATE(1979,8,31)

HTH
--
AP

Hi Andrew,

is not necessary to use Year, Day or month function on this formula:
use date=(79,8,31)

hth
regards from Brazil
Marcelo

"Andrew" escreveu:

 

[Guest]

Thank!

How do I change the entire column of dates?

 

[Guest]

Yup, that's what I figured. But how do I set the entire column that way?

 

[Guest]

For he second list of dates the cell display does not change if I format the
cell with another date format or change it to a number.

How do I determine the default date format used by my computer?

Is there anyway I can send the data to someone or post it somewhere?

 

[Guest]

To clarify I have two sets of dates. Both are written as mm/dd/yyyy.

For the first list, Excel reads the data as a date in the layout of
dd/mm/year.

For the second list, Excel reads the data as 'general' data. The contents
of the cel are not altered when a differnent data type is chosen.

I want to be able to sort the data by year, month, day and have it displayed
as yyyy-mmm-dd (1972-Jul-12).

 

[Guest]

Does this have someting to do with a macro in excel using the "datepart"
function?

 

[Dav]

Excel is expecting the data in the dd/mm/yyyy format and some of th
dates that are mm/dd/yyyy would appear to meet this format so they hav
become a date (all be it incorrect), the others it has just recognise
as a text string

If the value is in the cell A1 there are 2 things you need to be abl
to do
1) if the cell is a date it needs to have the month and date switched
2) if the date is text then it needs to be converted into a date

As dates are number the following should work although you will need t
format the cell yyyy-mm-dd

in another cell say b1 put
=IF(ISNUMBER(A1),DATE(YEAR(A1),DAY(A1),MONTH(A1)),DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2)))

copy this down by the side of all your dates

then copy all of it and paste special on top of itsself as values t
loose the formulas

copy again and paste on top of the original data, you can now delet
the column you added

If this does not work please get back to me

Regards

Da

 

[Guest]

1) if the cell is a date it needs to have the month and date switched

Tried this and it didn't work

2) if the date is text then it needs to be converted into a date

The date is being read by Excel as 'general' data. The contents
of the cel are not altered when a differnent data type is chosen.

Dav, can I send you the document and maybe you will at least have a better
idea of the problem and we save us both time. If you can't solve it at least
you'll be able to better articulate the problem on this board.

Thanks
Andrew

 

[Dav]

just attach it to this post, if you zip it first you can have it as an
attachment

regards

Dav

 

[Guest]

I don't see an option to attach a zip file.

 

[Dav]

if you are at www.excelforum.com and replying to the thread you click on
the paperclip in the top toolbar to the right of the smilie face
otherwise mail me at davunderscorewilsonathotmaildotcom. I am sure you
can work it out

Regards

Dav

 

[Guest]

Dav,

Thanks for all your help. I appreciate it very much.

I’m having difficulty making the changes permanent. Also, I sent you an
email with one more date type that I missed.

Thanks
Andrew



Also, there are a few dates that don’t sort well. I think they may be
another permutation. I’ve included the dates under the others.