excel - Converting from string to date -

short story:

my question 2 fold:

1. how copy on text 05-jun-2014 text file text 05-jun-2014 not displayed #name? or cut off @ 5? (i need whole thing copied over.)

2. how take text , make date?

long story

in text file have dates in format day-month-year, e.g 05-jun-2014. see, "e" in june cut off. if type in 05-jun-2014 in excel, recognizes it. use cdate convert string date.

my problem multi-fold. want excel vba recognize 05-jun-2014 date.

if try copy phrase 05-jun-2014 text document. 5 copy over. copy string jagged array jaggarray , there call it.

below can see code how copy text:

private function creategcstruct(byref tempstring() string, byval anum integer, byval onum integer, _ byref olist() string, byval index integer, byval gcsoidate date, byval gcfname string, _ byval typename string) gcbstruct  '...code...  ' jaggedarray: dim jaggarray() variant  '...code...  '2) capture structure information textfile array      'a) remove unnecessary spaces existing array , place jaggedarray     jaggarray = splitstringarrayelements(tempstring)  '...code...  end function 

splitstringarrayelements function():

private function splitstringarrayelements(tempstring() string) variant() ' 1) variables:     dim j long     dim trimmedstring string     dim jaggarray() variant     redim jaggarray(lbound(tempstring()) ubound(tempstring()))     dim emptystringarrayplaceholder() string     redim emptystringarrayplaceholder(0 0) ' 2) remove unwanted spaces tempstring() , create jaggedarray     j = lbound(tempstring()) ubound(tempstring())         ' remove spaces inbetween         trimmedstring = trim(tempstring(j))         ' if empty line, array empty         if trimmedstring = ""             jaggarray(j) = emptystringarrayplaceholder         ' else add array without spaces         else             jaggarray(j) = splitadv(trimmedstring, " ")         end if     next j      splitstringarrayelements = jaggarray end function 

splitadv() function:

public function splitadv(byval inputtext string, optional byval delimiter string) variant  ' function splits sentence in inputtext ' words , returns string array of words. each ' element of array contains 1 word.      ' constant contains punctuation , characters     ' should filtered input string.     const chars = """-" 'potential options are: "!?,.;:""'()[]{}"     dim strreplacedtext string     dim intindex integer      ' replace tab characters space characters.     strreplacedtext = trim(replace(inputtext, _          vbtab, " "))      ' filter specified characters string.     intindex = 1 len(chars)         strreplacedtext = trim(replace(strreplacedtext, _             mid(chars, intindex, 1), " "))     next intindex      ' loop until consecutive space characters     ' replaced single space character.     while instr(strreplacedtext, "  ")         strreplacedtext = replace(strreplacedtext, _             "  ", " ")     loop      ' split sentence array of words , return     ' array. if delimiter specified, use it.     'msgbox "string:" & strreplacedtext     if len(delimiter) = 0         splitadv = vba.split(strreplacedtext)     else         splitadv = vba.split(strreplacedtext, delimiter)     end if end function 

if remove - when copy, 3 parts become: 5-jan-00 #name? 14:42:58

here copy on date structure (assuming removed "-"). goal copy on parts of date "05", "jun", , "2014", , recombine them in format: 05 jun,2014, , try convert date:

dim rdstart integer      ' row of first gcdate dim cdstart integer      ' col of first gcdate dim tempd1 date dim tempd2 date rdstart = 6                                            ' row of first gcdate cdstart = 2                                            ' column of first gcdate 'collect gcdate '***i errors below*** tempd1 = cdate(jaggarray(rdstart - 1)(cdstart - 1) & " " & jaggarray(rdstart - 1)(cdstart) & ", " & jaggarray(rdstart - 1)(cdstart + 1)) tempd2 = cdate(jaggarray(rdstart - 1)(cdstart + 2)) creategcstruct.gcdate = tempd1 + tempd2 

you use cdate without splitting string @ all. cdate recognise many date formats, , long not ambiguous (which "05-jun-2014" isn't), conversion should work fine. works me:

dim d date d = cdate("05-jun-2014") range("a1")     .value = d     .numberformat = "dd-mmm-yyyy" end