Excel date format from Denmark to USA

cabjerk

Starting to get Involved
#1
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?
 
Elsmar Forum Sponsor

outdoorsNW

Quite Involved in Discussions
#2
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.
 

cabjerk

Starting to get Involved
#3
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

cabjerk

Starting to get Involved
#5
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

cabjerk

Starting to get Involved
#7
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:
 
Thread starter Similar threads Forum Replies Date
Colin Changing the Colour of a Date in Microsoft Excel Excel .xls Spreadsheet Templates and Tools 4
F Save Excel file with Date for Recording Weekly Test Samples Records and Data - Quality, Legal and Other Evidence 11
C Template for Excel Validation Reliability Analysis - Predictions, Testing and Standards 6
R Gage R&R Excel templates Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
S Issuing of CoC to Customer in a Word or Excel format ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
Ajit Basrur Track audit findings on Excel tracker Excel .xls Spreadsheet Templates and Tools 9
J Does anyone have an excel IATF 16949 Internal Audit checklist I could use? IATF 16949 - Automotive Quality Systems Standard 7
S Anyone Familiar with "SPC for Excel" add-in? Lean in Manufacturing and Service Industries 4
P Commercial excel templates that calculate overhead, hourly rates, fee proposal Excel .xls Spreadsheet Templates and Tools 0
I Cannot Export multiple sections from Excel to PDF without losing pagination Excel .xls Spreadsheet Templates and Tools 23
M Matching two matrices in Excel Excel .xls Spreadsheet Templates and Tools 5
J Level 3 KPI Excel Template Manufacturing and Related Processes 1
bobdoering Informational New AIAG PFMEA Process - Excel Template Attached FMEA and Control Plans 23
GreatNate Excel PPAP Tracking templates or tool wanted APQP and PPAP 1
E Part 11 Compliance, Excel living documents (i.e. document master list, equipment list, approved supplier list) Pharmaceuticals (21 CFR Part 210, 21 CFR Part 211 and related Regulations) 3
J ISO 9001:2015 Excel Gap Analysis wanted ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
B AS9110 Audit Check Sheet wanted Excel or Word Document AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 2
G Excel report with pictures Excel .xls Spreadsheet Templates and Tools 5
A Query regarding MS Excel usage Excel .xls Spreadsheet Templates and Tools 2
A Excel OOT (Out of Tolerance) Formula Excel .xls Spreadsheet Templates and Tools 3
D Asterix when pasting a column of values from Excel into Minitab (V17) Using Minitab Software 5
Y Excel Automation - Printing Word Documents from within Excel Excel .xls Spreadsheet Templates and Tools 0
S Help me build an Excel Spreadsheet for tracking training Training - Internal, External, Online and Distance Learning 14
P Template or example Excel list of ISO certificates issued to clients ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 5
M Medical Device Process validation, Validation of excel spreadsheets used for process Other Medical Device and Orthopedic Related Topics 4
G ISO 9001:2015 - Is control possible with Excel adaptable measurement sheets? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 24
N Where I can find an Excel sample/template for recording Delivery Time and Cost Excel .xls Spreadsheet Templates and Tools 2
D How do I go about creating document logs and registers with the MS Excel Excel .xls Spreadsheet Templates and Tools 2
D Microsoft Excel database to Stand-alone software Calibration and Metrology Software and Hardware 3
QSM_Rick Controlling Multi-Tab Microsoft Excel Forms Excel .xls Spreadsheet Templates and Tools 5
B Zeiss CMM Calypso output to Excel or CSV file Help Inspection, Prints (Drawings), Testing, Sampling and Related Topics 17
S used excel formula calculated the ARL's with Rule1&Rule2 for Shewhart control chart Statistical Analysis Tools, Techniques and SPC 0
N Gantt Chart / Time Tracker in Excel - Combining Tasks on the Same Line Excel .xls Spreadsheet Templates and Tools 2
M Preventive Maintenance Spreadsheet in Excel Quality Assurance and Compliance Software Tools and Solutions 2
1 KPI Dashboard Template in Excel Format wanted Excel .xls Spreadsheet Templates and Tools 9
Ajit Basrur MS Excel to study Correlation between Humidity & Differential Pressure Excel .xls Spreadsheet Templates and Tools 7
D Gage Calibration Tracking in MS Excel Calibration and Metrology Software and Hardware 9
T Where can I get a PPAP workbook in Excel .xls with links? APQP and PPAP 1
L Excel Template for Cgk Study wanted (Capability Gauges Study) Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 12
V Looking for advice on Pooled Standard Deviation between Minitab and MS Excel Statistical Analysis Tools, Techniques and SPC 1
P Recommendations on SPC Software or Excel SPC add-in(s) Quality Assurance and Compliance Software Tools and Solutions 6
T Calculating LCL, UCL, Cp, and Cpk in an Excel Spreadsheet Statistical Analysis Tools, Techniques and SPC 3
R Is there any Excel .xls Spreadsheet available for DOE with 3 factors? Six Sigma 1
kedarg6500 Excel spreadsheet for Unilateral Tolerance Study wanted Excel .xls Spreadsheet Templates and Tools 2
kedarg6500 Excel Spreadsheet for AIAG Example wanted Excel .xls Spreadsheet Templates and Tools 3
M Excel Leadtime Analysis Graphic Tool with Macros Enabled Lean in Manufacturing and Service Industries 0
A Tracking Calibration and Qualification in an MS Excel Spreadsheet Calibration and Metrology Software and Hardware 6
S Document Control Guidelines in Engineering Projects - Excel sheets with VBA coding Document Control Systems, Procedures, Forms and Templates 3
R Excel Dashboard for Health Safety and Environmental KPI's for Multiple Sites Occupational Health & Safety Management Standards 4
B Looking for a Y-Shaped / 3-way Matrix Template (Excel or Visio) Quality Assurance and Compliance Software Tools and Solutions 7

Similar threads

Top Bottom