Hướng Dẫn Cách Dùng Hàm Tính Ngày Trong Excel Chưa Chắc Bạn Đã Biết

Khi đo lường và thống kê ngày vào Excel, DATE là hàm cơ phiên bản nhất. Như chúng ta cũng có thể biết, Excel không tàng trữ ngày, tháng dưới format ngày. Cố gắng vào đó, Microsoft Excel lưu trữ ngày như một hàng số với đây là đó là nguyên ngân chính gây nhầm lẫn. Hãy thuộc xem Học Excel Online sẽ đưa về những gì cùng 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ó bao gồm công thức sau:

DATE(year, month, day)

Year – chỉ năm. Excel diễn giải đối số thời gian theo khối hệ thống ngày được tùy chỉnh thiết lập trên máy tính của bạn. Theo mang định, Microsoft Excel đến Windows sử dụng khối hệ thống 1900. Trong khối hệ thống ngày tháng này:

nếu năm trong vòng 1900 và 9999, bao gồm giá trị này được sử dụng trong năm. Ví dụ: = DATE (2015, 12, 31) trả về ngày 31 tháng 12 năm 2015. Ví như năm trong khoảng giữa 0 và 1899, Excel tính năm bằng phương pháp cộng thêm số được chỉ định vào năm 1900. Ví dụ: = DATE (100, 12, 31) trả về ngày 31 mon 12 năm 2000 (1900 + 100). Trường hợp năm nhỏ dại hơn 0 hoặc to hơn 9999, hàm DATE đã trả về #NUM! lỗi.

Bạn đang xem: Cách dùng hàm tính ngày

Mẹo. Để tránh nhầm lẫn, luôn luôn luôn ghi tư chữ số của năm. Ví dụ: nếu khách hàng 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 vài nguyên thay mặt cho mon trong năm, từ là một (January) mang đến 12 (December).

