IZBORNIK HOME FORUM ACCESS 2003 EXCEL 2003 WORD 2003  .
   
   
HOME
FORUM Win Tips&Tricks
   
KAKO INSTALIRATI
WINDOWS XP ?
Kako instalirati Win XP sa USB STICKA
Kako instalirati WINDOWS 7 ?
Naučite za 15 minuta raditi u Windows XP
Naučite Internet Explorer i Outlook Express
za 15 minuta
Kako kreirati BOOT CD za instalaciju Win95
   
MS OFFICE 2003
MS OFFICE 2007
   
   
HOME NETWORK
tutorijal za mreže
Network Windows 7 - XP
   
ZANIMLJIVI LINKOVI
BROJEVNI SUSTAVI
(DEC, OKT, BIN, HEX )
CMD - Command Prompt
CISCO - CCNA tutoriali
VLSM and SUBNETTING
   
Tutorijali za phpBB forum
JAVASCRIPT
VISUAL BASIC 6.0
AUTOCAD 2007
 
.
MICROSOFT EXCEL 2007 - Konverzija datuma koji je formiran kao tekst u originalan format datuma - Convert text-date to original date format - kako izvući datum iz teksta

Kako konvertirati datum kao tekst u originalni datum format
(Convert text to date - Extract date from text)

Search This Web Site




Konverzija datuma koji je formiran kao tekst u originalan format datuma (Extract dates from text)

Ako imate potrebu povlačiti datum sa neke internet stranice u Excel Worksheet, isti će vam biti u formatu teksta. Naravno vi imate potrebu isti konvertirati (pretvoriti) u originalan format datuma, ili pak ako ima nekih viškova dijelova u tekstu datuma (kao primjer sa slike ispod).

Na ovoj slici ispod datum je formata

Datum kao tekst (Date and time like text)

M/D/YYYY + vremenski ostatak
M/DD/YYYY + vremenski ostatak
MM/D/YYYY + vremenski ostatak
MM/DD/YYYY + vremenski ostatak

Iskoristite pomoćne stupce da bi izvukli važne elemente datuma (dan, mjesec, godina). Naravno nakon završetka možete sakriti stupce B, C i D.
Formule na slici iznad su slijedeće:

B2: formula za izračun dana =MID(A2;SEARCH("/";A2;1)+1;SEARCH("/";A2;4)-SEARCH("/";A2;1)-1)
C2: formula za izračun mjeseca =LEFT(A2;SEARCH("/";A2;1)-1)
D2: formula za izračun godine =MID(A2;SEARCH("/";A2;4)+1;4)
E2: =DATE(D2;C2;B2)

NARAVNO možemo izbjeći sva tri stupca tako što formule ugnijezdimo u jednu, pa to izgleda ovako

=DATE(MID(A2;SEARCH("/";A2;4)+1;4);LEFT(A2;SEARCH("/";A2;1)-1);MID(A2;SEARCH("/";A2;1)+1;SEARCH("/";A2;4)-SEARCH("/";A2;1)-1))

Ako je u text-date format D/M/YYYY tada samo promijenite redoslijed formula u završnoj formuli (dakle dani su na prvom mjestu znamenki)


Ovdje postoji jedna problematika kod svih formula za izvlačenje datuma iz teksta. Radi se o broju znakova u datumu (dani i mjeseci). U pitanju je jesu li jedna ili dvije znamenke u samom text-datumu.

Slika ispod prikazuje situaciju gdje imamo samo datum kao tekst bez vremenskih dodataka. Za izvlačenje stvarnog datuma u "datum format" koristite formulu ispod slike.

convert text to date

=DATE(VALUE(RIGHT(A2;4));VALUE(IF(ISERROR(SEARCH("/??/";A2));MID(A2;SEARCH("/?/";A2)+1;1);MID(A2;SEARCH("/??/";A2)+1;2)));VALUE(LEFT(A2;FIND("/";A2;1)-1)))

ili još kraće

= DATE(RIGHT(TEXT(A2;"dd.mm.yyyy");4);MID(TEXT(A2;"dd.mm.yyyy");4;2);LEFT(TEXT(A2;"dd.mm.yyyy");2)) ili druga varijanta formule

=DATE(RIGHT(A2;4);IF(ISERROR(SEARCH("/??/";A2));MID(A2;SEARCH("/?/";A2)+1;1);MID(A2;SEARCH("/??/";A2)+1;2));LEFT(A2;FIND("/";A2;1)-1))

Uočite razliku kod upotrebe SEARCH funkcije. U prvom primjeru formule korišten je dio argumenta "/" a u drugom primjeru druge "/?/" i "/??/"

