Hàm Date trong Excel, cách sử dụng, cấu trúc hàm

Bài viết liên quan

Định nghĩa hàm Date, hàm ngày tháng trong Excel

Hàm DATE được tích hợp sẵn trên Excel cho phép người dùng tạo một ngày với đầy đủ các thành phần năm, tháng và ngày. Đặc biệt hàm DATE cực kỳ hữu ích trong trường hợp cung cấp ngày làm đầu vào cho các hàm khác như hàm SUMIFS hay hàm COUNTIFS, vì người dùng có thể dễ dàng tập hợp các giá trị năm, tháng và ngày từ một ô tham chiếu hoặc kết quả công thức.

Hàm DATE là một trong các hàm ngày tháng được dùng nhiều trong những bài toán liên quan tới Excel, ngoài DATE thì hàm ngày tháng trong Excel còn có TODAY, DAY. Bài viết này, Taimienphi sẽ đi chi tiết về hàm DATE

Mục đích

Tạo các ngày hợp lệ từ năm, tháng và ngày.

Giá trị trả về

Trả về kết quả là giá trị số của một ngày cụ thể trong Excel.

Cú pháp

= DATE (năm, tháng, ngày)

Các tham số

Năm - Năm sử dụng khi tạo ngày.

Tháng - Tháng để sử dụng khi tạo ngày.

Ngày - Ngày để sử dụng khi tạo ngày.

Lưu ý

- Hàm DATE trong Excel trả về kết quả là giá trị số của một ngày cụ thể. Định dạng kết quả một ngày để hiển thị là một ngày (gồm năm, tháng, ngày).

- Nếu năm nằm trong khoảng giữa 0 và 1900, Excel sẽ thêm 1900 vào năm.

- Tháng có thể lớn hơn 12 và nhỏ hơn 0. Nếu tháng lớn hơn 12, Excel sẽ thêm tháng vào tháng đầu tiên trong năm. Nếu tháng nhỏ hơn hoặc bằng 0, Excel sẽ trừ đi giá trị tuyệt đối của tháng cộng thêm 1 (ví dụ ABS (tháng) + 1) tính từ tháng đầu tiên của năm.

- Ngày có thể tương đối hoặc tuyệt đối. Nếu ngày lớn hơn các ngày trong tháng cụ thể, Excel sẽ thêm ngày vào ngày đầu tiên của tháng được chỉ định. Nếu ngày nhỏ hơn hoặc bằng 0, Excel sẽ trừ giá trị tuyệt đối của ngày và cộng thêm 1 (ví dụ ABS (ngày) + 1) tính từ ngày đầu tiên của tháng được chỉ định.

Một số ví dụ về hàm Date

- Ví dụ 1: Đánh dấu ngày lớn hơn

Công thức chung:

=A1>DATE(year,month,day)

Giải thích:

Nếu muốn đánh dấu các ngày lớn hơn hoặc nhỏ hơn một ngày cụ thể với định dạng có điều kiện, bạn có thể sử dụng công thức đơn giản dựa trên hàm DATE. Ví dụ, nếu bạn có ngày trong các ô B4: G11 và làm nổi bật các ô chứa ngày lớn hơn ngày 1/8/2015, chọn phạm vi và tạo quy tắc CF mới sử dụng công thức này:

=B4>DATE(2015,8,1)

Sau khi lưu rule, bạn sẽ nhìn thấy các ngày lớn hơn ngày 8/1/2015 được đánh dấu.

Ý nghĩa công thức:

Hàm DATE tạo một ngày hợp lệ trên Excel với các giá trị năm, tháng, và ngày. Sau đó, chỉ đơn giản là vấn đề so sánh mỗi ngày trong phạm vi với ngày được tạo bằng hàm DATE. Tham chiếu B4 hoàn toàn tương đối, vì vậy sẽ cập nhật theo rule được áp dụng cho mỗi ô trong vùng và bất kỳ ngày nào lớn hơn 8/1/2015 sẽ được đánh dấu.

So sánh lớn hơn hoặc bằng:

Bạn có thể sử dụng tất cả các toán tử tiêu chuẩn trong công thức này để điều chỉnh hành vi nếu cần. Ví dụ, để làm nổi bật tất cả các ngày lớn hơn hoặc bằng ngày 8/1/2015, sử dụng công thức:

=B4>=DATE(2015,8,1)

Sử dụng một ô khác để làm đầu vào:

Không cần mã hóa ngày tháng vào rule. Để tạo ra một rule tương tác linh hoạt hơn, sử dụng một ô khác làm biến trong công thức. Ví dụ, nếu muốn sử dụng ô C2 làm ô đầu vào, đặt tên cho ô C2 "input", nhập ngày và sử dụng công thức này:

=B4>input

Sau đó thay đổi ngày trong ô C2 thành bất kỳ thứ gì bạn muốn và rule định dạng có điều kiện sẽ phản hồi ngay lập tức.

- Ví dụ 2: Lấy ngày cuối cùng của tháng

