excel vba - How to get the Date from Time Stamp -

-

am having 1 excel file contains data multiple sheets. in 1 column having time stamp 21/6/12 10:33:07:am. system date format "m/d/yyyy"
while converting date 21/06/2012. able date showing in wrong. 06-12-2021 should 21-06-2012. using below code.

ex : 26/6/12 11:15:07:am should 26/06/2012
21/6/12 10:33:07:am should 21/06/2012 

public sub convtdate() dim parsedatetime date application.screenupdating = false each datcol in ws_raw2.range("i2:i65536")   x = instr(1, datcol, " ", vbtextcompare) - 1   if x > 0       parsedatetime = datevalue(left(datcol, x))       datcol.value = parsedatetime end if   next   application.screenupdating = true   end sub

please how date.
in advance.

datevalue expects date string in format set system.
excel help:

if date string includes numbers separated valid date separators, datevalue recognizes order month, day, , year according short date format specified system.

since data in day month year , system month day year need reconstruct parameter pass datevalue.

here's refactor of coed, dealing few other issues:

  • dim all variables (use option explicit force this)
  • only process rows need to
  • loop variant array rather range, much faster
public sub convtdate()     dim parsedatetime date     dim long     dim x long     dim a() string     dim dat variant     dim rng range      application.screenupdating = false     set rng = range(cells(2, 9), cells(rows.count, 9).end(xlup))     dat = rng.value     = 1 ubound(dat, 1)         x = instr(1, dat(i, 1), " ", vbtextcompare) - 1         if x > 0             = split(left(dat(i, 1), x), "/")             parsedatetime = datevalue(a(1) & "/" & a(0) & "/" & a(2))             ' or if dont know system data format use             ' parsedatetime = dateserial(a(2), a(1), a(0))             dat(i, 1) = parsedatetime         end if     next     rng = dat     application.screenupdating = true end sub

Comments

Post a Comment

Popular posts from this blog

c++ - Linked List error when inserting for the last time -

-
so have part of code here this loop 5 times (max = 5) insert word link list. for ( int = 0 ; < max ; i++ ) { string alphabet = g->returnalphabets(); l[i]->addwords(alphabet[i]); } and have insert word part here string line; fstream myfile ("words.txt"); while (!myfile.eof()) { getline(myfile,line); if ( alphabet == line[0] ) { listnode *newnode = new listnode; if ( head == null ) { newnode->item = alphabet; newnode->next = null; head = newnode; } else { newnode->item = line; prev = cur; prev->next = newnode; } cur = newnode; } } myfile.close(); my problem gives me error on last loop. meaning on max = 4, insert node. rest of loop fine. when decrease max 4 meaning loop 4 times, program error-free when program loops 5 times gives error. anyone can poin...
Read more

java - activate/deactivate sonar maven plugin by profile? -

-
i use sonar plugin executes if activated in maven profile. sonar runs on every compile. the problem build fail other team members not have sonar application installed locally. pom.xml checked in team members same copy including sonar configuration, don't want them modify purpose of disabling sonar (they might inadvertently check modified copy in). please advise how configure maven (.m2/settings.xml) and/or pom.xml sonar feature can enabled/disabled local profile. here sonar configuration in each project's pom.xml: <plugin> <groupid>org.codehaus.mojo</groupid> <artifactid>sonar-maven-plugin</artifactid> <version>1.0</version> <executions> <execution> <id>make-sonar</id> <phase>compile</phase> <goals> <goal>sonar</goal> </goals> </execution> </executions> ...
Read more

tsql - Pivot with Temp Table (definition for column must include data type) -- SQL Server 2008 -

-
the following returning 2 errors: 'the definition column '0.63' must include data type.' 'the select list insert statement contains fewer items insert list. number of select values must match number of insert columns.' i understand need assign data type columns being inserted temp table don't quite understand how dynamic code however. if explanation on how @cols variable code working , specific issue. fldpk data type int , pivot fields i.e. @cols, float data type. declare @cols nvarchar(max), @query nvarchar(max); select @cols = stuff((select distinct ',' + quotename(fldci) fn_qryt_1() xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') set @query = 'select fldpk, ' + @cols + ' ( select fldpk ,fldni ...
Read more