Home

Excel Datum umwandeln von MM/TT/JJJJ bzw. MM.TT.JJJJ in TT.MM.JJJJ

%3CLINGO-SUB%20id%3D%22lingo-sub-572314%22%20slang%3D%22de-DE%22%3EConvert%20Excel%20Date%20by%20MM%2FTT%2FJJJJ%20or%20MMTT%2FJJJ%20MM.TT.%20JJJJJ%20in%20TT.%20MM.JJJJ%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-572314%22%20slang%3D%22de-DE%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI%20would%20have%20the%20following%20question%20please%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20Excel%20spreadsheet%20with%20a%20date%20column.%20Here%20are%202%20different%20formats%20MM%2FTT%2FJJJJ%20and%20MM.TT.%20Yyyy.%3C%2FP%3E%3CP%3Eeg%200%C2%AE%2021%2F2019%2C%2002%2C000%2022%2F2019%2C%2001.23.2019%2C%2001.24.2019%20etc.%3C%2FP%3E%3CP%3EI%20would%20love%20to%20convert%20these%20into%20TT.%20MM.JJJJ.%20(E.g%2021.01.2019%2C%2022.01.2019%2C%2023.01.2019%2C%2024.01.2019%2C%20etc.).%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20convert%20this%20column%20with%20a%20formula%3F%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20in%20advance!%3C%2FP%3E%3CP%3EDear%20greetings%3C%2FP%3E%3CP%3ELisa%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-572314%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-573035%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Datum%20umwandeln%20von%20MM%2FTT%2FJJJJ%20bzw.%20MM.TT.JJJJ%20in%20TT.MM.JJJJ%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-573035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342876%22%20target%3D%22_blank%22%3E%40Lisa128%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Lisa%2C%3C%2FP%3E%0A%3CP%3ESelect%20your%20column%2C%20on%20ribbon%20Data-%26gt%3BText%20to%20Columns%2C%20on%20thirds%20step%20of%20the%20wizard%20select%20Data%20and%20MDY%20format%20(it%20doesn't%20matter%20what%20to%20select%20on%20first%20two%20steps)%20and%20Finish%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20605px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113702i0C32739F9783BFD6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EResult%20will%20be%20the%20dates%20in%20your%20locale%20format%20(in%20my%20case%20ISO%20format)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20115px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113703i5970385CAB9C3191%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ebut%20you%20may%20apply%20any%20date%20format%20you%20wish.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-583026%22%20slang%3D%22de-DE%22%3ERe%3A%20Excel%20Date%20Convert%20by%20MM%2FTT%2FJJJJ%20respectively%20MM.TT.%20JJJJJ%20in%20TT.%20MM.JJJJ%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-583026%22%20slang%3D%22de-DE%22%3EMany%20thanks%20for%20your%20fast%20answer%3A)%20Thank%3CBR%20%2F%3Eyou%20for%20the%20quick%20response.%20%3CBR%20%2F%3E%20Everything%20worked%20great%3A-)%3CBR%20%2F%3EDear%20greetings%2C%20Lisa%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-583049%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Datum%20umwandeln%20von%20MM%2FTT%2FJJJJ%20bzw.%20MM.TT.JJJJ%20in%20TT.MM.JJJJ%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-583049%22%20slang%3D%22en-US%22%3E%3CP%3ELisa%2C%20you%20are%20welcome%2C%20have%20a%20nice%20weekend.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-741778%22%20slang%3D%22de-DE%22%3ECopying%20formulas%20unrelated%20to%20the%20old%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-741778%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%3C%2FP%3E%3CP%3ECan%20I%20ask%20something%20else%20please%3F%20Unfortunately%2C%20I%20didn't%20find%20the%20feature%20to%20start%20a%20new%20community.%20So%20I'm%20writing%20here%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20in%20Excel%20(Excel%20Makro.xlsx)%20in%20which%20I%20refer%20to%20another%20tab%20sheet%3A%3C%2FP%3E%3CP%3E%3DExcel!%20A2%26amp%3B%22%20%22%26amp%3BExcel!%20B2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20when%20I%20open%20a%20new%20Excel%20and%20copy%20and%20paste%20this%20formula%2C%20it%20always%20refers%20to%20the%20old%20Excel%3A%3C%2FP%3E%3CP%3E%3D'%5BExcel%20Macro.xlsx%5DExcel'!%20A2%26amp%3B%22%20%22%26amp%3B'%5BExcel%20Macro.xlsx%5DExcel'!%20B2%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20insert%20that%20that%20the%20formula%20remains%20the%20same%20as%20before%20and%20thus%20refers%20to%20the%20tab%20sheet%20in%20the%20current%20Excel%20folder%20and%20not%20to%20the%20original%20one%3F%3C%2FP%3E%3CP%3E%3DExcel!%20A2%26amp%3B%22%20%22%26amp%3BExcel!%20B2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much!%3C%2FP%3E%3CP%3EDear%20greetings%3C%2FP%3E%3CP%3ELisa%20Leiner%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-744520%22%20slang%3D%22en-US%22%3ERe%3A%20Kopieren%20von%20Formeln%20ohne%20Bezug%20zur%20alten%20Datei%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-744520%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342876%22%20target%3D%22_blank%22%3E%40Lisa128%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELisa%2C%20you%20may%20start%20new%20conversation%20from%20this%20page%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fbd-p%2FExcelGeneral%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20copy%20formulas%20from%20one%20workbook%20to%20another%20without%20keeping%20the%20link%20on%20the%20file%20-%20with%20bulk%20copy%2Fpasting%20the%20workaround%20is%20to%20convert%20formulas%20to%20text%20in%20source%20workbook%2C%20copy%20and%20paste%20them%2C%20after%20that%20convert%20back%20to%20formulas%20in%20both%20workbooks.%20See%2C%20for%20example%2C%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F2882-excel-copy-formula-from-one-workbook-to-another-without-link.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F2882-excel-copy-formula-from-one-workbook-to-another-without-link.html%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20copy%2Fpaste%20one%20or%20very%20few%20formulas%20it's%20easier%20to%20copy%20formula%20in%20formula%20bar%20for%20source%20cell%2C%20and%20paste%20it%20in%20formula%20bar%20on%20in%20cell%20edit%20mode%20for%20the%20destination%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Lisa128
New Contributor

Hallo,

Ich hätte bitte folgende Frage:

 

Ich habe eine Excel-Tabelle mit einer Datum-Spalte. Hier sind 2 unterschiedliche Formate MM/TT/JJJJ und MM.TT.JJJJ.

zB 01/21/2019, 01/22/2019, 01.23.2019, 01.24.2019 usw.

Ich würde diese gerne umwandeln in TT.MM.JJJJ. (zB 21.01.2019, 22.01.2019, 23.01.2019, 24.01.2019 usw.).

Gibt es hier eine Möglichkeit diese Spalte mit einer Formel umzuwandeln?

Danke vielmals im Voraus!

Liebe Grüße

Lisa

 

5 Replies

@Lisa128 ,

 

Hi Lisa,

Select your column, on ribbon Data->Text to Columns, on thirds step of the wizard select Data and MDY format (it doesn't matter what to select on first two steps) and Finish

image.png

Result will be the dates in your locale format (in my case ISO format)

image.png

but you may apply any date format you wish.

Many thanks for your fast answer :-)
Vielen Danke für die rasche Antwort.
Alles hat super funktioniert :-)
Liebe Grüße, Lisa

Lisa, you are welcome, have a nice weekend.

@Sergei Baklan

 

Hallo,

Kann ich bitte noch etwas fragen? Leider habe ich die Funktion nicht angefunden, wie ich eine neue Community starten kann. Also schreibe ich hier noch einmal.

 

Ich habe im Excel (Excel Makro.xlsx) eine Formel, in der ich mich auf ein anderes Registerblatt beziehe:

=Excel!A2&" "&Excel!B2

 

Und wenn ich nun ein neues Excel aufmache und diese Formel kopiere und einfüge, bezieht sich das immer auf das alte Excel:

='[Excel Makro.xlsx]Excel'!A2&" "&'[Excel Makro.xlsx]Excel'!B2#

 

Wie kann ich das einfügen, dass das die Formel gleich bleibt wie vorher und sich somit auf das Registerblatt in der aktuellen Excel-Mappe bezieht und nicht auf das ursprüngliche?

=Excel!A2&" "&Excel!B2

 

Vielen Dank!

Liebe Grüße

Lisa Leiner

@Lisa128 

Lisa, you may start new conversation from this page https://techcommunity.microsoft.com/t5/Excel/bd-p/ExcelGeneral

 

To copy formulas from one workbook to another without keeping the link on the file - with bulk copy/pasting the workaround is to convert formulas to text in source workbook, copy and paste them, after that convert back to formulas in both workbooks. See, for example, here https://www.extendoffice.com/documents/excel/2882-excel-copy-formula-from-one-workbook-to-another-wi...

 

To copy/paste one or very few formulas it's easier to copy formula in formula bar for source cell, and paste it in formula bar on in cell edit mode for the destination cell.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies