Excel có rất nhiều hàm tính toán khác nhau. Sau đây chúng ta cùng tìm hiểu về một hàm khá quen thuộc trong tin học văn phòng đó là hàm Date. Hàm DATE là hàm ngày tháng năm trong excel, hàm căn bản nhất. Như bạn cũng biết, Excel không lưu trữ ngày, tháng dưới định dạng ngày. Cho nên, bài viết dưới đây fifaresources.info sẽ giới thiệu chi tiết về hàm Date cho các bạn.
Tổng quan hàm ngày tháng năm trong excel
Các hàm DATE sẽ trả về kết quả 1 con số thể hiện một ngày cụ thể.
Công thức của hàm ngày tháng năm trong excel
DATE(year, month, day)
Year – chỉ năm.
Trong đó Excel diễn giải đối số năm dựa theo hệ thống ngày được thiết lập có trên máy tính của bạn. Theo mặc định, Microsoft Excel cho Windows tiêu dùng hệ thống 1900. Trong hệ thống ngày tháng này:
- Nếu như năm trong khoảng 1900 và 9999, chính trị giá đó được dùng trong năm. Ví dụ hàm = DATE (2015, 12, 31) kết quả sẽ trả về ngày 31 tháng 12 năm 2015.
- ví như năm từ giữa 0 và 1899, Excel tính năm bằng bí quyết 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 như năm nhỏ hơn 0 hoặc lớn hơn 9999, hàm DATE sẽ trả về #NUM! Lỗi.
Mẹo. Để hạn chế nhầm lẫn, xoành xoạch ghi bốn chữ số của năm. Ví dụ: nếu như bạn nhập “01” hoặc “1” vào đối số năm, hàm ngày tháng năm trong excel sẽ trả về kết quả năm 1901.
Month – 1 số nguyên đại diện cho tháng trong năm
Từ một (January) tới 12 (December). Giả dụ tháng lớn hơn 12, Excel sẽ cộng số đó vào tháng một 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 một năm 2015 cộng mang 15 tháng).
- nếu như 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, từ khi tháng trước hết của năm được chỉ định. Ví dụ: = DATE (2015, -5, 1) trả lại ngày một tháng 7 năm 2014 (ngày 1 tháng một 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ừ một đến 31.
Cũng như tháng, đối số ngày mang thể được cung ứng như 1 số dương và âm, và Excel tính trị giá của nó dựa trên những nguyên tắc như vậy như biểu lộ ở trên.
Mẹo. Lúc đầu, việc để giá trị âm trong đối số tháng hay ngày của hàm DATE có vẻ như phi lý, nhưng trên thực tại nó sở hữu thể phát triển thành tương đối 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 phần lớn 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ụ về hàm ngày tháng năm trong excel
Hãy bắt đầu từ các cái đơn thuần nhất.
Cách dùng rõ ràng nhất của hàm DATE trong Excel:
= DATE (2015, 5, 20) – sẽ được trả về kết quả ngày 20 tháng 5 năm 2015.
Thay vì chỉ định các trị giá đại diện cho năm, tháng và ngày trực tiếp trong 1 công thức, bạn có thể nhận được 1 số hoặc phần nhiều 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 trước nhất của năm bây giờ.
=DATE(YEAR(TODAY()), MONTH(TODAY(), 1)– cho kết quả ngày trước nhất của tháng hiện giờ trong năm hiện tại.
Thí dụ hai. Hàm DATE để trả lại 1 ngày dựa trên những giá trị trong các ô khác
Hàm DATE rất có ích cho việc tính những ngày mà những trị giá 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 trong khoảng ô A2, A3 và A4.
Hàm ngày tháng năm trong excel chuyển đổi 1 chuỗi văn bản thành 1 ngày
Lúc những ngày được lưu trữ ở định dạng mà Microsoft Excel ko nhìn thấy, tỉ dụ DDMMYYYY. Trong trường hợp này, bạn sở hữu thể sử dụng DATE có các hàm khác để chuyển đổi một ngày được lưu trữ như là 1 chuỗi văn bản thành ngày: =DATE (RIGHT (A2,4), MID (A2,3,2), LEFT (A2,2))
Cộng và trừ ngày trong Excel
Như đã kể, Microsoft Excel lưu trữ những ngày như là một chuỗi số và hoạt động trên những Thống kê trong công thức và tính toán. Ấy là lý do vì sao khi bạn muốn cùng hoặc trừ 1 số ngày vào / từ một ngày nhất thiết, bạn cần phải chuyển đổi ngày đấy sang số thứ tự đầu tiên bằng bí quyết tiêu dùng hàm DATE. Ví dụ:
Thêm ngày vào 1 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 bắt đầu từ ngày:
= DATE (2015, 5, 20) – 15
Kết quả của công thức trên là ngày 05/05/2015, nghĩa là ngày 20 tháng 5 năm 2015 trừ đi 15 ngày.
Để trừ một ngày trong khoảng 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 nay và 1 ngày khác mà bạn chỉ định.
Nếu như bạn cộng hoặc trừ hai tháng ngày được lưu trữ trong 2 ô, thì công thức đơn giản là = A1 + B1 hoặc A1-B1.
Dùng hàm ngày tháng năm trong excel trong định dạng với điều kiện
Trong trường hợp sẽ không chỉ tính toán mà bạn còn phải đánh dấu ngày tháng trong bảng tính Excel, sau đó tạo những lề luật định dạng mang điều kiện dựa trên hàm DATE.
giả tỉ bạn sở hữu một danh sách những ngày trong cột A và bạn muốn tô màu cam ngày trước ngày một tháng 5 năm 2015 và xanh lá cây các ngày xảy ra sau ngày 31 tháng 5 năm 2015.
Công thức DATE như sau:
Màu cam sẽ hiển thị với công thức = $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 to hơn 31 tháng 5 năm 2015.
Để sở hữu thể vận dụng các hàm 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. Ngoài hàm ngày tháng năm trong excel – Date thì còn có những hàm giúp vận dụng tốt vào công tác như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Các dụng cụ khác thường sử dụng là Data validation, Conditional formatting, Pivot table…