I have a column of dates that I'd like to use in a formula. Problem is Excel
sees the dates as dd/mm/yyyy when they are actually entered as mm/dd/yyyy.
For example the date entered in the cell is 10/17/1980 and Excel see the 17
as the month.

A second question although not as important as the first is, and I have seen
this before in other spreadsheets, why is it in that column of dates some of
the cells are actually seen as mm/dd/yyyy and some seen as dd/mm/yyyy.

Manual formatting does not work at all. I am thinking I need a formula to
switch the month and date around.

Thanks so much.

Dear Lisa

I think the basic issue you have is the format in which you have your data
in the sheet. Is that imported from somewhere or a csv file which you have
opened..

Try this....
Select the column--From menu..Data--Text to Columns..Next..Next will bring
you to the Wizard Step 3 of 3

Then select Date option button and select the format you require. MDY or DMY
as required . Hit Finish..


If this post helps click Yes
---------------
Jacob Skaria


"Lisa" wrote:

I have a column of dates that I'd like to use in a formula. Problem is Excel
sees the dates as dd/mm/yyyy when they are actually entered as mm/dd/yyyy.
For example the date entered in the cell is 10/17/1980 and Excel see the 17
as the month.

A second question although not as important as the first is, and I have seen
this before in other spreadsheets, why is it in that column of dates some of
the cells are actually seen as mm/dd/yyyy and some seen as dd/mm/yyyy.

Manual formatting does not work at all. I am thinking I need a formula to
switch the month and date around.

Thanks so much.

Thanks so much Jacob,

Not sure where the data came from but it certainly would make sense.

Thanks again



"Jacob Skaria" wrote:

Dear Lisa

I think the basic issue you have is the format in which you have your data
in the sheet. Is that imported from somewhere or a csv file which you have
opened..

Try this....
Select the column--From menu..Data--Text to Columns..Next..Next will bring
you to the Wizard Step 3 of 3

Then select Date option button and select the format you require. MDY or DMY
as required . Hit Finish..


If this post helps click Yes
---------------
Jacob Skaria


"Lisa" wrote:

I have a column of dates that I'd like to use in a formula. Problem is Excel
sees the dates as dd/mm/yyyy when they are actually entered as mm/dd/yyyy.
For example the date entered in the cell is 10/17/1980 and Excel see the 17
as the month.

A second question although not as important as the first is, and I have seen
this before in other spreadsheets, why is it in that column of dates some of
the cells are actually seen as mm/dd/yyyy and some seen as dd/mm/yyyy.

Manual formatting does not work at all. I am thinking I need a formula to
switch the month and date around.

Thanks so much.

You are welcome..

If this post helps click Yes
---------------
Jacob Skaria


"Lisa" wrote:

Thanks so much Jacob,

Not sure where the data came from but it certainly would make sense.

Thanks again



"Jacob Skaria" wrote:

Dear Lisa

I think the basic issue you have is the format in which you have your data
in the sheet. Is that imported from somewhere or a csv file which you have
opened..

Try this....
Select the column--From menu..Data--Text to Columns..Next..Next will bring
you to the Wizard Step 3 of 3

Then select Date option button and select the format you require. MDY or DMY
as required . Hit Finish..


If this post helps click Yes
---------------
Jacob Skaria


"Lisa" wrote:

I have a column of dates that I'd like to use in a formula. Problem is Excel
sees the dates as dd/mm/yyyy when they are actually entered as mm/dd/yyyy.
For example the date entered in the cell is 10/17/1980 and Excel see the 17
as the month.

A second question although not as important as the first is, and I have seen
this before in other spreadsheets, why is it in that column of dates some of
the cells are actually seen as mm/dd/yyyy and some seen as dd/mm/yyyy.

Manual formatting does not work at all. I am thinking I need a formula to
switch the month and date around.

Thanks so much.

Thax Jacob
your solution is very very helpful

"Jacob Skaria" wrote:

You are welcome..

If this post helps click Yes
---------------
Jacob Skaria


"Lisa" wrote:

Thanks so much Jacob,

Not sure where the data came from but it certainly would make sense.

Thanks again



"Jacob Skaria" wrote:

Dear Lisa

I think the basic issue you have is the format in which you have your data
in the sheet. Is that imported from somewhere or a csv file which you have
opened..

Try this....
Select the column--From menu..Data--Text to Columns..Next..Next will bring
you to the Wizard Step 3 of 3

Then select Date option button and select the format you require. MDY or DMY
as required . Hit Finish..


If this post helps click Yes
---------------
Jacob Skaria


"Lisa" wrote:

I have a column of dates that I'd like to use in a formula. Problem is Excel
sees the dates as dd/mm/yyyy when they are actually entered as mm/dd/yyyy.
For example the date entered in the cell is 10/17/1980 and Excel see the 17
as the month.

A second question although not as important as the first is, and I have seen
this before in other spreadsheets, why is it in that column of dates some of
the cells are actually seen as mm/dd/yyyy and some seen as dd/mm/yyyy.

Manual formatting does not work at all. I am thinking I need a formula to
switch the month and date around.

Thanks so much.