SOLVED

Sorting Data

Copper 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
best response confirmed by AlexeyNZ (Copper Contributor)
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

1 best response

Accepted Solutions
best response confirmed by AlexeyNZ (Copper Contributor)
Solution

@AlexeyNZ 

 

Take a look at the attached file for a possible solution

 

date conversion.PNG

 

View solution in original post