Automatically change a date format from American date (mm/dd/yyyy) to English (dd.mm.yyyy) with '.' instead of '/'

[Paul365]

Hello All,

I was wondering if any one could help, this has been driving me mad.

I am trying to copy cells with American dates and have them reformatted to English date system. I know to to custom the format, which works if I manually write the date in, however I want it to copy the date from a different cell.

Example: Cells A1 - A365 have the date in for the year. A1 would be 1/1/2024 and A365 would be 12/1/2024 (All American dates with '/' in between numbers. I would like to have Cells C1-C365 copy the date from A1-365 (Formula =A1 in C1 and =A2 in C2 etc.) However it automatically changes to English date system with the '.' in between instead of '/' (01.01.2024, 02.01.2024 etc).

Is there a way this can happen?

 

[awoohaw]

Use paste special values when you paste the values into Column C. Column C will keep what ever format you have.
Or if you are using a formula, use

=Value(A2:A365)

 

[Paul365]

Use paste special values when you paste the values into Column C. Column C will keep what ever format you have.
Or if you are using a formula, use

=Value(A2:A365)

Amazing Thankyou

 

[awoohaw]

my pleasure. I'm happy you found a solution at the Mr. Excel forum.
Best Wishes!