Excel date format from Denmark to USA

C

cabjerk

I hope someone can help. We use Q-Pulse and it resides on a server in Denmark. When we pull data from it the date format is in Danish D/M/Y. When bring into excel in USA this column changes into 2 layout. One is M/D/Y and the other is D-M-Y. Is there a simple way to get all this into the same format. I have tried to search and replace and text to columns. but both are very time consuming. Anyone have a suggestion?
 

outdoorsNW

Quite Involved in Discussions
In Excel 2013, there are a couple ways you can do this.
The easiest way:Select the cells or column you wish to change.On the “Home” tab, find the “Number” section and click on the small box with an arrow in the lower right.This brings up the “Format Cells” window.Normally the window defaults to the “Number” tab.On the number tab, select “Date” and then scroll down until you find the desired date format in the “type” window.Other possible methods include using the “locale” pull down.I have never used the function.I think it is new to Excel.I am unsure if part of a spreadsheet can be set to a different locale or if the entire worksheet is affected.If all else fails, in the “category” box select “custom” and create a custom date format.

If you are pasting data into a template and the cells are already set to the desired date format, you need to use “paste values” or a similar function to ensure that the data keeps the template format.

Another possible option, if the template does not have the correct formatting, is to use “Paste Special” and then select “Keep source formatting,” although this may bring in undesirable source formatting in other ways.
 
C

cabjerk

Thank you for this.
My data is cut and paste but still is a mix in the column for date format. The attached files shows what is pasted in column "A" and what the date should be in USA format in Column "C".
 

Attachments

  • Date format DK to US.xlsx
    8.7 KB · Views: 186

Proud Liberal

Quite Involved in Discussions
Not sure what approach you'd like but this uses a formula. The formula in column E will convert your input values in column A to the desired date.
 

Attachments

  • Date format DK to US (by formula).xlsx
    11.9 KB · Views: 166
C

cabjerk

Thank you.
Looks like it's getting closer.
But E2-E6 should be what D2-D6 is. The column with the formula has Mar 02 2018. This should be Mar 29 2018. Can you try edit the formula again for this? E7-E16 is correct.
I hope you see what a pain this has been for me.
The software pgm does not have a export option and all I can do is cut and paste.
The attached word file shows what the data looks like in the pgm DD-MM-YYYY. But when pasted into US excel the firsts lines stay the same but the bottom one change to "/" instead of "-"

Again thank you
 

Attachments

  • DK to US format.docx
    16.8 KB · Views: 169

Le Chiffre

Quite Involved in Discussions
Here's a version of Carl's file with a correction to the formula.
 

Attachments

  • Date format DK to US (by formula) v2.xlsx
    11.7 KB · Views: 195
C

cabjerk

Thank you for this update.
Looks great. I just copied over 4,000 lines of date covering all types of dates through out the past few years and everything looks great....

Thank you :applause:
 
Top Bottom