{"id":1356,"date":"2014-03-05T23:05:55","date_gmt":"2014-03-05T16:05:55","guid":{"rendered":"http:\/\/www.itersdesktop.com\/?p=1356"},"modified":"2014-03-23T20:54:35","modified_gmt":"2014-03-23T13:54:35","slug":"nhung-ngo-nhan-ve-nhap-lieu-kieu-ngay-gio-trong-excel","status":"publish","type":"post","link":"https:\/\/www.itersdesktop.com\/vi\/2014\/03\/05\/nhung-ngo-nhan-ve-nhap-lieu-kieu-ngay-gio-trong-excel\/","title":{"rendered":"Nh\u1eefng ng\u1ed9 nh\u1eadn v\u1ec1 nh\u1eadp li\u1ec7u ki\u1ec3u ng\u00e0y gi\u1edd trong Excel"},"content":{"rendered":"

T\u00f4i c\u0169ng th\u01b0\u1eddng tham gia tr\u1ea3 l\u1eddi c\u00e1c c\u00e2u h\u1ecfi tr\u00ean Yahoo! H\u1ecfi & \u0110\u00e1p<\/a>. V\u1edbi kinh nghi\u1ec7m c\u1ee7a m\u00ecnh, \u0111\u00e3 v\u00e0 \u0111ang ch\u01a1i nhi\u1ec1u di\u1ec5n \u0111\u00e0n kh\u00f3 t\u00ednh nh\u01b0 StackExchange<\/a>, CodeProject<\/a> hay nh\u1eefng s\u00e2n ch\u01a1i b\u00e9 h\u01a1n t\u1ea7m forum th\u00ec t\u00f4i \u0111\u00e1nh gi\u00e1 m\u1ee5c H\u1ecfi&\u0110\u00e1p c\u1ee7a Yahoo c\u00f2n l\u01b0\u1ee3m th\u01b0\u1ee3m l\u1eafm (\u0111\u1ee7 lo\u1ea1i \u00f9m b\u00e0 l\u1eb1ng). Tuy nhi\u00ean, m\u00ecnh v\u1eabn th\u00edch tr\u1ea3 l\u1eddi c\u00e1c c\u00e2u h\u1ecfi th\u1eafc m\u1eafc m\u00e0 theo m\u00ecnh \u0111\u00e1nh gi\u00e1 l\u00e0 c\u00f3 th\u1eadt v\u00e0 \u0111\u00fang nhu c\u1ea7u (v\u00e0 c\u0169ng ch\u1ec9 loanh quanh c\u00e1c ch\u1ee7 \u0111\u1ec1 s\u1edf tr\u01b0\u1eddng c\u1ee7a m\u00ecnh :D).<\/p>\n

H\u00f4m nay, t\u00ecnh c\u1edd l\u01b0\u1ee3n l\u1edd th\u1ea5y m\u1ed9t b\u1ea1n \u0111\u1eb7t c\u00e2u h\u1ecfi<\/a> nh\u01b0 sau:<\/p>\n

C\u00e1c anh c\u00e1c ch\u1ecb gi\u00fap em v\u1edbi…<\/p>\n

T\u00ecnh h\u00ecnh l\u00e0:<\/p>\n

\u0110\u1eb7t h\u00e0m : =Day(Y2)<\/p>\n

K\u1ebft qu\u1ea3: =Day(Y2)<\/p>\n

trong khi n\u1ebfu \u0111\u00fang th\u00ec ph\u1ea3i l\u00e0: 14<\/p>\n

V\u00ec gi\u00e1 tr\u1ecb \u1edf \u00f4 Y2 l\u00e0 “14-2-14”<\/p>\n

Xin xem file \u0111\u00ednh k\u00e8m,
\nM\u1ecfi m\u00f2n ng\u00f3ng tr\u00f4ng. Ch\u00e2n th\u00e0nh c\u00e1m \u01a1n<\/p>\n

\"V\u00ed<\/a>
V\u00ed d\u1ee5 v\u1ec1 nh\u1eadp ki\u1ec3u ng\u00e0y trong Excel<\/figcaption><\/figure><\/blockquote>\n

T\u00f4i \u0111\u00e3 t\u1eebng \u0111\u01b0\u1ee3c h\u1ecfi nhi\u1ec1u c\u00e2u ki\u1ec3u n\u00e0y trong th\u1ef1c th\u1ebf. Nh\u1eefng ng\u01b0\u1eddi l\u00e0m vi\u1ec7c li\u00ean quan \u0111\u1ebfn con s\u1ed1, ng\u00e0y gi\u1edd m\u00e0 kh\u00f4ng c\u1ea9n th\u1eadn v\u1edbi ch\u00fang th\u00ec \u0111i t\u00f9 nh\u01b0 ch\u01a1i. \u0110\u1ec3 gi\u1ea3i quy\u1ebft v\u1ea5n \u0111\u1ec1 n\u00e0y, t\u00f4i ngh\u0129 ch\u00fang ta c\u1ea7n n\u1eafm r\u00f5 c\u00e1c nguy\u00ean t\u1eafc c\u01a1 b\u1ea3n nh\u1ea5t khi l\u00e0m vi\u1ec7c v\u1edbi d\u1eef li\u1ec7u ki\u1ec3u Ng\u00e0y gi\u1edd (DateTime). \u1ede \u0111\u00e2y, t\u00f4i n\u00eau ra m\u1ed9t s\u1ed1 tr\u01b0\u1eddng h\u1ee3p l\u1ed7i c\u01a1 b\u1ea3n m\u00e0 b\u1ea1n s\u1ebd c\u00f3 th\u1ec3 s\u1ebd m\u1eafc ph\u1ea3i, m\u00e0 \u0111\u00f3 c\u0169ng l\u00e0 c\u00e1c l\u1ed7i ph\u1ed5 bi\u1ebfn nh\u1ea5t.<\/p>\n

1. Ng\u00e0y 14-02-14, b\u1ea1n g\u00f5\/nh\u1eadp n\u00f3 nh\u01b0 th\u1ebf n\u00e0o?<\/strong><\/span>
\nN\u1ebfu m\u00e1y t\u00ednh c\u00e0i h\u1ec7 \u0111i\u1ec1u h\u00e0nh Windows th\u00ec ng\u1ea7m \u0111\u1ecbnh ng\u00e0y gi\u1edd tr\u00ean m\u00e1y \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng ng\u00e0y gi\u1edd<\/a> theo ki\u1ec3u Anh M\u1ef9. Th\u1ebf th\u00ec, n\u1ebfu b\u1ea1n ch\u01b0a thay \u0111\u1ed5i \u0111\u1ecbnh d\u1ea1ng ng\u00e0y trong Control Panel<\/strong> (m\u1ee5c Clock, Language and Region) th\u00ec b\u1ea1n ph\u1ea3i g\u00f5 theo \u0111\u00fang ki\u1ec3u Anh M\u1ef9. Th\u00f4ng th\u01b0\u1eddng, g\u00f5 th\u00e1ng tr\u01b0\u1edbc, \u0111\u1ebfn ng\u00e0y v\u00e0 n\u0103m. V\u00ed d\u1ee5 ng\u00e0y b\u1ea1n n\u00eau th\u00ec ph\u1ea3i g\u00f5 theo tr\u00ecnh t\u1ef1 02-14-14.<\/p>\n

N\u1ebfu b\u1ea1n g\u00f5 m\u1ed9t ng\u00e0y h\u1ee3p l\u1ec7 (1 <= ng\u00e0y <=31, 1 <= th\u00e1ng <= 12 \u00a0v\u00e0 >= 1904) th\u00ec ng\u00e0y (g\u1ecdi \u0111\u1ea7y \u0111\u1ee7 l\u00e0 ng\u00e0y th\u00e1ng n\u0103m) s\u1ebd t\u1ef1 \u0111\u1ed9ng canh l\u1ec1 b\u00ean ph\u1ea3i (\u0111\u00e2y l\u00e0 t\u00ednh n\u0103ng ng\u1ea7m \u0111\u1ecbnh b\u1eaft bu\u1ed9c c\u1ee7a Excel, tr\u1eeb khi tr\u01b0\u1edbc \u0111\u00f3 b\u1ea1n \u0111\u00e3 \u0111\u1ecbnh d\u1ea1ng canh l\u1ec1 b\u00ean tr\u00e1i hay \u1edf gi\u1eefa cho \u00f4). Ngh\u0129a l\u00e0, n\u1ebfu \u00f4 \u0111\u00f3 ch\u01b0a \u0111\u1ecbnh d\u1ea1ng g\u00ec c\u1ea3 m\u00e0 ng\u00e0y t\u1ef1 \u0111\u1ed9ng canh l\u1ec1 b\u00ean tr\u00e1i th\u00ec ng\u00e0y nh\u1eadp v\u00e0o kh\u00f4ng h\u1ee3p l\u1ec7 (v\u00ed d\u1ee5 nh\u1eadp 23-02-14 – b\u1edfi v\u00ec kh\u00f4ng c\u00f3 th\u00e1ng 23).<\/p>\n

R\u1ea5t v\u00e0 r\u1ea5t nhi\u1ec1u b\u1ea1n l\u00e0m Excel h\u1ea7u nh\u01b0 nh\u1eadp sai d\u1eef li\u1ec7u ng\u00e0y v\u00f4 c\u00f9ng nguy hi\u1ec3m. L\u00fd do c\u00f3 th\u1ec3 b\u1ea1n hi\u1ec3u kh\u00f4ng \u0111\u00fang, nh\u01b0 t\u00f4i gi\u1ea3i th\u00edch \u1edf tr\u00ean. C\u00f3 m\u1ed9t s\u1ed1 b\u1ea1n ng\u1ed9 nh\u1eadn khi nh\u1eadp d\u1eef li\u1ec7u ng\u00e0y. T\u00f4i l\u1ea5y v\u00ed d\u1ee5:\u00a0Nh\u1eadp ng\u00e0y 3-2-2014, n\u1ebfu \u0111\u00fang \u0111\u1ecbnh d\u1ea1ng ng\u00e0y Anh M\u1ef9 th\u00ec \u0111\u00f3 l\u00e0 ng\u00e0y 2 th\u00e1ng 3 n\u0103m 2014. Nh\u01b0ng n\u1ebfu b\u1ea1n c\u1ee9 nh\u1eadp theo ki\u1ec3u Vi\u1ec7t Nam l\u00e0 nh\u1eadp s\u1ed1 3 sau \u0111\u00f3 l\u00e0 2 v\u00e0 n\u0103m 2014, m\u00e1y t\u00ednh c\u0169ng s\u1ebd \u0111\u1ecbnh d\u1ea1ng \u0111\u1ec1u b\u00ean ph\u1ea3i (nh\u01b0 t\u00f4i gi\u1ea3i th\u00edch tr\u00ean) v\u00e0 m\u00e1y lu\u00f4n hi\u1ec3u b\u1ea1n \u0111ang nh\u1eadp ng\u00e0y 3 th\u00e1ng 2 n\u0103m 2014. Trong tr\u01b0\u1eddng h\u1ee3p ng\u00e0y c\u1ea3 ng\u00e0y 2\/3 v\u00e0 3\/2 \u0111\u1ec1u h\u1ee3p l\u1ec7 (v\u00ec th\u00e1ng <= 12 v\u00e0 ng\u00e0y <=31, ok).<\/p>\n

Do \u0111\u00f3, t\u00f4i \u0111o\u00e1n:<\/span>\u00a0<\/span>tr\u01b0\u1eddng h\u1ee3p \u1edf tr\u00ean b\u1ea1n nh\u1eadp sai khu\u00f4n d\u1ea1ng ng\u00e0y th\u00e1ng n\u0103m. V\u00e0 c\u00f3 th\u1ec3 m\u00e1y t\u00ednh hi\u1ec3u r\u1eb1ng \u0111\u00f3 l\u00e0 th\u00e1ng 14, ng\u00e0y 2 n\u00ean h\u00e0m DAY<\/strong> cho ra k\u1ebft qu\u1ea3 sai (#NA) ho\u1eb7c (#REF). C\u00e1ch kh\u1eafc ph\u1ee5c: Nh\u1eadp l\u1ea1i d\u1eef li\u1ec7u \u00f4 \u0111\u00f3.<\/p>\n

2. Sai \u0111\u1ecbnh d\u1ea1ng d\u1eef li\u1ec7u<\/strong><\/span>
\nC\u0169ng c\u00f3 th\u1ec3 b\u1ea1n \u0111\u1ecbnh d\u1ea1ng c\u1ed9t ng\u00e0y ki\u1ec3u V\u0103n b\u1ea3n\/K\u00ed t\u1ef1 (Text) l\u00e0m cho m\u00e1y t\u00ednh kh\u00f4ng hi\u1ec3u \u00f4 \u0111\u00f3 c\u00f3 d\u1eef li\u1ec7u ki\u1ec3u Ng\u00e0y gi\u1edd (DateTime). L\u01b0u \u00fd: Ng\u00e0y \u0111\u01b0\u1ee3c Excel hi\u1ec3u nh\u01b0 s\u1ed1 l\u00e0 m\u1ed9t s\u1ed1 (Number).<\/p>\n

3. Sai \u0111\u1ecbnh d\u1ea1ng d\u1eef li\u1ec7u c\u1ed9t ch\u1ee9a k\u1ebft qu\u1ea3<\/strong><\/span>
\nC\u0169ng c\u00f3 th\u1ec3 b\u1ea1n format c\u1ed9t Ng\u00e0y ki\u1ec3u Text n\u00ean n\u00f3 kh\u00f4ng nh\u1eadn k\u1ebft qu\u1ea3 h\u00e0m DAY (tr\u1ea3 v\u1ec1 m\u1ed9t s\u1ed1)<\/p>\n

P\/s: N\u00ean k\u00e9o r\u1ed9ng c\u1ed9t ng\u00e0y ra m\u1ed9t t\u00ed \u0111\u1ec3 xem d\u1eef li\u1ec7u canh \u0111\u1ec1u b\u00ean tr\u00e1i\/gi\u1eefa\/ph\u1ea3i. L\u00e0m nh\u01b0 th\u1ebf b\u1ea1n s\u1ebd d\u1ec5 d\u00e0ng nh\u1eadn bi\u1ebft m\u1ed9t ng\u00e0y b\u1ea1n nh\u1eadp v\u00e0o \u0111\u00fang hay sai. C\u00e1ch ki\u1ec3m tra nhanh trong Excel m\u00e0 kh\u00f4ng c\u1ea7n m\u1edf Control Panel nh\u01b0 sau:<\/p>\n

– Ch\u1ecdn m\u1ed9t \u00f4 b\u1ea5t k\u00ec g\u00f5: 20-2-2014<\/p>\n

– Ch\u1ecdn \u00f4 b\u1ea5t k\u00ec, nh\u1eadp h\u00e0m MONTH(\u00f4 ch\u1ee9a ng\u00e0y v\u1eeba nh\u1eadp \u1edf tr\u00ean). N\u1ebfu k\u1ebft qu\u1ea3 ra s\u1ed1 2 th\u00ec ng\u00e0y trong m\u00e1y c\u00e0i \u0111\u1eb7t theo ki\u1ec3u dd-MM-yyyy; ng\u01b0\u1ee3c l\u1ea1i s\u1ebd kh\u00f4ng ra k\u1ebft qu\u1ea3, th\u00ec b\u1ea1n s\u1ebd k\u1ebft lu\u1eadn ng\u00e0y trong m\u00e1y \u0111\u01b0\u1ee3c c\u00e0i \u0111\u1eb7t theo ki\u1ec3u MM-dd-yyyy.<\/p>","protected":false},"excerpt":{"rendered":"

T\u00f4i c\u0169ng th\u01b0\u1eddng tham gia tr\u1ea3 l\u1eddi c\u00e1c c\u00e2u h\u1ecfi tr\u00ean Yahoo! H\u1ecfi & \u0110\u00e1p. V\u1edbi kinh nghi\u1ec7m c\u1ee7a m\u00ecnh, \u0111\u00e3 v\u00e0 \u0111ang ch\u01a1i nhi\u1ec1u di\u1ec5n \u0111\u00e0n kh\u00f3 t\u00ednh nh\u01b0… <\/p>\n","protected":false},"author":2,"featured_media":1360,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[390,425],"tags":[576,426],"class_list":["post-1356","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-office-applications","category-spreadsheet","tag-spreadsheet","tag-datetime"],"_links":{"self":[{"href":"https:\/\/www.itersdesktop.com\/vi\/wp-json\/wp\/v2\/posts\/1356","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.itersdesktop.com\/vi\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.itersdesktop.com\/vi\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.itersdesktop.com\/vi\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.itersdesktop.com\/vi\/wp-json\/wp\/v2\/comments?post=1356"}],"version-history":[{"count":4,"href":"https:\/\/www.itersdesktop.com\/vi\/wp-json\/wp\/v2\/posts\/1356\/revisions"}],"predecessor-version":[{"id":1441,"href":"https:\/\/www.itersdesktop.com\/vi\/wp-json\/wp\/v2\/posts\/1356\/revisions\/1441"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.itersdesktop.com\/vi\/wp-json\/wp\/v2\/media\/1360"}],"wp:attachment":[{"href":"https:\/\/www.itersdesktop.com\/vi\/wp-json\/wp\/v2\/media?parent=1356"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.itersdesktop.com\/vi\/wp-json\/wp\/v2\/categories?post=1356"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.itersdesktop.com\/vi\/wp-json\/wp\/v2\/tags?post=1356"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}