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.

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

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

J.

Andrew wrote:

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.

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560142

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

" wrote:

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

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

J.

Andrew wrote:

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.

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




"Andrew" wrote:

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

" wrote:

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

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

J.

Andrew wrote:

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.

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:

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




"Andrew" wrote:

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

" wrote:

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

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

J.

Andrew wrote:

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.

=DATE(1979,8,31)

HTH
--
AP

"Marcelo" a écrit dans le message de
news: ...
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:

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




"Andrew" wrote:

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

" wrote:

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

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

J.

Andrew wrote:

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.

Thank!

How do I change the entire column of dates?

"Marcelo" wrote:

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:

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




"Andrew" wrote:

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

" wrote:

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

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

J.

Andrew wrote:

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.

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

"Ardus Petus" wrote:

=DATE(1979,8,31)

HTH
--
AP

"Marcelo" a écrit dans le message de
news: ...
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:

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




"Andrew" wrote:

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

" wrote:

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

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

J.

Andrew wrote:

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.

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?

"Dav" wrote:


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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560142

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).

"Andrew" wrote:

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?

"Dav" wrote:


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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560142

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

"Andrew" wrote:

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).

"Andrew" wrote:

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?

"Dav" wrote:


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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560142

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

If the value is in the cell A1 there are 2 things you need to be able
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 to
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 to
loose the formulas

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

If this does not work please get back to me

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560142

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" wrote:


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

If the value is in the cell A1 there are 2 things you need to be able
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 to
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 to
loose the formulas

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

If this does not work please get back to me

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560142

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

regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560142

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

"Dav" wrote:


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

regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560142

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560142

Dav,

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

Im 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 dont sort well. I think they may be
another permutation. Ive included the dates under the others.



"Dav" wrote:


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

regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560142