SOLVED
Home

Sorting Data

%3CLINGO-SUB%20id%3D%22lingo-sub-869523%22%20slang%3D%22en-US%22%3ESorting%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869523%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone.%20Can%20you%20please%20help%20me%20to%20solve%20my%20problem.%20I%20have%20series%20of%20joined%20data%20in%20Year%20column%20with%20year-month%20separated%20by%20%22-%22.%20What%20I%20want%20is%20to%20separate%20dates%20for%20From%20Year%20and%20To%20Year.%20I%20tried%20to%20use%20Left%20function%20%3DLEFT(H2%2CLEN(H2)-SEARCH(%22-%22%2CH2)).%20However%2C%20how%20do%20I%20go%20about%20if%20there%20is%20only%20starting%20date%20available%20in%20Year%20column%20(for%20example%2014.4-).%20Once%20I%20use%20Left%20function%2C%20it%20gives%20me%20a%20blank%20result.%3C%2FP%3E%3CP%3EAnother%20question%20is%20how%20do%20I%20convert%20that%20date%20into%20full%20date%20(for%20example%2014.4%20into%202014.04%20or%2099.9%20into%201999.09)%3F%3C%2FP%3E%3CP%3EYour%20help%20is%20appreciated!!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20490px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133067iC756C1F946256CFC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%2223-09-2019%2010-56-59%20a-m-.jpg%22%20title%3D%2223-09-2019%2010-56-59%20a-m-.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-869523%22%20slang%3D%22en-US%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-869566%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869566%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F413321%22%20target%3D%22_blank%22%3E%40AlexeyNZ%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETake%20a%20look%20at%20the%20attached%20file%20for%20a%20possible%20solution%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20955px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133075iDA2B6562F4BE15EB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22date%20conversion.PNG%22%20title%3D%22date%20conversion.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-869595%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869595%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3EBrilliant%2C%20worked%20like%20a%20charm!%20Many%20thanks!!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-869598%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869598%22%20slang%3D%22en-US%22%3EGlad%20to%20help.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20haven't%20explored%20Excel's%20Power%20Query%20capability%20yet%20it's%20worth%20the%20effort.%20It's%20great%20at%20automating%20a%20solution%20to%20this%20sort%20of%20problem%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871182%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871182%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3EWill%20give%20it%20a%20try%20for%20sure%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871204%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871204%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Wyn%2C%3C%2FP%3E%0A%3CP%3EAs%20a%20comment%2C%20I'd%20modify%20a%20bit%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(DATE(2000-100*INT(L3%2F50)%2BL3%2CM3%2C1)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eto%20avoid%20locale%20dependency%20and%20handle%20missing%20of%20period%20end.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133223i11832FDA51D274BB%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%3C%2FLINGO-BODY%3E
Highlighted
AlexeyNZ
Occasional Contributor

Hi everyone. Can you please help me to solve my problem. I have series of joined data in Year column with year-month separated by "-". What I want is to separate dates for From Year and To Year. I tried to use Left function =LEFT(H2,LEN(H2)-SEARCH("-",H2)). However, how do I go about if there is only starting date available in Year column (for example 14.4-). Once I use Left function, it gives me a blank result.

Another question is how do I convert that date into full date (for example 14.4 into 2014.04 or 99.9 into 1999.09)?

Your help is appreciated!!!!

 

23-09-2019 10-56-59 a-m-.jpg

5 Replies
Solution

@AlexeyNZ 

 

Take a look at the attached file for a possible solution

 

date conversion.PNG

 

@Wyn HopkinsBrilliant, worked like a charm! Many thanks!!!!

Glad to help.

If you haven't explored Excel's Power Query capability yet it's worth the effort. It's great at automating a solution to this sort of problem

@Wyn HopkinsWill give it a try for sure

@Wyn Hopkins 

Hi Wyn,

As a comment, I'd modify a bit like

=IFERROR(DATE(2000-100*INT(L3/50)+L3,M3,1),"")

to avoid locale dependency and handle missing of period end.

image.png

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies