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

 

[Aberdham]

If the text after the date section is always 10 characters like all of your latest examples, then the formulas can be shortened a little.

Split Dates (3)

	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

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

Spreadsheet Formulas

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

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks a lot Peter,

I have also descriptions that states as follows:

Deposit_April.2018_DSL454512

and i would like to have the value return to an empty cell instead of #value

what formula should i use? perhaps if error? or?

look Forward to hear from you soon

 

[Peter_SSs]

Deposit_April.2018_DSL454512

and i would like to have the value return to an empty cell ..

What value? Your previous question was about extracting start & end dates. Clearly this text does not contain those.

Is this a completely new question, or could this text be among the text of the previous examples?

More examples and expected results might help clarify.

 

[Aberdham]

What value? Your previous question was about extracting start & end dates. Clearly this text does not contain those.

Is this a completely new question, or could this text be among the text of the previous examples?

More examples and expected results might help clarify.

Hi Peter,

It is associated with the previous question about extracting Dates.

	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 6.Deposit_April.2018_DSL454512	16/04/2018	16/05/2018

<tbody>
</tbody>

7. withdraw of 18 days_DSL454541
so basically the start and end date don't Show anything in rows 6&7

 

[Peter_SSs]

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

Excel Workbook
	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
Split Dates (4)

 

[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="width: 30px; font-weight: bold;"><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

Thanks for your post Peter! I have added the formula in split date (3) and split date (4), it doesn't seem to work?

Returns to #value

 

[Peter_SSs]

Thanks for your post Peter! I have added the formula in split date (3) and split date (4), it doesn't seem to work?

I've posted my worksheet and you can see it is not returning an error value.
What about you post your worksheet using one of the methods suggested in the link in my signature block below, then we have a better chance of seeing what is actually going on? Alternatively, you could upload your workbook (any sensitive data removed or disguised) to a public file-share site (eg Dropbox) and give us a link to that file.

 

[Aberdham]

I've posted my worksheet and you can see it is not returning an error value.
What about you post your worksheet using one of the methods suggested in the link in my signature block below, then we have a better chance of seeing what is actually going on? Alternatively, you could upload your workbook (any sensitive data removed or disguised) to a public file-share site (eg Dropbox) and give us a link to that file.

	Start date	End date
cre fee 07.01-06.02.2018_DSL_555601	#VALUE!
depot fee 14.01-13.02.2018_DSL 545412
withdraw fee 09.01-08.02.2018_DSL 585230
deposit april_DSL_A52523
credi fee 01.01-31.01.2018_DSL 907587

<tbody>
</tbody><colgroup><col><col span="2"></colgroup>

The formula I have entered is
=DATE(MID(A2;FIND("-";A2)+7;4);MID(A2;FIND("-";A2)-2;2);MID(A2;FIND("-";A2)-5;2))+IFERROR(SUBSTITUTE(REPLACE(MID(A2;LEN(A2)-25;16);6;6;"");".";"/")+0;"")

 

[Aberdham]

I've posted my worksheet and you can see it is not returning an error value.
What about you post your worksheet using one of the methods suggested in the link in my signature block below, then we have a better chance of seeing what is actually going on? Alternatively, you could upload your workbook (any sensitive data removed or disguised) to a public file-share site (eg Dropbox) and give us a link to that file.

or perhaps it is easier this way, only invoices contain formats such as:
xx-RD-xx and xx-MD-xx Show values in the Dates column, otherwise Shows nothing?

Invoice No.	Description:	Start date	End date
15RD000415	cre fee 07.01-06.02.2018_DSL_555601
15MD000151	depot fee 14.01-13.02.2018_DSL 545412
15RC000145	withdraw fee 09.01-08.02.2018_DSL 585230	no entry	no entry
15DD002145	deposit april_DSL_A52523	no entry	no entry
15DC052145	credi fee 01.01-31.01.2018_DSL 907587	no entry	no entry

<tbody>
</tbody><colgroup><col><col><col span="2"></colgroup>

 

[Peter_SSs]

The formula I have entered is
=DATE(MID(A2;FIND("-";A2)+7;4);MID(A2;FIND("-";A2)-2;2);MID(A2;FIND("-";A2)-5;2))+IFERROR(SUBSTITUTE(REPLACE(MID(A2;LEN(A2)-25;16);6;6;"");".";"/")+0;"")

That formula is nothing like the formula I suggested.

In post #15 you quoted my formulas from post #8 so that is what I modified. Post #8 went with the qualification that there was always 10 characters after the date. Post #17 is the first sample data that does NOT have 10 characters after the date - there are now 11, so not too surprising that my formula didn't give the result you expected, but it does not give an error, it returns null strings (appearing as blank cells).

So, if the number of characters after the date can vary (you have never stated that) we must return to my suggestion from post #7 .
The modifications to those formulas would be

Excel Workbook
	B	C	D
1	Description:	Start	End
2	cre fee 07.01-06.02.2018_DSL_555601	7/01/2018	6/02/2018
3	depot fee 14.01-13.02.2018_DSL 545412	14/01/2018	13/02/2018
4	withdraw fee 09.01-08.02.2018_DSL 585230	9/01/2018	8/02/2018
5	deposit april_DSL_A52523
6	credi fee 01.01-31.01.2018_DSL 907587	1/01/2018	31/01/2018
Split Dates (5)

 

[Aberdham]

That formula is nothing like the formula I suggested.

In post #15 you quoted my formulas from post #8 so that is what I modified. Post #8 went with the qualification that there was always 10 characters after the date. Post #17 is the first sample data that does NOT have 10 characters after the date - there are now 11, so not too surprising that my formula didn't give the result you expected, but it does not give an error, it returns null strings (appearing as blank cells).

So, if the number of characters after the date can vary (you have never stated that) we must return to my suggestion from post #7 .
The modifications to those formulas would be

Split Dates (5)

	B	C	D
1	Description:	Start	End
2	cre fee 07.01-06.02.2018_DSL_555601	7/01/2018	6/02/2018
3	depot fee 14.01-13.02.2018_DSL 545412	14/01/2018	13/02/2018
4	withdraw fee 09.01-08.02.2018_DSL 585230	9/01/2018	8/02/2018
5	deposit april_DSL_A52523
6	credi fee 01.01-31.01.2018_DSL 907587	1/01/2018	31/01/2018

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

Spreadsheet Formulas

Cell Formula C2 =IFERROR(DATE(MID(B2,FIND("-",B2)+7,4),MID(B2,FIND("-",B2)-2,2),MID(B2,FIND("-",B2)-5,2)),"") D2 =IF(C2="","",SUBSTITUTE(MID(B2,FIND("-",B2)+1,10),".","/")+0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> #8 00040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

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

Thank you so much !!