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
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
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies