SOLVED

convert to another date-format

%3CLINGO-SUB%20id%3D%22lingo-sub-2648431%22%20slang%3D%22en-US%22%3Econvert%20to%20another%20date-format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648431%22%20slang%3D%22en-US%22%3E%3CP%3EI%20often%20select%20data%20from%20my%20bank-account%20and%20they%20use%20the%20date-format%20dd.mm.yyyy%20but%20to%20use%20dates%20to%20manage%20my%20data%2C%20I%20use%20the%20format%20dd-mm-yyyy.%20How%20to%20convert%20to%20this%20format%20in%20an%20easy%20way%20%3F%3C%2FP%3E%3CP%3EI%20have%20used%20the%20search%2Fchange%20facility%20to%20convert%20all%20.%20to%20-%2C%20but%20it%20isn't%20a%20nice%20way.%20Can%20you%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2648431%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2648465%22%20slang%3D%22en-US%22%3ERe%3A%20convert%20to%20another%20date-format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648465%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1128499%22%20target%3D%22_blank%22%3E%40ean-1501%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20formula%20that%20will%20convert%20your%20bank's%20dates%20(assuming%20they're%20in%20there%20as%20text)%20to%20a%20standard%20Excel%20formatted%20datevalue%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DDATE(VALUE(RIGHT(A1%2C4))%2CVALUE(LEFT(A1%2C2))%2C(VALUE(MID(A1%2C4%2C2))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2648664%22%20slang%3D%22en-US%22%3ERe%3A%20convert%20to%20another%20date-format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2648664%22%20slang%3D%22en-US%22%3EIt%20works%20-%20super%3C%2FLINGO-BODY%3E
New Contributor

I often select data from my bank-account and they use the date-format dd.mm.yyyy but to use dates to manage my data, I use the format dd-mm-yyyy. How to convert to this format in an easy way ?

I have used the search/change facility to convert all . to -, but it isn't a nice way. Can you help.

 

3 Replies
best response confirmed by ean-1501 (New Contributor)
Solution

@ean-1501 

 

Here's a formula that will convert your bank's dates (assuming they're in there as text) to a standard Excel formatted datevalue

 

=DATE(VALUE(RIGHT(A1,4)),VALUE(LEFT(A1,2)),(VALUE(MID(A1,4,2))))

 

 

It works - super

The Danish formulas have another structur with ; instead of , and the form had to be with rigth, mid and lest in the end before it worked. @ean-1501