nếu tháng lớn hơn 12, Excel sẽ cùng số đó trong thời điểm tháng 1 của năm được chỉ định. Ví dụ: = DATE (2015, 15, 5) trả về hiệu quả ngày 1 mon 3 năm năm 2016 (ngày 5 tháng một năm 2015 cộng với 15 tháng). Nếu như tháng nhỏ dại hơn 1 (số không hoặc cực hiếm âm), Excel trừ đi số mon đó cùng trừ thêm 1, kể từ tháng trước tiên 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 năm trước (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 vào tháng, từ là một đến 31.

Cũng như tháng, đối số ngày rất có thể được hỗ trợ như một số dương với âm, và Excel tính giá trị của nó dựa trên những nguyên tắc tương tự như thể hiện ở trên.

Mẹo. Thời gian đầu, việc hỗ trợ giá trị âm trong đối số mon hoặc ngày của hàm DATE có vẻ như vô lý, tuy thế trên thực tiễn nó hoàn toàn có thể trở đề nghị khá hữu ích, ví dụ như trong công thức phức tạp chuyển đổi tuần.

Hàm DATE bao gồm sẵn trong toàn bộ các phiên bản của Excel 2016, Excel 2013, Excel 2010, Excel 2007, 2003, XP, 2000 tương tự như trong Excel 2016.

Ví dụ hàm DATE

Hãy ban đầu từ phần lớn cái dễ dàng và đơn giản nhất.

Đây là bí quyết sử dụng rõ ràng nhất của hàm DATE vào Excel:

= DATE (2015, 5, 20) – trả về kết quả ngày 20 tháng 5 năm 2015.

Thay bởi chỉ định những giá trị thay mặt cho năm, tháng với ngày trực tiếp trong một công thức, bạn cũng có thể nhận được một số trong những hoặc tất cả các đối số như là công dụng của những hàmkhác:

=DATE(YEAR(TODAY()), 1, 1) – cho kết quả ngày thứ nhất của năm hiện nay tại.

=DATE(YEAR(TODAY()), MONTH(TODAY(), 1)– cho kết quả ngày trước tiên của tháng hiện tại những năm hiện tại.

Ví dụ 2. Hàm
DATE nhằm 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 câu hỏi tính những ngày mà các giá trị năm, tháng cùng ngày được đem từ ô khác. Ví dụ:

= DATE (A2, A3, A4) – trả về tác dụng ngày, mon , năm tự ô A2, A3 với A4.

Ví dụ 3. Hàm DATE biến đổi một chuỗi văn bạn dạng thành một ngày

Khi các ngày được lưu trữ ở định dạng cơ mà Microsoft Excel không nhận ra, lấy ví dụ DDMMYYYY. Trong trường hòa hợp này, chúng ta cũng có thể sử dụng DATE với các hàm không giống để biến đổi một ngày được lưu trữ như là một trong chuỗi văn bạn dạng thành ngày:

= DATE (RIGHT (A2,4), MID (A2,3,2), LEFT (A2,2))

Ví dụ 4. Cùng và trừ ngày vào Excel

Như đã đề cập, Microsoft Excel lưu trữ các ngày như là một trong những chuỗi số và vận động trên những số lượng trong phương pháp và tính toán. Đó là vì sao tại sao khi bạn muốn cộng hoặc trừ một số trong những ngày vào / xuất phát điểm từ 1 ngày duy nhất định, bạn phải chuyển thay đổi ngày kia sang số vật dụng tự đầu tiên bằng cách sử dụng hàm DATE. Ví dụ:

Thêm ngày vào một trong những ngày:

= DATE (2015, 5, 20) + 15

Công thức thêm vào đó 15 ngày cho ngày trăng tròn tháng 5 năm năm ngoái và trả về tác dụng ngày 4 mon 6 năm 2015.

Trừ ngày tính từ lúc ngày:

= DATE (2015, 5, 20) – 15

Kết trái của bí quyết trên là ngày 5 mon 5 năm 2015, có nghĩa là ngày 20 tháng 5 năm năm ngoái 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 bây giờ và một ngày khác mà chúng ta chỉ định.

Nếu bạn cộng hoặc trừ nhì ngày mon được tàng trữ trong 2 ô, thì công thức đơn giản là = A1 + B1 hoặc A1-B1.

Sử dụng hàm DATE vào định dạng tất cả điều kiện

Trong trường hợp các bạn không chỉ giám sát và đo lường mà còn khắc ghi ngày mon trong bảng tính Excel, tiếp nối tạo các quy tắc định hình có đk dựa trên hàm DATE.

Giả sử các bạn có một danh sách những ngày trong cột A và bạn có nhu cầu tô màu cam cách nay đã lâu ngày 01/05 năm năm ngoái và xanh lá cây đều ngày xẩy ra sau ngày 31 tháng 5 năm 2015.

Công thức DATE bạn có nhu cầu là như sau:

Màu cam: = $A2 NGÀY (2015, 5, 31) – sơn màu những ngày to hơn 31 tháng 5 năm 2015.

Chắc hẳn trong vượt trình thao tác làm việc trên bảng tính Microsoft Excel các bạn đã nghe đến các hàm Ngày, tháng, năm… như DATE, MONTH, YEAR… rồi đúng không nhỉ nào? Nhưng so với những chúng ta mới thì chưa chắn chắn đã biết về các hàm tính tháng ngày năm này, nhất là đối với hàm DATE - hàm đo lường và thống kê ngày. Bọn chúng xuất hiện không ít trong các bảng biểu tương quan đến tháng ngày như 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 các ngày vào tháng...

Vậy trong bài viết sau đây họ sẽ cùng tìm hiểu cụ thể về hàm DATE trong Excel nhé!

1. Diễn đạt về hàm

Hàm Microsoft Excel DATE trả về cực hiếm ngày cụ thể. Hàm DATE là 1 trong những hàm ngày thángtrong Excel được phân các loại là Hàm Ngày / giờ đồng hồ . Nó có thể được thực hiện như một hàm bảng tính (WS) vào Excel.

Hình minh họa

2. Cách làm tính hàm ngày vào Excel

Công thức date trong Excel:

=DATE(year, month, day)

Trong đó:

- Year: Đại diện cho năm

- Month: Chỉ số tháng trong thời gian (12 mon trong năm). Nếu giá trị tháng lớn hơn 12, thì cứ sau 12 tháng sẽ thêm 1 năm vào quý hiếm năm . Chúng ta hiểu đơn giản, lấy ví dụ DATE (2019,13,1) bằng DATE (2020,1,1), tức thị tháng 13 của năm 2019 là tháng 1 của năm 2020.

- Day: Chỉ ngày có giá trị từ là một đến 31 và cần phải là số dương.

Nếu cực hiếm day lớn hơn số ngày hồi tháng quy định thì đã cộng vào tháng tiếp và số tháng đã được tạo thêm theo quy dụng cụ của giá trị month, nếu như tháng nhiều lên thừa so với luật thì vẫn đẩy năm lên theo quy phương tiện của quý giá year.

Lưu ý:

Hàm DATE Exceltrả về cực hiếm ngày nối tiếp. Theo khoác định vào Excel ban đầu với ngày trước tiên là ngày một tháng một năm 1900, ngày máy hai là ngày 2 tháng 1 năm 1900, và tiếp là những ngày sau đó.

- ví như năm lớn hơn 9999, hàm DATE đã trả về #NUM! lỗi.

- ví như năm nằm trong tầm từ 0 mang đến 1899, cực hiếm năm được cung ứng 1900 để khẳng định năm.

Xem thêm: Hướng Dẫn Cách Dùng Cốc Kinh Nguyệt San Cho Bạn Nữ Ngày "Dâu"

- giả dụ năm nằm trong tầm từ 1900 cho 9999, hàm DATE thực hiện giá trị năm làm cho năm.

- trường hợp tháng bé dại hơn 1 thì Excel sẽ tự quy đổi -12 bằng một năm và bớt số năm đi.

- ví như tháng lớn hơn 12 thì Excel vẫn tự quy đôi thành 12 bằng một năm và tăng thời gian lên.

3. Giải pháp sử dụnghàm Date trong Excel

3.1. Lấy một ví dụ minh họa

Ví dụ minh họa cách áp dụng hàm DATE

Ta cùng xét ví dụ như sau:

-Tại mẫu đầu tiên, ta bao gồm công thức =DATE(2019,2,14), nghĩa là lúc ta nhập tác dụng ngày, tháng, năm cân xứng vào đã trả về kết quả và đúng là ngày 14 tháng 2 năm 2019.

- Tại mẫu thứ 2, ta nhập quý giá tháng lớn hơn 12,là 50 thì Excel vẫn quy ra quý hiếm 12 mon thành một năm và tăng số thời gian lên theo quy luật. Công dụng trả về vẫn là ngày 14 tháng 2 năm 2023

- Tại chiếc thứ 3, Ta nhập quý giá ngày to hơn 31, Excel đã quy ra tháng cùng cả năm sẽ tiến hành cộng thêm theo quy luật. Tác dụng trả về đang là ngày 24 mon 6 năm 2019.

Lưu ý:Khi các bạn nhập công thức phải định dạng ô dạng Date theo mong ước để tránh vấn đề ngày mon bị đảo lộn:

Bạn nhấp chuột phải, chọn Format Cells, tại Mục Number ấn lựa chọn Date và lựa chọn định dạng ý muốn hiển thị.

Ví dụ minh họa cách sử dụng hàm DATE

3.2. Biến hóa định dạng ngàytháng

Để bao gồm thể biến hóa định dạng ngày tháng, các bạn làm như sau:

- cách 1: trên thanh chế độ của bảng tính Excel, bạn chọn Home, sau đó nhấp chọn vào vết mũi tên nhỏ dại bên mục Number.

Hình minh họa

- bước 2: trong Formet Cells, bạn chọn Date tiếp nối nhấn Ok để đưa định dạng tùy thuộc vào mục đích sử dụng.

Hình minh họa

3.3. Cùng trừ ngày tháng bằng hàm DATE

Trong quá trình học Excel và làm việcbạn có thể dùng hàm DATE để đo lường ngày mon năm trong 1 ô dựa trên ngày tháng năm ở 1 ô khác.

Ví dụ sau:

Hình minh họa

Tại ô phải điền hiệu quả bạn nhập =DATE(YEAR(B4)+5,MONTH(B4),DAY(B4))

Trong đó:

- Cú pháp hàm DATE: =DATE(year,month,day)

- quý giá year đã là YEAR(B4)+5. Ở đây sẽ là quý giá năm ở ô D4 là 2019+5=2024

- quý hiếm month là MONTH(B4). Hiệu quả là tháng sống ô B4 là tháng 2

- quý hiếm day là DAY(B4). Hiệu quả trả về là ngày sống ô B4 là ngày 14.

3.4. Tính toán dựa trên 1 ngày khác

Cách triển khai như sau:

- bước 1: vào ô đề nghị xuất kết quả, các bạn nhập công thức=DATE(YEAR(A1)+3,MONTH(A1)+2,DAY(A1)+17).

Trong đó:

- Hàm YEAR: Tham chiếu ô A1 cùng trích xuất năm 2021. Tiếp nối +3 là giá trị thêm vào đó 3 năm.

- Hàm MONTH: Tham chiếu ô A1 với trích xuất tháng 3. Kế tiếp +2 là giá bán trị cộng thêm 2 tháng.

- Hàm DAY: Tham chiếu là ô A1 và trích xuất ngày 15. Tiếp nối +17 là giá trị cộng thêm 17 ngày.

Hình minh họa

- cách 2: dìm Enter và bình chọn kết quả

Hình minh họa

3.5Chuyển đổi những chuỗi văn bạn dạng và số thành ngày

Trong trường thích hợp này các bạn sẽ tiến hàng chuyển được một chuỗi các chữ số định hình dưới dạng văn bạn dạng (GENERAL, NUMBER,…) thàng ngày, tháng, năm.

Cùng tìm hiểu cụ thể trong ví dụ bên dưới đây.

Bước 1: Trong bảng tính Excel chúng ta nhập hàm =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) vào ô bạn muốn xuất kết quả.

Trong đó

- Hàm LEFT(A1,4) là hàm đem tham chiếu ô A1 với lấy 4 ký tự đầu tiên tính từ mặt trái.

- Hàm MID(A1,5,2) là hàm lấy tham chiếu ô A1 và ban đầu từ cam kết tự lắp thêm 5 rồi lấy 2 ký kết tự quý phái phải.

- Hàm RIGHT(A1,2) là hàm lấy tham chiếu ô A1 với lấy 2 ký kết tự đầu tiên bắt đầu từ quanh đó cùng mặt phải.

Chuyển đổi các chuỗi văn phiên bản và số thành ngày

Bước 2: nhận phím Enter và kiểm tra kết quả.

Chuyển đổi những chuỗi văn bản và số thành ngày 1

3.6 Tăng hoặc sút ngày theo một số ít ngày tuyệt nhất định

Việc tăng, bớt ngày theo một trong những ngày tốt nhất định có một mẹo nhỏ là bạn chỉ cần công hoặc trừ số ngày với mức giá trị hoặc tham chiếu ô bao gồm chứa ngày đó.

Bước 1: trong bảng tính Excel chúng ta nhập=DATE(C2,A2,B2)+C5 vào ô bắt buộc xuất kết quả.

Tăng hoặc bớt ngày theo một số ngày tuyệt nhất định

Bước 2: dấn phím Enter và khám nghiệm kết quả

Tăng hoặc sút ngày theo một số ngày nhất quyết 1

3.7 dùng Hàm DATE trong định dạng tô màu bao gồm điều kiện

Bước 1: Tại đồ họa Excel các bạn chọn New Rule trong mụcConditional Formatting nhằm hiển thị bảng New Formatting Rule.

Dùng Hàm DATE trong format tô màu có điều kiện

Bước 2: tiếp sau chọn hàng ở đầu cuối trong mục Select a Rule Type > nhập cách làm hàm sinh hoạt mục Format values where this formula is true > chọn màu tùy ý sinh sống mục Format > Bấm OK.