CÁCH SỬ DỤNG HÀM DATE TRONG EXCEL TÍNH NĂM ĐƠN GIẢN, HƯỚNG DẪN CÁCH SỬ DỤNG HÀM DATE TRONG EXCEL
Trong quá trình làm việc với Microsoft Excel các bạn đã nghe đến các hàm về ngày, tháng, năm như hàm DATE, MONTH, YEAR…Tuy nhiên, đối với những bạn mới thì chưa chắc các bạn đã biết về các hàm tính ngày tháng năm này, đặc biệt là hàm DATE – hàm dùng để tính toán các ngày. Hàm này thường xuất hiện rất nhiều trong các bảng liên quan đến ngày tháng như bảng hiệu suất công việc các ngày, so sánh thông tin các ngày, xuất- nhập kho vào các ngày trong tháng…Vậy hãy cùng Daotaotinhoc tìm hiểu chi tiết về hàm DATE trong bài viết dưới đây nhé!
MỤC LỤC
3. Hướng dẫn cách sử dụng hàm Date trong Excel
1. Định nghĩa hàm Date là gì? Ứng dụng của hàm Date trong Excel
Định nghĩa: Hàm Date là hàm được dùng để tạo các ngày hợp lệ từ năm, tháng và ngày trong bảng tính Excel.
Bạn đang xem: Cách sử dụng hàm date
Ứng dụng của hàm Date trong Excel:
Kết quả trả về là giá trị số của một ngày cụ thể.Có thể kết hợp được với nhiều hàm khác nhau trong Excel để đạt được kết quả mong muốn.2. Cú pháp chi tiết của hàm Date trong Excel
Để tìm kết quả số cho một ngày cụ thể, bạn nhập hàm với cú pháp như sau:
=DATE(year,month,day)
Trong đó:
Year: là giá trị đại diện cho năm.Month: là giá trị chỉ số tháng trong năm (tức 12 tháng trong năm).Day: là giá trị chỉ ngày có giá trị từ 1 đến 31 và bắt buộc phải là số dương.Lưu ý:
Nếu Year lớn hơn 0 và nhỏ hơn hoặc bằng 1899 thì phía Excel sẽ tự động cộng thêm 1900 vào để tính và cho ra kết quả.Nếu Year nằm trong khoảng từ 1900 đến 9999 thì Excel sẽ dùng số đó để làm năm cần tính.Nếu Year nhỏ hơn ) hay lớn hơn hoặc bằng 1000 thì Excel sẽ trả về kết quả lỗi là #NUM!.Nếu Month lớn hơn 12 thì Excel sẽ tiến hành quy đổi thành 12 bằng một năm và tăng số năm lên.Nếu Month nhỏ hơn 1 thì Excel sẽ tiến hành quy đổi thành -12 bằng một năm và giảm số năm đi.Nếu Day lớn hơn số ngày của một tháng thì Excel sẽ tiến hành quy đổi thành số ngày nhiều nhất của tháng cho phù hợp tăng số tháng lên, nếu như số tháng lớn hơn 12 tháng thì số năm cũng sẽ được tăng lên.3. Hướng dẫn cách sử dụng hàm Date trong Excel
Hướng dẫn thay đổi định dạng ngày tháng
Lưu ý: Để có thể sử dụng được hàm Date mà không mắc những lỗi ngoài ý muốn thì bạn hãy thay đổi định dạng ngày tháng của Excel theo cách hướng dẫn chi tiết sau nhé.
Bước 1: Tại thanh công cụ của bảng tính Excel, bạn hãy chọn Home và nhấp chuột vào mũi tên nhỏ bên mục Number.
Bước 2: Nhấn chọn Date và chọn OK để chuyển định dạng tùy theo mục đích sử dụng của bạn.
Cộng và trừ ngày trong bảng tính Excel bằng hàm Date
Ví dụ 1: Tiến hành cộng thêm 7 ngày
Bước 1: Nhập =DATE(2022,10,22) + 7 vào bảng tính Excel
Lưu ý:
Bạn có thể sử dụng hàm Date để tính cho bất cứ ngày nào trong tháng, trong năm.Bạn chỉ cần thay dấu + thành dấu – nếu bạn muốn trừ đi ngày theo mục đích mong muốn.
Bước 2: Nhấn phím Enter và kiểm tra kết quả trả về
Ví dụ 2: Hãy trừ 1 ngày tính từ ngày hôm nay
Bước 1: Nhập hàm =TODAY() – DATE(2020,10,22) vào ô cần kiểm tra kết quả trong bảng tính Excel.
Bước 2: Nhấn Enter và kiểm tra kết quả trả về
Tính toán dựa vào 1 ngày khác trong năm
Lưu ý: Cách này sẽ giúp bạn cộng ngày hiện tại với các số tự nhiên để cho ra 1 ngày mới mà bạn đang cần tính.
Bước 1: Trong bảng tính Excel bạn làm như sau:
=DATE(YEAR(A1)+3,MONTH(A1)+2,DAY(A1)+17) vào ô mà bạn cần kiểm tra kết quả.
Trong đó:
Hàm YEAR tham chiếu ô A1 và trích xuất năm 2022. Sau đó +3 có nghĩa là giá trị cộng thêm 3 năm.Hàm MONTH tham chiếu là ô A1 và trích xuất vào tháng 3. Sau đó +2 có nghĩa là giá trị cộng thêm 2 tháng.Hàm DAY sẽ tham chiếu ô A1 và trích xuất ngày 15. Sau đó +17 có nghĩa là giá trị công thêm 17 ngày.
Bước 2: Nhấn Enter và kiểm tra kết quả trả về
Hướng dẫn chuyển đổi các chuỗi văn bản số thành ngày
Lưu ý: Ở phần hướng dẫn này bạn có thể chuyển được 1 chuỗi các chữ số được định dạng dưới dạng văn bản(GENERAL, NUMBER,…) thành ngày, tháng và năm
Bước 1: Nhập hàm sau vào bảng tính Excel:
=DATE(LEFT(A2,4)MID(A2,5,2),RIGHT(A2,2)) vào ô mà bạn muốn kiểm tra kết quả
Trong đó:
Hàm LEFT(A2,4) là hàm lấy tham chiếu ô A2 và lấy 4 ký tự đầu tiên được tính từ bên trái.Hàm MID(A2,5,2) là hàm lấy tham chiếu ô A2 và được bắt đầu từ ký tự thứ 5 rồi lấy 2 ký tự sang phải.Hàm RIGHT(A2,2) là hàm lấy tham chiếu ô A2 và lấy 2 ký tự đầu tiên được tính bắt đầu từ ngoài cùng bên phải.
Bước 2: Nhấn Enter và kiểm tra kết quả trả về
Hướng dẫn tăng hoặc giảm theo 1 số ngày nhất định
Mẹo tham khảo: Để có thể tăng hoặc giảm ngày theo một số ngày nhất định, bạn chỉ cần cộng hoặc trừ số ngày với giá trị hoặc tham chiếu ô có chứa ngày.
Bước 1: Nhập công thức hàm sau vào bảng tính:
=DATE(C2,A2,B2)+A6 vào ô cần kiểm tra kết quả
Lưu ý: Các tham chiếu ô A2, B2, C2,… là những ô tham chiếu mà mình đang có giá trị để hướng dẫn và bạn cũng có thể thay đổi giá trị của ô tham số này.
Bước 2: Nhấn Enter và kiểm tra kết quả trả về
Dùng hàm DATE trong định dạng tô màu có điều kiện
Bước 1: Trong bảng tính Excel bạn hãy chọn New Rule trong mục Conditional Formatting để hiển thị bảng New Formatting Rule.
Bước 2: Bạn hãy chọn hàng cuối cùng trong mục Select a Rule Type -> nhập công thức hàm ở mục Format values where this formula is true -> chọn màu tùy ý ở mục Format -> chọn Ok
Bước 3: Sau khi thiết lập công thức bạn hãy ấn chọn OK
Bước 4: Kiểm tra kết quả trả về
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é!
Cú pháp hàm DATE
Nhữ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:
Nếu năm trong khoảng 1900 và 9999, chính giá trị đó được sử dụng trong năm. Ví dụ: = DATE (2015, 12, 31) trả về ngày 31 tháng 12 năm 2015. Nếu năm trong khoảng giữa 0 và 1899, Excel tính năm bằng cách cộng thêm số được chỉ định vào năm 1900. Ví dụ: = DATE (100, 12, 31) trả về ngày 31 tháng 12 năm 2000 (1900 + 100). Nếu năm nhỏ hơn 0 hoặc lớn hơn 9999, hàm DATE sẽ trả về #NUM! lỗi.Xem thêm: Cách bán hàng online cho học sinh, hiệu quả➸kinh nghiệm bán hàng online
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).
Nếu tháng lớn hơn 12, Excel sẽ cộng số đó vào tháng 1 của năm được chỉ định. Ví dụ: = DATE (2015, 15, 5) trả về kết quả ngày 1 tháng 3 năm 2016 (ngày 5 tháng 1 năm 2015 cộng với 15 tháng). Nếu tháng nhỏ hơn 1 (số không hoặc giá trị âm), Excel trừ đi số tháng đó và trừ thêm 1, kể từ tháng đầu tiên của năm được chỉ định. Ví dụ: = DATE (2015, -5, 1) trả lại ngày 1 tháng 7 năm 2014 (ngày 1 tháng 1 năm 2015 trừ đi 6 tháng).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 DATE
Hã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àm
DATE để 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.
Sử dụng hàm DATE trong định dạng có điều kiện
Trong 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, 31) – tô màu các ngày lớn hơn 31 tháng 5 năm 2015.
