Khi tính toán ngày trong Excel, DATE là hàm cơ bản nhất. Như bạn có thể biết, Excel không lưu trữ ngày, tháng dưới định dạng ngày. Thay vào đó, Microsoft Excel lưu trữ ngày như một dãy số và đây là chính là nguyên ngân chính gây nhầm lẫn. Hãy cùng xem Học Excel Online sẽ mang đến những gì với Hàm Date vào ngày hôm nay nhé! Show Cú pháp hàm DATENhững gì hàm DATE làm là trả về kết quả số cho một ngày cụ thể. Nó có công thức sau: DATE(year, month, day) Year – chỉ năm. Excel diễn giải đối số năm theo hệ thống ngày được thiết lập trên máy tính của bạn. Theo mặc định, Microsoft Excel cho Windows sử dụng hệ thống 1900. Trong hệ thống ngày tháng này:
Mẹo. Để tránh nhầm lẫn, luôn luôn ghi bốn chữ số của năm. Ví dụ: nếu bạn nhập “01” hoặc “1” vào đối số năm, hàm DATE sẽ trả về kết quả năm 1901. Month – một số nguyên đại diện cho tháng trong năm, từ 1 (January) đến 12 (December).
Day – một số nguyên tương ứng với ngày trong tháng, từ 1 đến 31.
Cũng như tháng, đối số ngày có thể được cung cấp như một số dương và âm, và Excel tính giá trị của nó dựa trên các nguyên tắc tương tự như mô tả ở trên. Mẹo. Lúc đầu, việc cung cấp giá trị âm trong đối số tháng hoặc ngày của hàm DATE có vẻ như vô lý, nhưng trên thực tế nó có thể trở nên khá hữu ích, ví dụ trong công thức phức tạp chuyển đổi tuần. Hàm DATE có sẵn trong tất cả các phiên bản của Excel 2016, Excel 2013, Excel 2010, Excel 2007, 2003, XP, 2000 cũng như trong Excel 2016.
Ví dụ hàm DATEHãy bắt đầu từ những cái đơn giản nhất. Đây là cách sử dụng rõ ràng nhất của hàm DATE trong Excel: = DATE (2015, 5, 20) – trả về kết quả ngày 20 tháng 5 năm 2015. Thay vì chỉ định các giá trị đại diện cho năm, tháng và ngày trực tiếp trong một công thức, bạn có thể nhận được một số hoặc tất cả các đối số như là kết quả của các hàmkhác: =DATE(YEAR(TODAY()), 1, 1) – cho kết quả ngày đầu tiên của năm hiện tại. =DATE(YEAR(TODAY()), MONTH(TODAY(), 1)– cho kết quả ngày đầu tiên của tháng hiện tại trong năm hiện tại. Ví dụ 2. HàmDATE để trả lại một ngày dựa trên các giá trị trong các ô khác Hàm DATE rất hữu ích cho việc tính các ngày mà các giá trị năm, tháng và ngày được lấy từ ô khác. Ví dụ: = DATE (A2, A3, A4) – trả về kết quả ngày, tháng , năm từ ô A2, A3 và A4. Ví dụ 3. Hàm DATE chuyển đổi một chuỗi văn bản thành một ngày Khi các ngày được lưu trữ ở định dạng mà Microsoft Excel không nhận ra, ví dụ DDMMYYYY. Trong trường hợp này, bạn có thể sử dụng DATE với các hàm khác để chuyển đổi một ngày được lưu trữ như là một chuỗi văn bản thành ngày: = DATE (RIGHT (A2,4), MID (A2,3,2), LEFT (A2,2)) Ví dụ 4. Cộng và trừ ngày trong Excel Như đã đề cập, Microsoft Excel lưu trữ các ngày như là một chuỗi số và hoạt động trên những con số trong công thức và tính toán. Đó là lý do tại sao khi bạn muốn cộng hoặc trừ một số ngày vào / từ một ngày nhất định, bạn cần phải chuyển đổi ngày đó sang số thứ tự đầu tiên bằng cách sử dụng hàm DATE. Ví dụ: Thêm ngày vào một ngày: = DATE (2015, 5, 20) + 15 Công thức cộng thêm 15 ngày cho ngày 20 tháng 5 năm 2015 và trả về kết quả ngày 4 tháng 6 năm 2015. Trừ ngày kể từ ngày: = DATE (2015, 5, 20) – 15 Kết quả của công thức trên là ngày 5 tháng 5 năm 2015, tức là ngày 20 tháng 5 năm 2015 trừ đi 15 ngày. Để trừ một ngày từ ngày hôm nay: = TODAY () – NGÀY (2015,5,20) Công thức tính số ngày nằm giữa ngày hiện tại và một ngày khác mà bạn chỉ định. Nếu bạn cộng hoặc trừ hai ngày tháng được lưu trữ trong 2 ô, thì công thức đơn giản là = A1 + B1 hoặc A1-B1. Xem thêm: Định dạng ngày tháng trong Excel Sử dụng hàm DATE trong định dạng có điều kiệnTrong trường hợp bạn không chỉ tính toán mà còn đánh dấu ngày tháng trong bảng tính Excel, sau đó tạo các quy tắc định dạng có điều kiện dựa trên hàm DATE. Giả sử bạn có một danh sách các ngày trong cột A và bạn muốn tô màu cam ngày trước ngày 1 tháng 5 năm 2015 và xanh lá cây những ngày xảy ra sau ngày 31 tháng 5 năm 2015. Công thức DATE bạn muốn là như sau: Màu cam: = $A2 <NGÀY (2015, 5, 1) – tô màu ngày trước 1 tháng 5 năm 2015 Màu xanh lá cây: = $A2> NGÀY (2015, 5, 31) – tô màu các ngày lớn hơn 31 tháng 5 năm 2015. Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online. Xem thêm: Cách trừ ngày tháng năm trong Excel đơn giản Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table… Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học Excel cơ bản đến chuyên gia dành cho người đi làm Trong bài viết này, Học Excel Online sẽ hướng dẫn cách cộng trừ thời gian trong Excel, cộng trừ ngày, cộng tháng trong excel; chẳng hạn như trừ hai ngày, thêm ngày, tuần, tháng và năm cho một ngày… giúp thuận tính cho công việc cũng như trong học tập. Làm thếnào để trừ ngày trong Excel:Giả sử bạn có hai ngày trong ô A2 và B2, và bây giờ bạn muốn trừ chúng để biết số ngày nằm giữa hai ngày này. Kết quả có thể đạt được bằng nhiều cách. Ví dụ 1. Trừ một ngày trực tiếp Như bạn có thể biết, Microsoft Excel lưu trữ mỗi ngày như một số serial độc đáo bắt đầu bằng 1 đại diện cho 1 tháng 1 năm 1900. Vì vậy, khi bạn trừ chúng, một phép tính số học bình thường xảy ra: = B2-A2 Ví dụ 2. Trừ ngày sử dụng hàm DATEDIF Nếu công thức trên có vẻ quá đơn giản, bạn có thể đạt được cùng một kết quả bằng cách sử dụng hàm DATEDIF của Excel:
= DATEDIF (A2, B2, “d”) Hình sau đây cho thấy rằng cả hai phép tính đều trả về kết quả giống hệt nhau, ngoại trừ dòng 4 trong đó hàm DATEDIF trả về lỗi #NUM. Hãy tìm ra lý do tại sao điều đó xảy ra. Khi bạn trừ một ngày nhỏ hơn (1 tháng 5 năm 2015) cho một ngày lớn hơn (6 tháng 5 năm 2015), kết quả sẽ trả về số âm (-5). Tuy nhiên, cú pháp của hàm DATEDIF không cho phép ngày bắt đầu lớn hơn ngày kết thúc và do đó nó trả về lỗi. Xem thêm: Cách tính tuổi từ ngày sinh trong Excel Ví dụ 3. Trừ một ngày từ ngày hiện tại Để trừ một ngày từ ngày hôm nay, bạn có thể sử dụng một trong hai công thức trên. Chỉ cần sử dụng chức năng TODAY () thay vì ngày: = TODAY () – A2 hoặc là = DATEDIF (A2, TODAY (), “d”) Giống như trong ví dụ trước, cả hai công thức đều làm việc tốt khi ngày hôm nay lớn hơn ngày bạn trừ đi, nếu không DATEDIF sẽ báo lỗi: Ví dụ 4. Trừ ngày sử dụng hàm DATE Nếu bạn muốn lấy ngày của công thức cung cấp, nhập ngày, sử dụng hàm DATE (năm, tháng, ngày) và trừ đi một ngày khác.Ví dụ: công thức sau trừ ngày 20 tháng 5 năm 2015 cho ngày 15 tháng 5 năm 2015 và trả lại kết quả 5 ngày:= DATE (2015, 5, 20) – NGÀY (2015, 5, 15) Tóm lại, để trừ ngày tháng trong Excel, bạn nên dùng tùy chọn đơn giản và rõ ràng nhất – chỉ cần trừ một ngày trực tiếp từ một ngày khác. Nếu bạn muốn tính số tháng hoặc năm giữa hai ngày, hàm DATEDIF là giải pháp duy nhất. Bây giờ ta đã biết cách trừ hai ngày, ta hãy xem làm thế nào có thể thêm hoặc trừ ngày, tháng, hoặc năm cho một ngày nhất định. Có một số hàm Excel thích hợp cho mục đích này, việc sử dụng cái nào phụ thuộc vào đơn vị bạn muốn cộng hoặc trừ.
Làm thế nào để trừ hoặc cộng thêm ngày trong Excel:Nếu bạn có một ngày trong một ô nào đó hoặc một danh sách ngày trong cột, bạn có thể cộng hoặc ngày trừ ngày trong excel bằng cách sử dụng một phép toán số học tương ứng. Ví dụ 1. Cách cộng thêm ngày vào một ngày trong Excel Công thức chung để thêm một số ngày cụ thể cho một ngày như sau: =Date + N ngày Ngày có thể được nhập bằng nhiều cách:
Hình sau minh họa các công thức trên. Ngày hiện tại là 6 tháng 5 năm 2015: Chú ý: Kết quả của các công thức trên là một số thứ tự đại diện cho ngày. Để hiển thị nó dưới dạng ngày tháng, hãy chọn (các) ô và nhấn Ctrl + 1 để mở hộp thoại Format Cells. Trên tab Number, chọn Date trong danh mục Category, và sau đó chọn định dạng ngày bạn muốn. Ví dụ 2. Trừ ngày từ ngày trong Excel Còn được hiểu đơn sơ là thao tác ngày trừ ngày trong excel. Để trừ một số ngày từ một ngày nhất định, bạn thực hiện lại một phép tính số học thông thường. Sự khác biệt duy nhất từ ví dụ trước đó là bạn gõ dấu trừ cho dấu cộng 🙂 =Date – N ngày Dưới đây là một vài ví dụ:
Cách cộng hoặc trừ tuần (từ ngày): Trong trường hợp bạn muốn cộng hoặc trừ tuần cho một ngày nhất định, bạn có thể sử dụng cùng một công thức như thêm / bớt ngày, và nhân số số tuần cho 7: Cộng thêm tuần vào một ngày trong Excel: = A2 + N tuần * 7 Ví dụ, bạn cộng 3 tuần cho ngày trong ô A2, sử dụng công thức sau: = A2 + 3 * 7. Trừ tuần kể từ ngày trong Excel: = A2 – N tuần * 7 Để trừ 2 tuần kể từ ngày hôm nay, bạn viết = TODAY () – 2 * 7. Làm thế nào để cộng, trừ tháng từ ngày trong Excel: Nếu bạn muốn cộng tháng trong excel hoặc là trừ tháng trong excel, bạn có thể sử dụng chức năng DATE hoặc EDATE, như được trình bày dưới đây. Ví dụ 1. Cộng tháng cho một ngày với hàm DATE Chẳng hạn, lấy một danh sách ngày trong cột A, nhập số tháng bạn muốn cộng (số dương) hoặc trừ (số âm) trong một số ô, gọi là C2. Nhập công thức sau vào ô B2 và kéo góc của ô xuống để sao chép công thức vào các ô khác: = DATE (YEAR (A2), MONTH (A2) + $ C $ 2, DAY (A2)) Bây giờ, chúng ta hãy xem kết quả. Logic của công thức là hiển nhiên và đơn giản. Hàm DATE (năm, tháng, ngày) có những lập luận sau:
Đơn giản phải không nào. Nếu bạn gõ một số âm trong C2, công thức sẽ trừ đi các tháng thay vì thêm chúng: Đương nhiên, không có gì ngăn cản bạn gõ dấu trừ trực tiếp trong công thức để trừ đi tháng kể từ ngày: =DATE (YEAR (A2), MONTH (A2) – $ C $ 2, DAY (A2)) Và dĩ nhiên, bạn có thể gõ số tháng để cộng hoặc trừ trong công thức thay vì tham chiếu ô: = DATE (YEAR (ngày), MONTH (ngày) + N tháng, DAY (ngày)) Các công thức thực sự có thể trông giống như sau: Cộng tháng vào ngày:= DATE (YEAR (A2), MONTH (A2) + 2, DAY (A2)) Trừ tháng vào ngày:= DATE (YEAR (A2), MONTH (A2) – 2, DAY (A2)) Ví dụ 2. Cộng hoặc trừ tháng cho một ngày với Excel EDATE Microsoft Excel cung cấp một hàm trả về một ngày là một số nhất định trước hoặc sau ngày bắt đầu n tháng – hàm EDATE. Nó có sẵn trong các phiên bản hiện đại của Excel 2007, 2010, 2013 và 2016. Trong hàm EDATE (start_date, months), bạn cung cấp hai đối số sau:
Công thức sau được sử dụng trên cột thời gian cho kết quả giống hệt như hàm DATE trong ví dụ trước: Khi sử dụng hàm EDATE, bạn cũng có thể chỉ định ngày bắt đầu và số tháng để thêm / trừ trực tiếp trong công thức. Ngày nên được nhập bằng cách sử dụng chức năng DATE hoặc là kết quả của các công thức khác. Ví dụ:
Công thức thêm 10 tháng từ 7 tháng 5 năm 2015.
Công thức trừ 10 tháng kể từ ngày hôm nay. Chú ý. Hàm EDATE trả về một số đại diện cho ngày. Để buộc Excel hiển thị nó như một ngày, bạn nên áp dụng định dạng Ngày cho các ô chứa công thức.
Làm thế nào để trừ hoặc thêm năm vào ngày trong Excel:Thêm năm vào một ngày trong Excel được thực hiện tương tự như thêm tháng. Bạn sử dụng lại hàm DATE (năm, tháng, ngày), nhưng lần này bạn chỉ định bạn muốn thêm bao nhiêu năm nữa: = DATE (YEAR (ngày) + N năm, MONTH (ngày), DAY (ngày)) Trong bảng tính Excel của bạn, các công thức có thể trông như sau: Để thêm năm vào một ngày trong Excel: = DATE (YEAR (A2) + 5, MONTH (A2), DAY (A2)) Công thức thêm 5 năm cho ngày trong ô A2. Để trừ năm kể từ ngày trong Excel: = DATE (YEAR (A2) – 5, MONTH (A2), DAY (A2)) Công thức trừ 5 năm kể từ ngày trong ô A2. Nếu bạn gõ số năm để thêm (số dương) hoặc trừ (số âm) trong một số ô và sau đó tham chiếu ô đó trong hàm DATE, bạn sẽ nhận được một công thức tổng quát: Thêm / xóa ngày, tháng và năm. Nếu bạn quan sát cẩn thận hai ví dụ trước đó, chắc bạn đã đoán được cách cộng trừ ngày tháng trong Excel từ một ngày trong công thức. Để thêm năm, tháng, ngày: = DATE (YEAR (ngày) + X năm, MONTH (ngày) + Y tháng, DAY (ngày) + Z ngày) Để trừ năm, tháng, ngày: = DATE (YEAR (ngày) – X năm, MONTH (ngày) – Y tháng, DAY (ngày) – Z ngày) Ví dụ, công thức sau thêm 2 năm, 3 tháng và trừ 15 ngày kể từ ngày trong ô A2: = DATE (YEAR (A2) + 2, MONTH (A2) + 3, DAY (A2) – 15) Áp dụng cho cột ngày tháng của ví dụ, công thức có dạng sau: = DATE (YEAR (A2) + $ C $ 2, MONTH (A2) + $ D $ 2, DAY (A2) + $ E $ 2) Cách cộng và trừ thời gian trong Excel Trong Microsoft Excel, bạn có thể cộng hoặc trừ thời gian bằng hàm TIME. Nó cho phép bạn hoạt động theo thời gian đơn vị (giờ, phút và giây) chính xác theo cách bạn xử lý năm, tháng và ngày với hàm DATE. Để thêm thời gian trong Excel: = A2 + TIME (giờ, phút, giây) Để trừ thời gian trong Excel: = A2 – TIME (giờ, phút, giây) Ô A2 chứa giá trị thời gian mà bạn muốn thay đổi. Ví dụ, để thêm 2 giờ, 30 phút và 15 giây vào thời gian trong ô A2, bạn có thể sử dụng công thức sau: = A2 + TIME (2, 30, 15) Nếu bạn muốn cộng và trừ thời gian hợp nhất trong một công thức, chỉ cần thêm dấu trừ vào các giá trị tương ứng: = A2 + TIME (2, 30, -15) Công thức trên thêm 2 giờ 30 phút vào ô A2 và trừ 15 giây. Ngoài ra, bạn có thể nhập thời gian bạn muốn thay đổi trong một số ô, và tham chiếu những ô đó trong công thức của bạn: = A2 + TIME ($ C $ 2, $ D $ 2, $ E $ 2) Vẫn có thể dùng công thức trên nếu dữ liệu nguồn chứa cả ngày tháng v à thời gian. Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online. Đó là toàn bộ các trường hợp cộng trừ ngày tháng trong Excel. Hãy lưu lại để mở ra khi cần nhé các bạn. Để có thể sử dụng tốt Excel trong công việc, chúng ta còn phải biết sử dụng các hàm nâng cao như Sumproduct, Sumifs, Countifs… Đồng thời nắm được cách sử dụng các công cụ của Excel như Conditional formatting, Data Validation, Pivot Table… Những kiến thức này các bạn đều có thể tìm hiểu thông qua khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Hiện nay hệ thống đang có nhiều ưu đãi khi bạn tham gia đăng ký khóa học này. Chi tiết xem tại: Học Excel Online |
