Split of dates

[Aberdham]

Dear all,

I have an Excel sheet with description such as deposit period 16.04-16.05.2018

and I would like to make a split to fill in the respective column
start date : 16.04
ene date: 16.05.2018

Could any of you come up with a formula that could help me to split the Dates?

would be greatly appreciated !

Thanks

 

[Peter_SSs]

We have finally got it working !!! you are a life saver Peter!!

Thank you so much !!

No problem, glad we got there in the end.

 

[Aberdham]

OK, I think I understand now. You were on the right track with IFERROR. Try these

Split Dates (4)

	B	C	D
1	Description:	Start	End
2	Deposit period 16.04-16.05-2018_DSL555601	16/04/2018	16/05/2018
3	credit lease_16.04-16.05.2018_DSL556262	16/04/2018	16/05/2018
4	withdraw period 16.04-16.05.2018_DSL556985	16/04/2018	16/05/2018
5	credit purchase 16.04-16.05.2018_DSL545421	16/04/2018	16/05/2018
6	Deposit_April.2018_DSL454512
7	withdraw of 18 days_DSL454541

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:299px;"><col style="width:97px;"><col style="width:82px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =IFERROR(SUBSTITUTE(REPLACE(MID(B2,LEN(B2)-25,16),6,6,""),".","/")+0,"") D2 =IF(C2="","",SUBSTITUTE(MID(B2,LEN(B2)-19,10),".","/")+0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi Peter,

Sorry to bother you again, recently I have received data that have changed their format, instead of
withdraw period 16.(0)4-16.(0)5.2018_DSL556985; they have changed to [FONT=Arial, Arial]withdraw period 16.4-16.5.2018_DSL556985[/FONT]
[FONT=Arial, Arial]the 0 are omitted, however I was able to extract by modifying the formula by
[/FONT]=IFERROR(DATE(MID(B2,FIND("-",B2)+6,4),MID(B2,FIND("-",B2)-1,1),MID(B2,FIND("-",B2)-4,2)),"")

what if the month is December, and the day is 5. how should I modify this?
also, how would I be able to modify the result in D2 to auto populate the results?

Thank you for your help !

 

[Peter_SSs]

Try these
C2: =DATE(MID(B2,FIND("_",B2,FIND("-",B2))-4,4),SUBSTITUTE(MID(B2,FIND("-",B2)-2,2),".",""),MID(B2,FIND(".",B2)-2,2))
D2: =SUBSTITUTE(LEFT(SUBSTITUTE(REPLACE(B2,1,FIND("-",B2),""),"_",REPT(" ",10)),10),".","/")+0

I'm suspecting though that there will still be problems when a period spans 2 years. For example 16 December 2018 to 16 January 2019. What will the data look like then?

 

[Aberdham]

Hi Peter,

Thank you for your Response. The formula in C2 works for certain entries, but not for the others such as

candi_cos_1.1-31.1.2018_DSL_XXXXXX
candi_cos_1.1-9.1.2018_DSL_XXXXXX

However, formula in D2 works perfectly even if C2 has #Value

could you please advise.

Thank you

 

[Peter_SSs]

The formula in C2 works for certain entries, but not for the others such as

candi_cos_1.1-31.1.2018_DSL_XXXXXX
candi_cos_1.1-9.1.2018_DSL_XXXXXX

Yes, my oversight sorry. Try this adjustment.

C2: =DATE(MID(B2,FIND("_",B2,FIND("-",B2))-4,4),SUBSTITUTE(MID(B2,FIND("-",B2)-2,2),".",""),SUBSTITUTE(MID(B2,FIND(".",B2)-2,2),"_"," "))

 

[Aberdham]

Hi Peter,

That works great! Thank you so much !

However, I am strugging with extracting the left part of it. the part which Prior the start date and end date.
=IFERROR(LEFT(B2;SEARCH("_ESN";B2)-18);"")

 

[Peter_SSs]

Is this what you mean?

Excel Workbook
	B	C	D	E
1	Description:	Start	End	Left part
2	Deposit period 16.04-16.05-2018_DSL555601	16/04/2018	16/05/2018	Deposit period
3	credit lease_16.04-16.05.2018_DSL556262	16/04/2018	16/05/2018	credit lease_
4	withdraw period 16.04-16.05.2018_DSL556985	16/04/2018	16/05/2018	withdraw period
5	credit purchase 16.04-16.05.2018_DSL545421	16/04/2018	16/05/2018	credit purchase
6	Deposit period 16.4-16.5-2018_DSL555601	16/04/2018	16/05/2018	Deposit period
7	credit lease_16.4-16.5.2018_DSL556262	16/04/2018	16/05/2018	credit lease_
8	withdraw period 16.11-16.12.2018_DSL556985	16/11/2018	16/12/2018	withdraw period
9	credit purchase 16.04-16.05.2018_DSL545421	16/04/2018	16/05/2018	credit purchase
10	candi_cos_1.1-31.1.2018_DSL_XXXXXX	1/01/2018	31/01/2018	candi_cos_
Split Dates (7)

 

[Aberdham]

Perfectly done !!

Thank you so much Peter!

 

[Peter_SSs]

Perfectly done !!

Thank you so much Peter!

You’re welcome.