Re: kako ugnijezditi 4 SEARCH-funkcije (formule) u IF funkciju - izvlačenje datuma iz tekstaCode:
=VALUE(MID(" "&B2&" ";SEARCH("?/";B2);10))
=VALUE(MID(" "&B2&" ";SEARCH("?/";B2);10))
Zanimljivo je rešenje koje pretvara datum-kao-tekst i *bez pretrage* (dakle, kad imamo samo datum u "mm/dd/yyyy" formatu):
Code:
=--(LEFT(RIGHT(B2;4)&"/"&TEXT(B2;"dd/mm");10))
=--(LEFT(RIGHT(B2;4)&"/"&TEXT(B2;"dd/mm");10))
A još zanimljivije je da nam u idealnom slučaju čak ni formula nije potrebna -opet, uslov je da imamo samo datume unete kao tekst.
Postupak je ovaj:
1. Obeležimo kolonu sa datumima i Data / Text to Columns
2. Izaberemo Delimited > Next.
3. Uklonimo sve eventualne 'delimitere' i pritisnemo Next.
4. Konačno, kao format izaberemo Date MDY > Finish.
Edit: Unapređena konverzija "bez pretrage" -tačnije, verzija koja zapravo 'radi' za promenljive dd i mm. :D
Code:
=--(RIGHT(B2;4)&"/"&LEFT(B2;MAX({5;4;3}*(MID(B2;{6;5;4};1)="/"))))
=--(RIGHT(B2;4)&"/"&LEFT(B2;MAX({5;4;3}*(MID(B2;{6;5;4};1)="/"))))
(Ipak, godina i dalje mora biti u 4-cif. formatu.)
[Ovu poruku je menjao 3okc dana 16.10.2012. u 09:51 GMT+1]
Have you found helpful examples, solutions or ideas-in-work, please consider the
following:
• Citation of *actual page* you are going to using is welcome.
• Have you saw "Value Added" examples you are going to incorporate to your
profitable blog, daily job or any money making venture: How about online donation?
↗Ana & Vlade Divac Foundation
Hvala!
following:
• Citation of *actual page* you are going to using is welcome.
• Have you saw "Value Added" examples you are going to incorporate to your
profitable blog, daily job or any money making venture: How about online donation?
↗Ana & Vlade Divac Foundation
Hvala!