Công thức chung:

=EOMONTH(date,0)

Giải thích:

Cách dễ nhất để tính ngày cuối cùng của tháng là sử dụng hàm EOMONTH.

Trong ví dụ trên, công thức trong ô B5 là:

=EOMONTH(B5,0)

Ý nghĩa công thức:

Đối số thứ 2 (tháng) của hàm EOMONTH cho phép bạn lấy được ngày cuối cùng trong những tháng tiếp theo hoặc trong tháng trước. Khi bạn sử dụng số 0 trong nhiều tháng, EOMONTH sẽ trả lại kết quả vào ngày cuối cùng trong tháng đó.

Để lấy ngày cuối cùng của tháng trước, sử dụng công thức:

=EOMONTH(date,-1)

Để lấy ngày cuối cùng của tháng tiếp theo, sử dụng công thức:

=EOMONTH(date,1)

Giải pháp thay thế thông minh:

Nếu bạn là người thích xây dựng các công thức thông minh để phân loại, bạn cũng có thể sử dụng hàm DATE trong Excel để lấy ngày cuối cùng của tháng:

=DATE(YEAR(date),MONTH(date)+1,0)

Thủ thuật với công thức này là cung cấp số 0 làm đối số ngày. Khi bạn cung cấp số 0 làm đối số ngày trong hàm DATE, hàm date sẽ quay trở lại ngày đầu tiên của tháng trước cho đến ngày cuối cùng của tháng đó. Vì vậy, bằng cách cộng thêm 1 vào đối số tháng, và sử dụng 0 cho đối số ngày, hàm DATE cuộn lại cho ngày cuối cùng của tháng "ban đầu".

- Ví dụ 3: Bôi đen các ngày có cùng tháng và năm

Công thức chung:

=TEXT(A1,"myyyy")=TEXT(date,"myyyy")

Giải thích:

Nếu muốn sử dụng định dạng có điều kiện để đánh dấu các ngày cùng tháng cùng năm với nhau, bạn có thể sử dụng công thức đơn giản dựa trên hàm TEXT.

Ví dụ, nếu bạn có ngày trong phạm vi B4: G11 và muốn làm nổi bật các ngày có cùng tháng và năm với ngày 1/6/2015, chọn phạm vi B4: G11 và tạo rule CF mới sử dụng công thức dưới đây :

=TEXT(B4,"myyyy")=TEXT(DATE(2015,6,1),"myyyy")

Sau khi lưu rule, bạn sẽ nhìn thấy tất cả các ngày cùng tháng 6 năm 2015 được đánh dấu.

Ý nghĩa công thức:

Công thức này sử dụng hàm TEXT để ghép tháng và năm của mỗi ngày. Sau đó, 2 ngày được kiểm tra sự bình đẳng. TEXT là một hàm hữu ích cho phép bạn chuyển đổi một số thành văn bản ở định dạng văn bản mà bạn chọn. Trong trường hợp này định dạng là định dạng ngày tùy chỉnh "myyyy", có nghĩa là đối số tháng không có số 0 ở đầu và đối số năm có 4 chữ số. Ví dụ, nếu A1 chứa ngày 9/6/2015, TEXT (A1, "myyyy") sẽ tạo chuỗi văn bản "62016".

Sử dụng các ô khác cho đầu vào:

Bạn không cần mã hoá một ngày vào rule. Để tạo rule linh hoạt hơn, bạn có thể sử dụng các ô khác làm biến. Ví dụ, nếu bạn đặt tên cho ô E2 là "date", bạn có thể viết lại công thức như sau:

=TEXT(B4,"myyyy")=TEXT(date,"myyyy")

Và bất cứ khi nào bạn thay đổi ngày trong E2, rule định dạng có điều kiện sẽ cập nhật ngay lập tức. Điều này đơn giản hóa công thức và giúp bạn đọc dễ hơn.

- Ví dụ 4: Tính tổng giá trị theo ngày

Công thức chung:

=SUMIFS(amount,start_date,">"&A1,end_date,"

Giải thích:

Để tính tổng các giá trị giữa 2 ngày, bạn có thể sử dụng hàm SUMIFS. Trong ví dụ trên, ô H5 sử dụng công thức này:

=SUMIFS(amount,date,">"&H5,date,"

Công thức này tính tổng số tiền trong cột D khi một ngày trong cột C nằm giữa một ngày trong cột H5 và một ngày trong cột H6. Trong ví dụ, H5 chứa ngày 15/9/2015 và H6 chứa ngày 15/10 /2015.

Ý nghĩa của công thức

Hàm SUMIFS hỗ trợ toán tử logic (logical operator) của Excel (tức là "=", ">", "> =",...) và nhiều tiêu chí khác.

Để ghép khớp ngày giữa hai giá trị, chúng ta phải sử dụng hai tiêu chí. SUMIF yêu cầu mỗi tiêu chí phải được nhập dưới dạng một vùng tiêu chuẩn:

">"&H5,date // criteria 1

"