{"id":7066,"date":"2024-12-21T16:50:08","date_gmt":"2024-12-21T16:50:08","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/lich-am-duong-van-nien-trong-excel-moi-nhat-khong-dung-vba\/"},"modified":"2024-12-21T16:50:08","modified_gmt":"2024-12-21T16:50:08","slug":"lich-am-duong-van-nien-trong-excel-moi-nhat-khong-dung-vba","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/lich-am-duong-van-nien-trong-excel-moi-nhat-khong-dung-vba\/","title":{"rendered":"L\u1ecbch \u00c2m D\u01b0\u01a1ng V\u1ea1n Ni\u00ean trong Excel m\u1edbi nh\u1ea5t kh\u00f4ng d\u00f9ng VBA"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><em>Ch\u1eafc b\u1ea1n s\u1ebd r\u1ea5t b\u1ea5t ng\u1edd khi bi\u1ebft r\u1eb1ng, ch\u00fang ta ho\u00e0n to\u00e0n c\u00f3 th\u1ec3 tra c\u1ee9u<strong> l\u1ecbch \u00e2m d\u01b0\u01a1ng trong Excel<\/strong> (c\u1ea3 l\u1ecbch \u00e2m v\u00e0 l\u1ecbch d\u01b0\u01a1ng) m\u00e0 kh\u00f4ng c\u1ea7n b\u1ea5t k\u1ef3 add-in hay VBA n\u00e0o c\u1ea3, v\u00e0 c\u00f3 th\u1ec3 d\u00f9ng v\u1edbi Office 2007 tr\u1edf \u0111i. H\u00e3y c\u00f9ng <a href=\"http:\/\/blog.ezworkapp.com\"><strong>H\u1ecdc Excel Online<\/strong><\/a> kh\u00e1m ph\u00e1 ngay th\u00f4i n\u00e0o<\/em><\/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-6766f1bf7c0ad\" 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-2\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/lich-am-duong-van-nien-trong-excel-moi-nhat-khong-dung-vba.html#Cong_thuc_tra_cuu_lich_am_trong_Excel_tu_ngay_cho_truoc\" title=\"C\u00f4ng th\u1ee9c tra c\u1ee9u l\u1ecbch \u00e2m trong Excel t\u1eeb ng\u00e0y cho tr\u01b0\u1edbc\">C\u00f4ng th\u1ee9c tra c\u1ee9u l\u1ecbch \u00e2m trong Excel t\u1eeb ng\u00e0y cho tr\u01b0\u1edbc<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/lich-am-duong-van-nien-trong-excel-moi-nhat-khong-dung-vba.html#Ung_dung_ham_TEXT_trong_tra_cuu_ngay_am_lich_trong_Excel\" title=\"\u1ee8ng d\u1ee5ng h\u00e0m TEXT trong tra c\u1ee9u ng\u00e0y \u00e2m l\u1ecbch trong Excel\">\u1ee8ng d\u1ee5ng h\u00e0m TEXT trong tra c\u1ee9u ng\u00e0y \u00e2m l\u1ecbch trong Excel<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/lich-am-duong-van-nien-trong-excel-moi-nhat-khong-dung-vba.html#Lich_Van_Nien_trong_Excel_hoan_toan_khong_dung_VBA\" title=\"L\u1ecbch V\u1ea1n Ni\u00ean trong Excel ho\u00e0n to\u00e0n kh\u00f4ng d\u00f9ng VBA\">L\u1ecbch V\u1ea1n Ni\u00ean trong Excel ho\u00e0n to\u00e0n kh\u00f4ng d\u00f9ng VBA<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/lich-am-duong-van-nien-trong-excel-moi-nhat-khong-dung-vba.html#Tai_ve_file_lich_van_nien_trong_Excel_moi_nhat\" title=\"T\u1ea3i v\u1ec1 file l\u1ecbch v\u1ea1n ni\u00ean trong Excel m\u1edbi nh\u1ea5t:\">T\u1ea3i v\u1ec1 file l\u1ecbch v\u1ea1n ni\u00ean trong Excel m\u1edbi nh\u1ea5t:<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/lich-am-duong-van-nien-trong-excel-moi-nhat-khong-dung-vba.html#Huong_dan_tuy_chinh_mau_sac_de_in_an_mau\" title=\"H\u01b0\u1edbng d\u1eabn t\u00f9y ch\u1ec9nh m\u00e0u s\u1eafc \u0111\u1ec3 in \u1ea5n m\u00e0u.\">H\u01b0\u1edbng d\u1eabn t\u00f9y ch\u1ec9nh m\u00e0u s\u1eafc \u0111\u1ec3 in \u1ea5n m\u00e0u.<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Cong_thuc_tra_cuu_lich_am_trong_Excel_tu_ngay_cho_truoc\"><\/span>C\u00f4ng th\u1ee9c tra c\u1ee9u l\u1ecbch \u00e2m trong Excel t\u1eeb ng\u00e0y cho tr\u01b0\u1edbc<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>\u0110\u00f3 ch\u00ednh l\u00e0 h\u00e0m TEXT. Ta s\u1ebd c\u00f3 c\u00f4ng th\u1ee9c nh\u01b0 sau, v\u00ed d\u1ee5 \u00f4 A1 ch\u1ee9a ng\u00e0y th\u00e1ng n\u0103m.<\/p>\n<p><code class=\"excel-formula\">=TEXT(A1,\"[$-0011042A]dd-mm\")<\/code><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2020\/03\/cong-thuc-tra-cuu-lich-am-trong-Excel.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-31732 size-full\" title=\"lich-trong-excel-1\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cong-thuc-tra-cuu-lich-am-trong-excel.png\" alt=\"lich-trong-excel-1\" width=\"661\" height=\"209\"><\/a><\/p>\n<p>Theo \u0111\u00f3 ta c\u00f3, \u201c00\u201d l\u00e0 ph\u1ee5 thu\u1ed9c thi\u1ebft l\u1eadp m\u1eb7c \u0111\u1ecbnh trong System, \u201c11\u201d l\u00e0 l\u1ecbch \u00e2m, \u201c042A\u201d l\u00e0 m\u00e3 qu\u1ed1c gia Vi\u1ec7t Nam.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ung_dung_ham_TEXT_trong_tra_cuu_ngay_am_lich_trong_Excel\"><\/span>\u1ee8ng d\u1ee5ng h\u00e0m TEXT trong tra c\u1ee9u ng\u00e0y \u00e2m l\u1ecbch trong Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Ta c\u00f3 h\u00e0m TEXT nh\u01b0 tr\u00ean, v\u1eady b\u1ea1n c\u00f3 th\u1ec3 bi\u1ebft ng\u00e0y r\u1eb1m ho\u1eb7c m\u00f9ng 1 c\u1ee7a th\u00e1ng \u0111\u00f3 s\u1ebd r\u01a1i v\u00e0o ng\u00e0y n\u00e0o kh\u00f4ng?<\/p>\n<p>V\u1edbi B2 ch\u1ee9a ng\u00e0y c\u1ea7n tra c\u1ee9u, ta c\u00f3:<\/p>\n<div class=\"code-block code-block-2\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-282.gif\" title=\"vba101_92019-nho-282\"><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-636.gif\" title=\"ex101_92019-nho-636\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>M\u00f9ng 1: <code>=B2-TEXT(B2,\"[$-0011042A]dd\")+1<\/code><\/p>\n<p>R\u1eb1m: <code>=B2-TEXT(B2,\"[$-0011042A]dd\")+15<\/code><\/p>\n<p>\u0110\u1ec3 t\u00ednh th\u00e1ng n\u00e0y c\u00f3 29 hay 30 ng\u00e0y? B\u1ea1n s\u1ebd t\u00ednh nh\u01b0 th\u1ebf n\u00e0o?<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2020\/03\/cach-tinh-ngay-ram-am-lich-trong-Excel.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-31745 size-full\" title=\"lich-trong-excel-2\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cach-tinh-ngay-ram-am-lich-trong-excel.png\" alt=\"lich-trong-excel-2\" width=\"811\" height=\"206\"><\/a><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Lich_Van_Nien_trong_Excel_hoan_toan_khong_dung_VBA\"><\/span>L\u1ecbch V\u1ea1n Ni\u00ean trong Excel ho\u00e0n to\u00e0n kh\u00f4ng d\u00f9ng VBA<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>T\u1eeb c\u00f4ng th\u1ee9c tr\u00ean, ta ho\u00e0n to\u00e0n c\u00f3 th\u1ec3 x\u00e2y d\u1ef1ng b\u1ed9 l\u1ecbch t\u01b0\u01a1ng \u1ee9ng. Tuy nhi\u00ean b\u1ea1n s\u1ebd th\u1ea5y ng\u00e0y \u00e2m l\u1ecbch tr\u1ea3 v\u1ec1 n\u1ebfu <a href=\"http:\/\/blog.ezworkapp.com\/nam-nhuan-leap-year-va-nhung-dieu-thu-vi.html\"><strong>n\u0103m nhu\u1eadn<\/strong><\/a>, ch\u00fang ta s\u1ebd c\u00f3 13 th\u00e1ng. V\u00e0 vi\u1ec7c c\u1ee7a ch\u00fang ta l\u00e0 t\u00ecm ra th\u00e1ng nhu\u1eadn trong n\u0103m nhu\u1eadn \u0111\u00f3. Theo thu\u1eadt to\u00e1n c\u1ee7a H\u1ed3 Ng\u1ecdc \u0110\u1ee9c, ta c\u00f3 th\u1ec3 x\u00e2y d\u1ef1ng b\u1ea3ng danh s\u00e1ch c\u00e1c ng\u00e0y \u0111\u1ea7u th\u00e1ng trong n\u0103m, theo \u0111\u00f3 ki\u1ec3m tra xem th\u00e1ng n\u00e0o b\u1ecb tr\u00f9ng l\u1eb7p l\u1ea1i, th\u00ec n\u00f3 ch\u00ednh l\u00e0 th\u00e1ng nhu\u1eadn.<\/p>\n<p>\u0110\u1ec3 x\u00e1c \u0111\u1ecbnh n\u0103m nhu\u1eadn \u00c2m L\u1ecbch, ch\u00fang ta l\u1ea5y n\u0103m D\u01b0\u01a1ng l\u1ecbch chia cho 19, n\u1ebfu ph\u00e9p chia c\u00f3 s\u1ed1 d\u01b0 ra l\u00e0: <strong>0, 3, 6, 9, 11, 14, 17<\/strong>\u00a0th\u00ec n\u0103m \u0111\u00f3 l\u00e0 n\u0103m nhu\u1eadn.<\/p>\n<p>B\u1ea1n c\u00f3 th\u1ec3 tham kh\u1ea3o file sau, v\u1edbi t\u00f9y ch\u1ec9nh ch\u1ecdn \u0111\u01b0\u1ee3c Ng\u00f4n ng\u1eef Anh\/Vi\u1ec7t. B\u1ea1n s\u1ebd h\u1ecfi t\u1ea1i sao m\u00ecnh l\u00e0m \u0111\u01b0\u1ee3c c\u1ea3 2 ng\u00f4n ng\u1eef, b\u00ed m\u1eadt n\u1eb1m \u1edf Custom Format k\u1ebft h\u1ee3p Conditional Formatting.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Tai_ve_file_lich_van_nien_trong_Excel_moi_nhat\"><\/span>T\u1ea3i v\u1ec1 file l\u1ecbch v\u1ea1n ni\u00ean trong Excel m\u1edbi nh\u1ea5t:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<ul>\n<li><strong>T\u1ea3i v\u1ec1 File Excel L\u1ecbch \u00e2m d\u01b0\u01a1ng, l\u1ecbch v\u1ea1n ni\u00ean qua Google Drive<\/strong>:\u00a0<a href=\"http:\/\/bit.ly\/HEO_LichAmDuong\" target=\"_blank\" rel=\"noopener noreferrer\">http:\/\/bit.ly\/HEO_LichAmDuong<\/a><\/li>\n<\/ul>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2020\/03\/lich-van-nien-am-duong-trong-Excel.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-31733 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/lich-van-nien-am-duong-trong-excel.png\" alt=\"lich-trong-excel-3\" width=\"1201\" height=\"477\" title=\"lich-van-nien-am-duong-trong-excel\"><\/a><\/p>\n<p>Tham kh\u1ea3o th\u00eam:<\/p>\n<ul>\n<li><strong>T\u1ea3i v\u1ec1 file <a href=\"http:\/\/blog.ezworkapp.com\/lich-van-nien-trong-excel-lich-duong-trong-file-excel.html\">L\u1ecbch v\u1ea1n ni\u00ean<\/a>, L\u1ecbch \u00e2m d\u01b0\u01a1ng trong Excel d\u00f9ng VBA.<\/strong><\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Huong_dan_tuy_chinh_mau_sac_de_in_an_mau\"><\/span>H\u01b0\u1edbng d\u1eabn t\u00f9y ch\u1ec9nh m\u00e0u s\u1eafc \u0111\u1ec3 in \u1ea5n m\u00e0u.<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>B\u1ea1n ho\u00e0n to\u00e0n c\u00f3 th\u1ec3 t\u00f9y ch\u1ec9nh l\u1ea1i m\u00e0u s\u1eafc theo \u00fd mu\u1ed1n trong ph\u1ea7n Page Layout, v\u00e0 ch\u1ecdn l\u1ea1i Themes t\u01b0\u01a1ng \u1ee9ng v\u1edbi \u00fd th\u00edch c\u1ee7a b\u1ea1n, ngo\u00e0i ra ph\u1ea7n Colors \u0111\u1ec3 ch\u1ecdn l\u1ea1i m\u00e0u s\u1eafc, ho\u1eb7c Fonts ch\u1eef \u0111\u1ecbnh d\u1ea1ng.<\/p>\n<p>Ngo\u00e0i ra b\u1ea1n c\u00f3 th\u1ec3 \u0111i\u1ec1n c\u00e1c th\u00f4ng tin v\u1ec1 c\u00f4ng ty c\u1ee7a b\u1ea1n, nh\u01b0 S\u1ed1 \u0111i\u1ec7n tho\u1ea1i, t\u00ean cty, m\u00e3 s\u1ed1 thu\u1ebf, \u0111\u1ecba ch\u1ec9\u2026<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2020\/03\/tuy-chinh-mau-sac-file-lich-van-nien.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-31734 size-full\" title=\"lich-trong-excel-4\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tuy-chinh-mau-sac-file-lich-van-nien.jpg\" alt=\"lich-trong-excel-4\" width=\"1217\" height=\"672\"><\/a><\/p>\n<p><em>V\u00e0 \u0111\u00f3 l\u00e0 c\u00e1ch tra c\u1ee9u<strong> l\u1ecbch \u00e2m d\u01b0\u01a1ng<\/strong> trong Excel. C\u00f3 th\u1ec3 t\u1ea5t c\u1ea3 nh\u1eefng th\u00f4ng tin m\u00e0 b\u1ea1n \u0111ang t\u00ecm ki\u1ebfm \u0111\u1ec1u c\u00f3 trong<strong> <a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-excel-tu-co-ban-toi-nang-cao-danh-cho-dan-van-phong-2010-20132016\">kh\u00f3a h\u1ecdc Excel<\/a> <\/strong>c\u1ee7a ch\u00fang t\u00f4i. H\u00e3y \u0111\u0103ng k\u00ed ngay \u0111\u1ec3 nh\u1eadn \u0111\u01b0\u1ee3c nhi\u1ec1u \u01b0u \u0111\u00e3i nh\u1ea5t.<\/em><\/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<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-282.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":7067,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-7066","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/7066","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=7066"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/7066\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/7067"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=7066"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=7066"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=7066"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}