{"id":3409,"date":"2024-12-03T02:22:37","date_gmt":"2024-12-03T02:22:37","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/03\/cach-sua-ngay-sai-trong-excel\/"},"modified":"2024-12-03T02:22:37","modified_gmt":"2024-12-03T02:22:37","slug":"cach-sua-ngay-sai-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/03\/cach-sua-ngay-sai-trong-excel\/","title":{"rendered":"C\u00e1ch s\u1eeda ng\u00e0y sai trong Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>B\u1ea1n nh\u1eadn \u0111\u01b0\u1ee3c file c\u1ee7a ng\u01b0\u1eddi kh\u00e1c l\u00e0m, nh\u1eadp ng\u00e0y th\u00e1ng n\u0103m kh\u00f4ng \u0111\u00fang quy chu\u1ea9n. V\u00e0 c\u1ea7n ph\u1ea3i s\u1eeda l\u1ea1i \u0111\u1ed3ng lo\u1ea1t, v\u1eady th\u00ec ch\u00fang ta ph\u1ea3i l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 <strong><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-xu-ly-loi-du-lieu-ngay-thang-trong-excel.html\" target=\"_blank\" rel=\"noopener noreferrer\">s\u1eeda l\u1ed7i ng\u00e0y sai<\/a><\/strong>?<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/07\/date-fix-substitute.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-19936\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/date-fix-substitute.jpg\" width=\"1070\" height=\"412\" title=\"date-fix-substitute\"><\/a><\/p>\n<p>Nh\u01b0 trong \u1ea3nh, ch\u00fang ta s\u1ebd th\u1ea5y \u00f4 A3, c\u00f3 d\u1ea1ng ng\u00e0y theo dd\/mm\/yyyy. Tuy nhi\u00ean, khi ki\u1ec3m tra l\u1ea1i l\u00e0 ng\u00e0y 12 th\u00e1ng 2, kh\u00f4ng ph\u1ea3i l\u00e0 ng\u00e0y 2 th\u00e1ng 12. \u1ede \u0111\u00e2y, m\u00ecnh mu\u1ed1n gi\u1edbi thi\u1ec7u v\u1edbi c\u00e1c b\u1ea1n c\u00f4ng d\u1ee5ng c\u1ee7a h\u00e0m SUBSTITUTE \u0111\u1ec3 l\u1ea5y ra d\u1eef li\u1ec7u ng\u00e0y v\u00e0 th\u00e1ng, c\u00f9ng v\u1edbi <a href=\"http:\/\/blog.ezworkapp.com\/cach-xac-dinh-ngay-thang-nam-bang-cac-ham-thoi-gian-trong-excel-co-bai-tap.html\" target=\"_blank\" rel=\"noopener noreferrer\">h\u00e0m DATE(N\u0103m, Th\u00e1ng, Ng\u00e0y) \u0111\u1ec3 chu\u1ea9n h\u00f3a d\u1eef li\u1ec7u ng\u00e0y<\/a>.<\/p>\n<p>V\u1edbi d\u1eef li\u1ec7u nh\u01b0 \u00f4 A2, ch\u00fang ta th\u1ea5y r\u00f5 r\u00e0ng \u0111\u00e2y l\u00e0 TEXT, v\u1edbi d\u1eef li\u1ec7u N\u0103m, th\u00ec th\u01b0\u1eddng l\u00e0 4 k\u00fd t\u1ef1 cu\u1ed1i, ch\u00fang ta \u00e1p d\u1ee5ng h\u00e0m RIGHT(A2,4) = 2018. V\u1eady l\u00e0 ta c\u00f3 N\u0103m, cho h\u00e0m DATE(N\u0103m, Th\u00e1ng, Ng\u00e0y).<\/p>\n<p>C\u00f2n d\u1eef li\u1ec7u Th\u00e1ng v\u00e0 Ng\u00e0y, th\u00ec l\u00e0m th\u1ebf n\u00e0o? Ch\u00fang ta th\u1ea5y c\u00f3 d\u1ea5u \u201c\/\u201d ph\u00e2n chia,\u00a0v\u00e0 theo \u0111\u00f3 ta c\u00f3 c\u00e1c c\u00e1ch sau:<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_61 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Xem nhanh<\/p>\n<p><label for=\"ez-toc-cssicon-toggle-item-674e6b6cbcc6c\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><\/span><\/span><\/label><\/p>\n<nav>\n<ul class=\"ez-toc-list ez-toc-list-level-1 \">\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/cach-sua-ngay-sai-trong-excel.html#Kieu_ddmmyyyy_%E2%80%93_%E2%80%9C00000000%E2%80%9D\" title=\"Ki\u1ec3u dd\/mm\/yyyy \u2013 \u201c00\/00\/0000\u201d:\">Ki\u1ec3u dd\/mm\/yyyy \u2013 \u201c00\/00\/0000\u201d:<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/cach-sua-ngay-sai-trong-excel.html#Kieu_%E2%80%9Cdmyyyy%E2%80%9D_%E2%80%93_%E2%80%9C000000%E2%80%9D\" title=\"Ki\u1ec3u \u201cd\/m\/yyyy\u201d \u2013 \u201c0\/0\/0000\u201d:\">Ki\u1ec3u \u201cd\/m\/yyyy\u201d \u2013 \u201c0\/0\/0000\u201d:<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Kieu_ddmmyyyy_%E2%80%93_%E2%80%9C00000000%E2%80%9D\"><\/span>Ki\u1ec3u dd\/mm\/yyyy \u2013 \u201c00\/00\/0000\u201d:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>V\u00ed d\u1ee5:\u00a0\u201c02\/12\/2018\u201d hay \u201c01\/02\/2018\u201d<\/p>\n<p>\u0110i\u1ec1u n\u00e0y s\u1ebd v\u00f4 c\u00f9ng d\u1ec5 d\u00e0ng h\u01a1n khi ch\u00fang ta c\u00f3 ng\u00e0y th\u00e1ng n\u0103m ki\u1ec3u 2 ch\u1eef s\u1ed1, l\u00fac n\u00e0y ch\u1ec9 c\u1ea7n LEFT(\u201c01\/02\/2018\u201d, 2)=01, th\u00ec ch\u00fang ta \u0111\u00e3 c\u00f3 Ng\u00e0y. C\u00f2n th\u00e1ng th\u00ec MID(\u201c02\/12\/2018\u201d,4,2)=12.<\/p>\n<div class=\"code-block code-block-3\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<p>D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, nh\u01b0ng k\u1ef9 n\u0103ng Excel v\u1eabn c\u1ef1c k\u1ef3 quan tr\u1ecdng v\u1edbi k\u1ebf to\u00e1n, b\u1ea1n \u0111\u00e3 v\u1eefng Excel ch\u01b0a? H\u00e3y \u0111\u1ec3 t\u00f4i gi\u00fap b\u1ea1n, \u0111\u0103ng k\u00fd kho\u00e1 h\u1ecdc Excel:<\/p>\n<p><a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-261.gif\" title=\"ex101_92019-nho-261\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-261.gif\" title=\"ex101_92019-nho-261\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Kieu_%E2%80%9Cdmyyyy%E2%80%9D_%E2%80%93_%E2%80%9C000000%E2%80%9D\"><\/span>Ki\u1ec3u \u201cd\/m\/yyyy\u201d \u2013 \u201c0\/0\/0000\u201d:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>V\u00ed d\u1ee5: \u201c12\/1\/2018\u201d hay \u201c14\/2\/2018\u201d, l\u00fac n\u00e0y th\u00ec s\u1ed1 k\u00fd t\u1ef1 kh\u00f4ng c\u1ed1 \u0111\u1ecbnh, l\u00e0 2, v\u00e0 v\u1ecb tr\u00ed c\u0169ng thay \u0111\u1ed5i, n\u00ean ch\u00fang ta s\u1ebd \u00e1p d\u1ee5ng h\u00e0m SUBSTITUTE \u0111\u1ec3 l\u1ea5y ra d\u1eef li\u1ec7u Ng\u00e0y v\u00e0 Th\u00e1ng. Theo \u0111\u00f3, m\u00ecnh c\u00f3 c\u00f4ng th\u1ee9c:<br \/>\nTh\u00e1ng: =TRIM(MID(SUBSTITUTE(A2,\u201d\/\u201d,REPT(\u201d \u201c,9)),1*9+1,9))<\/p>\n<p>Ng\u00e0y:=TRIM(MID(SUBSTITUTE(A2,\u201d\/\u201d,REPT(\u201d \u201c,9)),0*9+1,9)))<\/p>\n<p>V\u00ec sao c\u00f3 s\u1ed1 9? B\u1ea1n \u0111\u00e3 \u0111\u1ecdc truy\u1ec7n <strong><a href=\"http:\/\/blog.ezworkapp.com\/substitute-va-40-ten-cuop.html\" target=\"_blank\" rel=\"noopener noreferrer\">SUBSTITUTE v\u00e0 40 t\u00ean c\u01b0\u1edbp<\/a> ch\u01b0a<\/strong>? ;). V\u00ec sao l\u1ea1i l\u00e0 <strong>0*9+1<\/strong>, k\u1ebft qu\u1ea3 r\u00f5 r\u00e0ng l\u00e0 0+1 r\u1ed3i m\u00e0. M\u00ecnh c\u1ed1 t\u00ecnh vi\u1ebft \u0111\u1ec3 c\u00e1c b\u1ea1n c\u00f3 th\u1ec3 th\u1ea5y r\u00f5 h\u01a1n, khi ch\u00fang ta l\u1ea5y ra t\u01b0\u01a1ng \u1ee9ng v\u1edbi th\u00e1ng <strong>1*9+1<\/strong>. 0 \u1edf \u0111\u00e2y l\u00e0 v\u1ecb tr\u00ed \u0111\u1ea7u ti\u00ean, 1 l\u00e0 v\u1ecb tr\u00ed th\u1ee9 2, t\u01b0\u01a1ng t\u1ef1 n\u1ebfu c\u1ea7n l\u1ea5y N\u0103m, th\u00ec ta c\u00f3 2*9.<\/p>\n<p>Cu\u1ed1i c\u00f9ng ch\u00fang ta gh\u00e9p 3 d\u1eef li\u1ec7u tr\u00ean v\u1edbi h\u00e0m <strong><a href=\"http:\/\/blog.ezworkapp.com\/cach-xac-dinh-ngay-thang-nam-bang-cac-ham-thoi-gian-trong-excel-co-bai-tap.html#Ham_DATE_de_tao_ra_1_gia_tri_thoi_gian_tu_viec_ghep_cac_doi_tuong_Nam_ThangNgay\" target=\"_blank\" rel=\"noopener noreferrer\">DATE<\/a><\/strong>, th\u00ec ta c\u00f3 k\u1ebft qu\u1ea3 nh\u01b0 mong mu\u1ed1n r\u1ed3i ph\u1ea3i v\u1eady?<\/p>\n<p>[alert-success]=IFERROR(<br \/>\nDATE(RIGHT(A2,4),TRIM(MID(SUBSTITUTE(A2,\u201d\/\u201d,REPT(\u201d \u201c,9)),1*9+1,9)),TRIM(MID(SUBSTITUTE(A2,\u201d\/\u201d,REPT(\u201d \u201c,9)),0*9+1,9))),<br \/>\nDATE(YEAR(A2),DAY(A2),MONTH(A2)))[\/alert-success]<\/p>\n<p>V\u1eady t\u1ea1i sao c\u00f2n IFERROR trong c\u00f4ng th\u1ee9c tr\u00ean? L\u00e0 v\u00ec ch\u00fang ta s\u1ebd g\u1eb7p nh\u1eefng d\u1eef li\u1ec7u l\u00e0 Ng\u00e0y, nh\u01b0ng \u0111\u01b0\u1ee3c hi\u1ec3u ng\u01b0\u1ee3c l\u1ea1i mm\/dd. V\u1edbi nh\u1eefng d\u1eef li\u1ec7u l\u00e0 Ng\u00e0y, ch\u00fang ta c\u00f3 c\u00e1c h\u00e0m <strong><a href=\"http:\/\/blog.ezworkapp.com\/cach-xac-dinh-ngay-thang-nam-bang-cac-ham-thoi-gian-trong-excel-co-bai-tap.html#HamDAY_de_lay_gia_triNgaytu_mot_doi_tuongthoi_gian\" target=\"_blank\" rel=\"noopener noreferrer\">DAY<\/a> <\/strong>\u0111\u1ec3 l\u1ea5y ng\u00e0y, <a href=\"http:\/\/blog.ezworkapp.com\/cach-xac-dinh-ngay-thang-nam-bang-cac-ham-thoi-gian-trong-excel-co-bai-tap.html#HamMONTH_de_lay_gia_triThangtu_mot_doi_tuongthoi_gian\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>MONTH<\/strong> <\/a>\u0111\u1ec3 l\u1ea5y th\u00e1ng, v\u00e0 <a href=\"http:\/\/blog.ezworkapp.com\/cach-xac-dinh-ngay-thang-nam-bang-cac-ham-thoi-gian-trong-excel-co-bai-tap.html#Ham_YEARde_lay_gia_tri_Nam_tu_mot_doi_tuongthoi_gian\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>YEAR<\/strong> <\/a>\u0111\u1ec3 l\u1ea5y n\u0103m. \u0110\u01a1n gi\u1ea3n h\u01a1n r\u1ed3i ph\u1ea3i kh\u00f4ng n\u00e0o?<\/p>\n<p>Update: 15\/08\/2018.<\/p>\n<p>Ngo\u00e0i c\u00e1c c\u00e1ch d\u00f9ng h\u00e0m, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng c\u1ee5, TEXT TO COLUMN \u0111\u1ec3 s\u1eeda l\u1ea1i ng\u00e0y th\u00e1ng nhanh ch\u00f3ng:<br \/>\n<a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/07\/text-to-column-fixdate.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-21276\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/text-to-column-fixdate.jpg\" width=\"1208\" height=\"631\" title=\"text-to-column-fixdate\"><\/a><\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<p>D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, nh\u01b0ng k\u1ef9 n\u0103ng Excel v\u1eabn c\u1ef1c k\u1ef3 quan tr\u1ecdng v\u1edbi k\u1ebf to\u00e1n, b\u1ea1n \u0111\u00e3 v\u1eefng Excel ch\u01b0a? H\u00e3y \u0111\u1ec3 t\u00f4i gi\u00fap b\u1ea1n, \u0111\u0103ng k\u00fd kho\u00e1 h\u1ecdc Excel:<\/p>\n<p><a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-261.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":3410,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3409","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-nang-cao"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/3409","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/comments?post=3409"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/3409\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/3410"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=3409"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=3409"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=3409"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}