Ove dvije formule ispod su ideja kolege Zorana (koje izgledaju kao "kobasice") rade isti posao i ovdje ih prikazujem tek toliko da uočite kako se isti problem može riješiti na više načina. Ako vas zanima kako funkcioniraju provucite ih kroz "Evaluate Formula" opciju. Uočite 4 uvjeta tj. varijante za svaki datum koji ima kombinaciju znamenki dana/mjesec. Zbog tih varijanti upotrijebljen je "?" upitnik kao dio argumenta u funkcijama MATCH.

=DATE(VALUE(RIGHT(A2;4));--CHOOSE(SUM({4;3;2;1}*(1-ISNA(MATCH({"?/";"??/";"?/?";"??/?"}&"?/????";A2;0))));MID(A2;4;2);MID(A2;3;2);MID(A2;4;1);MID(A2;3;1));VALUE(LEFT(A2;FIND("/";A2;1)-1)))

Dakle ako formulu iznad gledamo kroz argumente u funkciji DATE to izgleda ovako:

=DATE(year;month;day)

=DATE(VALUE(RIGHT(A2;4));--CHOOSE(SUM({4;3;2;1}*(1-ISNA(MATCH({"?/";"??/";"?/?";"??/?"}&"?/????";A2;0))));
MID(A2;4;2);MID(A2;3;2);MID(A2;4;1);MID(A2;3;1))
;VALUE(LEFT(A2;FIND("/";A2;1)-1)))

ili druga ideja sa IF i ISNA funkcijama

=DATE(VALUE(RIGHT(A2;4));--IF(ISNA(MATCH("?/?/????";A2;0));IF(ISNA(MATCH("??/?/????";A2;0));IF(ISNA(MATCH("?/??/????";A2;0));IF(ISNA(MATCH("??/??/????";A2;0));"";MID(A2;4;2));MID(A2;3;2));MID(A2;4;1));MID(A2;3;1));VALUE(LEFT(A2;FIND("/";A2;1)-1)))

još jedna formula koja je podugačka i koristi Search i 4 uvjeta u IF funkciji (autor Ratko)

=DATE(MID(A2;SEARCH("/";A2;4)+1;4);IF(IFERROR(SEARCH("?/?/????";A2;1)=1;FALSE);VALUE(MID(A2;3;1));IF(IFERROR(SEARCH("??/?/????";A2;1)=1;FALSE);VALUE(MID(A2;4;1));IF(IFERROR(SEARCH("?/??/????";A2;1)=1;FALSE);VALUE(MID(A2;3;2));IF(IFERROR(SEARCH("??/??/????";A2;1)=1;FALSE);VALUE(MID(A2;4;2));""))));LEFT(A2;SEARCH("/";A2;1)-1))

ili ova koja također koristi varijante maski u funkciji Search (autor Zoran)

=DATE(RIGHT(A2;4);--IF(ISERR(SEARCH("??/??/????";A2));IF(ISERR(SEARCH("??/?/????";A2));IF(ISERR(SEARCH("?/??/????";A2));IF(ISERR(SEARCH("?/?/????";A2));FALSE;MID(A2;3;1));MID(A2;3;2));MID(A2;4;1));MID(A2;4;2));LEFT(A2;FIND("/";A2;1)-1))


Ovaj primjer ispod pokazat će vam kako izvući datum iz teksta koji osim datuma sadrži tekst sa obje strane datuma, samo pomoću jedne formule. Ovdje moramo voditi računa da u formatu datuma nema dvoznamenkasti broj za jednoznamenkasti element. npr: ne smije biti oznaka za dan 05 ako je to 5-i dan u mjesecu.

izvlačenje datuma iz teksta

Slika iznad prikazuje tekst sa datumom u formatu M/D/G

Formule za izvlačenje (extract) pojedinog elementa datuma (mjeseca, dana ili godine) su slijedeće (autor Zoran):

formula za extract Dana iz teksta
=--IF(ISERR(SEARCH("/??/";A2));IF(ISERR(SEARCH("/?/";A2));FALSE;MID(A2;SEARCH("/?/";A2)-1;1));MID(A2;SEARCH("/??/";A2)-2;2))

formula za extract Mjeseca iz teksta
=--IF(ISERR(SEARCH("/??/";A2));IF(ISERR(SEARCH("/?/";A2));FALSE;MID(A2;SEARCH("/?/";A2)+1;1));MID(A2;SEARCH("/??/";A2)+1;2))

formula za extract Godine iz teksta
=--IF(ISERR(SEARCH("/??/";A2));IF(ISERR(SEARCH("/?/";A2));FALSE;MID(A2;SEARCH("/?/";A2)+3;4));MID(A2;SEARCH("/??/";A2)+4;4))

