How to correct date format

I want to insert dates to my table as followed:
mm/dd/yyyy.

For now, I costumed the column to be set as such. However, while inserting new value - order of input is different - dd/mm/yyyy.
Example:
input: 29/09/2014
output: 09/29/2014

1. I want input to match the output, and not write one way and watch other (want to insert mm/dd/yyyy and watch the same).

2. Also, I want to change format existing "opposite" values (dd/mm/yyyy) to mm/dd/yyyy. I manged to do so with left-mid-right functions, but later when pasting them as values - excel wouldn't recognize them as dates.

See attached table. A more problematic dates are in the year of 2015 (line 1209 and down). Thorough explanation of solution will be much appreciated.

Droriley

Originally Posted by Droriley

I want to insert dates to my table as followed:
mm/dd/yyyy.

For now, I costumed the column to be set as such. However, while inserting new value - order of input is different - dd/mm/yyyy.
Example:
input: 29/09/2014
output: 09/29/2014

1. I want input to match the output, and not write one way and watch other (want to insert mm/dd/yyyy and watch the same).

2. Also, I want to change format existing "opposite" values (dd/mm/yyyy) to mm/dd/yyyy. I manged to do so with left-mid-right functions, but later when pasting them as values - excel wouldn't recognize them as dates.

See attached table. A more problematic dates are in the year of 2015 (line 1209 and down). Thorough explanation of solution will be much appreciated.

Droriley

Do you set up the date in your computer, after that you format your data. I Think it is ok
1. Control panel -> clock, langue, Region-> Change date, time, or number format -> in short date: MM/DD/YYYY -> aply-> OK
2. format your data: Ctrl+1: choose: type: MM/DD/YYYY

Let me simplify my problem:
I want all the values to be in the same format: mm/dd/yyyy
Best if I can insert them the same way, but no must (PC set to dd/mm/yyyy - cannot be changed since it's company's server etc.)
attention: not all values are now formatted the same way (example: line 1 is acting differently than line 1222 - check with "DATE" functions).
Thanks

Not perfect, but try this.........



regards

Originally Posted by azumi

Not perfect, but try this.........



regards

Well Done! great solution. I only formatted the C column to mm/dd/yyyy for finish.
Do you know any way of inserting date as mm/dd/yyyy, and let it appear the same, without adjust time setting in Win?