I am using the VBA DateSerial function to separate dates in a string, but Excel Ends Sub when year in string is '1000'. What am I doing wrong? -


i using vba dateserial function separate dates in string, , populate them on worksheet excel ends sub when year in string '1000'.

here example:

string data: 2012-01-012012-03-01 2013-01-012013-03-01 1000-01-011000-01-01  vba code: private sub worksheet_change(byval target range)     if not intersect(target, columns(1)) nothing         on error goto safe_exit         application.enableevents = false         dim rng range, str string, rw long         each rng in intersect(target, columns(1))             str = rng.value             if len(str) >= 8                 sheet2                     rw = .cells(rows.count, 1).end(xlup).offset(1, 0).row                     .cells(rw, 1) = dateserial(left(str,4), mid(str, 6, 2), mid(str, 9, 2))                      .cells(rw, 2) = dateserial(mid(str, 11, 4), mid(str, 16, 2), mid(str, 19, 2))                  end           end if         next rng     end if safe_exit:     application.enableevents = true end sub

no matter how many strings paste first worksheet, dates appear on second sheet until date year 1000. doing wrong?

excel bases dates number of days since midnight 30 december 1899 , not except negative dates.

even though appears can enter dates prior jan 1, 1900, if notice left-aligned. indicates have been accepted string values , not date.

you modify code set string value instead. valid date string automatically converted date. .cells(rw, 1) = cstr(dateserial(left(str,4), mid(str, 6, 2), mid(str, 9, 2)))

note 1/1/1904 minimum if 1904 date option selected.


Comments

Post a Comment

Popular posts from this blog

c# - Validate object ID from GET to POST -

i have method allows user access comment have made on site. have post method lets them update comment. my normal solution send entire comment model through, let them update it, update on database when post back. involve sending commentid through in hiddenfor , can manipulated. how can verify commentid sent in get method same i'm getting in post , not able alter comment wish? how can verify commentid sent in method same i'm getting in post? basically have validate following things in post - user logged-in user. authenticated users post comments. commentid in post , should valid commentid , should present in database. userid associated logged-in user should same userid associated comment . comment should contain userid column, can check @ time of update. to make sure update happens comment has been sent in get - hold commentid in session , in post action compare commentid value in session .
Read more

node.js - Custom Model Validator SailsJS -

when using custom model validator checks record within model. return within spread doesn't seem end control flow. //post.js model /** * custom validator **/ types: { isuservalid: function(user_id) { var promise = require('bluebird'); promise.all([ user.findone({id: user_id}) ]) .spread(function(user) { console.log(user); if (user === null || user === undefined) { console.log('failed'); return false; }else{ console.log('passed'); return true; } }); } }, my response standard validation failed response. { "error": "e_validation", "status": 400, "summary": "1 attribute invalid", "model": "post", "invalidattributes": { "owner": [ { "rule": "isuservalid", ...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more