Kada ove tri formule spojimo u jednu koja će biti za sve varijante dobivamo formulu za pronalaženje datuma u tekstu
=IF(ISERR(SEARCH("/??/";A2));IF(ISERR(SEARCH("/?/";A2));FALSE;DATE(--MID(A2;SEARCH("/?/";A2)+3;4);--MID(A2;SEARCH("/?/";A2)-1;1);--MID(A2;SEARCH("/?/";A2)+1;1)));DATE(--MID(A2;SEARCH("/??/";A2)+4;4);--MID(A2;SEARCH("/??/";A2)-2;2);--MID(A2;SEARCH("/??/";A2)+1;2)))

pronalaženje datuma u tekstu

Slika iznad prikazuje tekst sa datumom u formatu D/M/G

Ovo je formula koja će pronaći datum u tekstu na slici iznad i kao rezultat vratiti datumski format
=VALUE(IF(IFERROR(FIND("/";A28;1);0)=0;"Nema datuma";LEFT(RIGHT(A28;LEN(A28)-IFERROR(FIND("/";A28;1);0)+3);10)))

ili još kraća formula

=VALUE(MID(A28&" ";SEARCH("??/";A28);10))

.

Adsense sponzor




Razdvajanje datuma i vremena u dva stupca ili izvlačenje elemenata datuma i vremena

Ako imate određeni oblik datuma i vremena a pisan je kao tekst tada isti možete razdvojiti slijedećih pomoću formula i oblikovati kao datum i vrijeme.
Uzmimo situaciju kao na slici ispod, trebamo razdvojiti datum i vrijeme u dva stupca pa čak možda i svaki element zasebno.

Formule na slici su slijedeće:

Ćelije B2 i C2 su formatirane kao datum i vrijeme

ćelija B2: =VALUE(LEFT(A2;10))
ćelija C2: =VALUE(RIGHT(A2;9))
ćelija C4: =DAY(B2)
ćelija C5: =MONTH(B2)
ćelija C6: =YEAR(B2)
ćelija C8: =HOUR(C2)
ćelija C9: =MINUTE(C2)

 

izdvajanje elemenata datuma i sati iz teksta zasebno u stupce

Text to Columns

Ako želimo možemo pomoću funkcije text to columns izdvojiti svaki element datuma i vremena u zaseban stupac (kolonu). Na slici ispod imamo određeni tekst koji smo kopirali s neke web stranice ili bilo već otkuda. Želimo izdvojiti svaki element zasebno iz teksta a možemo i grupe teksta kao npr. datum posebno a vrijeme posebno.

kako razdvojiti datum i vrijeme iz ćelije

Convert Text to Columns - Fixed width

Adsense sponzor




određivanje fiksne širine stupaca prilikom konvertiranja teksta u stupce

Postavljanje fiksne širine podjele teksta u stupce

U ovom koraku čarobnjaka možemo postaviti po želji podjelu teksta na stupce. klikom miša na brojevnoj skali (crti) ili u samom tekst postavljamo razdjelnike u obliku strelice. Iste možemo pomicati pa i obrisati. Uočite da sam ja postavio više razdjelnika kako bi dobio više stupaca a time i veću podjelu teksta.

Na slici ispod uočite rezultat konvertiranja teksta u stupce putem čarobnjaka na opisano na slikama iznad. Sa ovim podacima sada možemo raditi daljnje proračune i kalkulacije.

 

Adsense sponzor




Neke od formula koje se koriste u svrhu izvlačenja datuma iz teksta možete pronaći na internetu (ima ih podosta). Evo nekih od njih u kratkom sažetku

=TEXT(A1;"MM-DD-YYYY") ova formula će konvertirati text-datum u datum naznačenog formata

=IF(ISERROR(MID(B1;SEARCH("??/??/????";B1;1);10));"";TEXT(MID(B1; SEARCH("??/??/????";B1;1); 10);"MM/DD/YYYY")) (ako je gornja formula u B1)

=DATEVALUE(IFERROR(MID(A1;FIND("/";A1;1)-2;10);".."))

=DATEVALUE(MID($B1;MIN(IFERROR(FIND(CHAR(ROW($48:$57));$B1);""));10))

=IFERROR(- -(MID(A1;FIND("/";A1)-2;10));"nema datuma") => ARRAY formula (formula polja ili matrična formula)

=DATE(0+MID(A1;FIND("/";A1)+4;4);MID(A1;FIND("/";A1)+1;2);MID(A1;FIND("/";A1)-2;2))

=DATE(MID(B1;7;4);MID(B1;1;2);MID(B1;4;2))

=IFERROR(DATEVALUE(MID(A1;MIN(FIND("0";A1);FIND("1";A1));10));"nema datuma u celiji")

=MID($A1;SEARCH("??/??/????"; $A1;1);10)

=IFERROR(DATEVALUE(MID(A1;SEARCH("??/??/????";A1);10));"")

 




Adsense sponzor




©- 2006 - 2020 - IvanC  - Sva prava pridržana.  ic.ims.hr