Home

dates in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-307843%22%20slang%3D%22en-US%22%3Edates%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307843%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20someone%20help%20with%20converting%20these%20dates%20into%20a%20format%20that%20I%20can%20sort%20from%20most%20recent.%26nbsp%3B%20Although%20I%20have%20tried%20some%20functions%20but%20none%20work.%26nbsp%3B%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-307843%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307923%22%20slang%3D%22en-US%22%3ERe%3A%20dates%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307923%22%20slang%3D%22en-US%22%3E%3CP%3EAnother%20way%20is%20to%20select%20first%20part%20of%20the%20column%20E%20(E2%3AE210)%2C%20Data-%26gt%3BText%20to%20Columns%20and%20on%20the%20third%20step%20of%20the%20wizard%20select%20dates%20and%20DMY.%20Repeat%20the%20same%20for%20the%20rest%20(starting%20from%20E211)%20but%20apply%20YMD.%20And%20that%20apply%20date%20format%20to%20entire%20column%20E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-307846%22%20slang%3D%22en-US%22%3ERe%3A%20dates%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-307846%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20noticed%20that%20your%20dates%20column%20contains%20two%20different%20ways%20of%20presenting%20a%20date.%20Provided%20that%20this%20this%20does%20not%20change%2C%20you%20can%20use%20a%20combination%20of%20IF%2C%20LEFT%2C%20RIGHT%20and%20MID%20functions%20together%20with%20YEAR%2C%20MONTH%20and%20DAY%20functions%20to%20extract%20day%2C%20month%20and%20year%20from%20the%20'Date'%20column.%20Subsequently%2C%20you%20can%20use%20DATE%20function%20to%20reconstruct%20dates%20in%20the%20'dates'%20format%2C%20which%20then%20can%20be%20sorted.%20Please%20find%20attached%20a%20solution%20(%20I%20have%20converted%20the%20data%20to%20a%20table%20for%20an%20easier%20sort).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EYury%3C%2FP%3E%3C%2FLINGO-BODY%3E
placerdog
Occasional Visitor

Can someone help with converting these dates into a format that I can sort from most recent.  Although I have tried some functions but none work.  Thanks

2 Replies

Hi,

 

I have noticed that your dates column contains two different ways of presenting a date. Provided that this this does not change, you can use a combination of IF, LEFT, RIGHT and MID functions together with YEAR, MONTH and DAY functions to extract day, month and year from the 'Date' column. Subsequently, you can use DATE function to reconstruct dates in the 'dates' format, which then can be sorted. Please find attached a solution ( I have converted the data to a table for an easier sort).

 

Thanks

Yury

Another way is to select first part of the column E (E2:E210), Data->Text to Columns and on the third step of the wizard select dates and DMY. Repeat the same for the rest (starting from E211) but apply YMD. And that apply date format to entire column E.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies