vba - Specify date format of a large amount of input data -


i have input spreadsheet needs sorted date. current format of date in uk format (dd/mm/yyyy) need in yyyy-mm-dd (actually don't, need sort , format foolproof way of sorting). needs done in vba it's part of bigger project allows bunch of data collation @ once. other problem input sheet can quite large (150,000+ rows). so, while could parse through each row of data , change around way need, horrifically slow , not option.

currently i'm using bit of code format date yyyy-mm-dd:

inputgadrsheet.columns(7).numberformat = "yyyy-mm-dd"

but, excel outsmarts me , assumes date format of column in format (mm/dd/yyyy) messes , half of values in column don't meet requirement (days above 12th) don't formatted @ all. there way tell excel format current data in? way won't assume it's in date format...

is solution change excel region uk. assume done using vba, seems risky...

if data in excel column, can't reinterpret values: excel date values (internally) number, 1 representing 1900-01-01. after data has been (mis-)interpreted excel there's no way back.

the question is: input data sheet from? if dates entered correctly, reformatting possible without problem , not affect sorting (which depends on numeric value of date). if data comes text file (probably .csv-kind), sure read ii text , use excel worksheet functions or vba interpret values.


Comments

Post a Comment

Popular posts from this blog

matlab - Deleting rows with specific rules -

i got 20*3 cell array , need delete rows contains "137", "2" , "n:t" origin data: 't' '' '' 'np(*)' '' '' [ 137] '' '' [ 2] '' '' 'are' 'and' 'np(fcc_a1#1)' '' '' '1:t' [ 1200] [0.7052] '' [1.2051e+03] [0.7076] '' 'are' 'and' 'np(fcc_a1#3)' '' '' '2:t' [ 1200] [0.0673] '' [1.2051e+03] [0.0671] '' 'are' 'and' 'np(m23c6)' '' '' '3:t' [ 1200] [0.227...
Read more

jquery - How would i go about shortening this code? And to cancel the previous click on click of new section? -

my code below changes image people in team sector, can see theres alot of repeated code; there way shorten it? also currently, allows click on sections without turning off clicked one; assume toggle() function, couldn't work other way! still quite new jquery coding, grand!! cheers in advance! $("#team_client_services").click(function () { if ($(".team_client_services").hasclass("team_hide")) { $(".team_client_services").toggleclass("team_hide"); $(".team_client_services2").toggleclass("team_show"); } else { $(".team_client_services").toggleclass("team_hide"); $(".team_client_services2").toggleclass("team_show"); }; }); $("#team_management").click(function () { if ($(".team_management").hasclass("team_hide")) { $(".team_management").toggleclass("team_hide"); $(".team_management2...
Read more