Hướng dẫn tính hàm Date và hàm Dateif trong Excel - Hướng dẫn tính ngày tháng năm trong Excel đơn giản
Sử dụng Excel để tính toán dữ liệu thường được các bạn học sinh, sinh viên, nhân viên văn phòng áp dụng để thao tác được nhanh hơn. Bên cạnh những hàm tính tổng, hiệu, căn bậc hai thì trong Excel còn có thể tính được thời gian nhờ vào việc sử dụng hàm Date hay hàm Dateif. Nếu bạn muốn biết:
Hàm Date là gì?Hàm Dateif là gì?
Công thức của hàm Date?
Công thức của hàm Dateif?
Thì bài viết ngày hôm nay, Mega sẽ giải đáp từ a –z và hướng dẫn bạn cách sử dụng một cách chi tiết nhé!
Hướng dẫn sử dụng hàm DATE và hàm DATEDIF trong Excel
I. Hướng dẫn sử dụng hàm DATE trong Excel
1. Hàm Date là gì?
Hàm Date một hàm đơn giản trong Excel, hàm này giúp tạo ra các ngày hợp lệ từ ngày, tháng, năm trong Excel.
Bạn đang xem: Hàm điều kiện thời gian trong excel
Bạn có thể ứng dụng hàm Date này:
Giá trị trả về là giá trị của một ngày cụ thể.Kết hợp được với nhiều hàm khác nhau để tạo ra một kết quả như ý trong Excel.
2. Công thức của hàm Date
= DATE(year,month,day)
Trong đó:
Year là hiển thị cho chỉ số nămMonth là hiển thị chỉ số tháng
Day là hiển thị cho chỉ số ngày
Một số lưu ý khi sử dụng hàm Date
Nếu Year lớn hơn 0 và nhỏ hơn hoặc bằng 1899 thì Excel sẽ tự động cộng thêm 1900 vào để tính.Nếu Year từ 1900 đến 9999 thì Excel sẽ dùng số đó làm năm cần tính.Nếu Year nhỏ hơn 0 hoặc lớn hơn hoặc bằng 10000 thì Excel sẽ báo lỗi #NUM!.Nếu Month lớn hơn 12 thì Excel sẽ tự 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ẽ tự quy đổi -12 bằng 1 năm và giảm số năm đi.Nếu Day lớn hơn số ngày của tháng thì Excel sẽ quy đổi thành số ngày nhiều nhất của tháng cho phù hợp và tăng số tháng lên, nếu số tháng lớn hơn 12 tháng thì số năm cũng được tăng lên.
3. Một số ví dụ hướng dẫn cách sử dụng hàm Date trong Excel
- Thay đổi định dạng ngày tháng trong Excel
Bước 1: Tại vị trí thanh công cụ trong bảng Excel, bạn chọn vào Home và nhấn vào dấu mũi tên nhỏ tại vị trí bên mục Number.
Chọn vào Home và nhấn vào dấu mũi tên nhỏ tại vị trí bên mục Number
Bước 2: Khi cửa sổ hiện ra bạn nhấn chọn Date và chọn OK để chuyển định dạng tùy theo mục đích sử dụng.
Chọn Date và chọn OK để chuyển định dạng
- Hàm Date giúp bạn có thể cộng và trừ ngày
Ví dụ: Công thêm 9 ngày.
Bước 1: Bạn thực hiện nhập =DATE(2022, 5, 15) + 9 vào ô bảng tính trong Excel.
Nhập =DATE(2022, 5, 15) + 9 vào ô bảng tính trong Excel
Bước 2: Nhấn phím Enter và kiểm tra kết quả.
Kết quả hiện lên khi bạn nhấn Enter
Ví dụ 2: Trừ một ngày kể từ ngày hôm nay.
Bước 1: Trong bảng Excel bạn nhập hàm =TODAY()-DATE(2022,6,5)
Trong bảng Excel bạn nhập hàm =TODAY()-DATE(2022,6,5)
Bước 2: Nhấn phím Enter và kiểm tra kết quả.
Kết quả được hiện lên sau khi bạn nhấn Enter
- Tính toán dựa trên 1 ngày khác trong Excel
Bước 1: Trong giao diện bảng tính Excel bạn nhập hàm =DATE(YEAR(A7)+3,MONTH(A7)+2,DAY(A7)+5) vào ô bạn cần xuất kết quả.
Trong giao diện bảng tính Excel bạn nhập hàm =DATE(YEAR(A7)+3,MONTH(A7)+2,DAY(A7)+5)
Giải thích hàm:
Hàm YEAR tham chiếu ô A1 và trích xuất năm 2022. Sau đó +3 là giá trị cộng thêm 3 năm.Hàm MONTH tham số chiếu là ô A1 và trích xuất tháng 5. Sau đó +2 là giá trị công thêm 2 tháng.Hàm DAY tham số chiếu là ô A1 và trích xuất ngày 25. Sau đó +5 là giá trị công thêm 5 ngày.Bước 2: Nhấn Enter và kiểm tra kết quả.
Nhấn Enter và kiểm tra kết quả
Giải thích: Vì tháng một năm chỉ có 12 tháng, trong phép toán này 12 + 2 sẽ thành tháng 2 của năm sau nữa. Vì thế kết quả như trên sẽ cộng thêm 1 ở năm.
- Chuyển đổi các chuỗi văn bản và số thành ngày
Bước 1: Trong bảng tính Excel bạn nhập hàm =DATE(LEFT(A4,4),MID(A4,5,2),RIGHT(A4,2)) vào ô bạn muốn xuất kết quả.
Trong bảng tính Excel bạn nhập hàm =DATE(LEFT(A4,4),MID(A4,5,2),RIGHT(A4,2))
Giải thích hàm:
Hàm LEFT(A1,4) là hàm lấy tham chiếu ô A1 và lấy 4 ký tự đầu tiên tính từ bên trái.Hàm MID(A1,5,2) là hàm lấy tham chiếu ô A1 và bắt đầu từ ký tự thứ 5 rồi lấy 2 ký tự sang phải.Hàm RIGHT(A1,2) là hàm lấy tham chiếu ô A1 và lấy 2 ký tự đầu tiên bắt đầu từ ngoài cùng bên phải.Bước 2: Nhấn phím Enter và kiểm tra lại kết quả có đúng như bạn mong muốn hay không.
Kết quả sẽ hiện ra khi bạn nhấn Enter
- Tăng hoặc giảm ngày theo 1 ngày nhất định
Bước 1: Trong bảng tính Excel bạn nhập công thức hàm =DATE(C12,A12,B12)+C15 vào ô cần xuất kết quả.
Một lưu ý: Các tham chiếu ô C12, A12, B12 ,... là những thông số mà mình ví dụ để đưa ra hướng dẫn chi tiết. Công thức vẫn sẽ như hình dưới đây và bạn cần thay đổi những con số để thực hiện phép tính.
Trong bảng tính Excel bạn nhập công thức hàm =DATE(C12,A12,B12)+C15
Bước 2: Nhấn phím Enter và kiểm tra kết quả.
Nhấn phím Enter và kiểm tra kết quả
Cách tính phép trừ cũng làm tương tự như phép cộng.
- Sử dụng hàm DATE trong định dạng tô màu có điều kiện
Bước 1: Trong giao diện bảng tính Excel bạn chọn New Rule trong mục Conditional Formatting để hiển thị bảng New Formatting Rule.
Bước 2: Bạn 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 > Bấm OK.
Bước 3: Sau khi đã thiết lập công thức hàm xong bạn bấm chọn OK.
Bước 4: Kiểm tra kết quả.
III. Hướng dẫn sử dụng hàm DATEIF trong Excel
1. Hàm DATEIF trong Excel là gì?
Hàm DATEIF hàm dùng để xác định khoảng thời gian giữa hai thời điểm mà bạn muốn biết.
Một lưu ý khi sử dụng hàm này: Nếu ngày bắt đầu lớn hơn ngày kết thúc thì kết quả cho ra sẽ là #NUM!
2. Công thức của DATEIF
Hàm DATEDIF này sẽ không có công thức nhất định, vậy nên bạn cần dựa vào các ví dụ dưới đây của Mega để thực hiện được hàm này nhé!
Tuy nhiên trước tiên bạn cần thay đổi số liệu trong bảng tính với những con số của bạn.
- Công thức tính ngày chênh lệnh ở Hàm DATEIF
Như trong ví dụ trên, công thức sẽ là =DATEIF(A2,B2,”d”)
Với ví dụ như trên ngày bắt đầu sẽ là A2 và ngày kết thúc sẽ là B2. Kết quả cho ra ở ô C2. “d” trả về số ngày chênh lệch giữa ngày bắt đầu và ngày kết thúc.
Bước 1: Tại ô C2 ta nhập công thức tính ngày chênh lệch giữa ngày bắt đầu và ngày kết thúc, ta sẽ có công thức như trong hình dưới đây.
Công thức tính ngày chênh lệnh ở Hàm DATEIF
Bước 2: Sau khi nhập đúng công thức ta sẽ nhận kết quả chính xác như trong hình.
Kết quả nhận được sau khi nhấn Enter cho lệnh hàm DATEDIF
- Công thức tính số tuần chênh lệch trong Excel
Công thức: =DATEDIF(A2,B2,”d”)/7
Ngày bắt đầu nằm trong ô A2 và ngày kết thúc ở ô B2. Kết quả cho ra ở ô C3. “d” trả về số ngày. Tuy nhiên, ở đây ta có thêm phép /7 để tìm ra số tuần chênh lệch giữa 2 ngày (vì 1 tuần có 7 ngày).
Bước 1: Ta thực hiện theo công thức =DATEDIF(A2,B2,”d”)/7 để tính số tuần chênh lệch trong Excel như bảng dưới đây.
Bước 2: Nhấn Enter để nhận lại kết quả.
Lưu ý, kết quả này cần được định dạng lại do có thể nó sẽ cho ra một số thập phân. Nhấn CTRL + 1. Sau đó, bấm số vị trí thập phân: 2 (hiện 2 chữ số ở phần thập phân).
Kết quả nhận được sau sử dụng hàm DATEDIF
- Công thức tính số tháng chênh lệch trong Excel
Công thức: =DATEDIF(A2,B2,”m”)
Ngày bắt đầu nằm trong ô A2 và ngày kết thúc ở ô B2. Kết quả cho ra ở ô C3. “m” trả về số tháng chênh lệch giữa hai ngày.
Công thức tính số tháng chênh lệch trong Excel
Kết quả cho ra sẽ như hình dưới đây.
Kết quả ra được là 28 tháng chênh lệch
- Công thức tính số năm chênh lệch trong Excel
Công thức: =DATEDIF(A2,B2,”y”)
Ngày bắt đầu nằm trong ô A2 và ngày kết thúc ở ô B2. Kết quả cho ra ở ô C3. “y” trả về số năm chênh lệch giữa hai ngày.
Kết quả tính toán nhanh chóng khi sử dụng Excel nhờ vào hàm DATEDIF
- Công thức tính số độ tuổi trong lũy tích năm, tháng, ngày trong Excel
Hàm DATEDIF còn có thể dùng để tính tuổi thọ, hoặc tổng thời gian đã làm một công việc của một người nào đó. Kết quả ra một khoảng thời gian cụ thể ví dụ như “3 năm, 2 tháng, 5 ngày”.
- Sử dụng DATEDIF để tính tổng số năm
Công thức: =DATEDIF(A2,B2,”y”)
Với ví dụ này, ngày bắt đầu nằm trong ô A2 và ngày kết thúc ở ô B2. Kết quả cho ra ở ô C2. “y” trả về số năm chênh lệch giữa hai ngày.
Bước 1: Nhập công thức =DATEDIF(A2,B2,”y”) vào ô bạn cần kết quả.
Nhập công thức =DATEDIF(A2,B2,”y”) vào ô bạn cần kết quả
Bước 2: Nhấn Enter để nhận kết quả.
Sử dụng hàm Datedif để tính tổng số năm sẽ được kết quả như hình trên
- Sử dụng DATEDIF một lần nữa để tính số tháng còn lại
Công thức: =DATEDIF(A2,B2,”ym”)
Ngày bắt đầu nằm trong ô A2 và ngày kết thúc ở ô B2. Kết quả cho ra ở ô C3. “ym” trả về số tháng còn lại trong năm đầy đủ cuối cùng.
- Sử dụng DATEDIF để tính tổng số ngày
Công thức: =B2-DATE(YEAR(B2),MONTH(B2),1)
Ngày bắt đầu nằm trong ô A2 và ngày kết thúc ở ô B2. Kết quả cho ra ở ô C2. Bây giờ ta cần tìm số ngày còn lại. Công thức này lấy ngày kết thúc gốc trong ô B2 trừ ngày đầu tiên của tháng kết thúc .
Nhập công thức theo trên hình để tính tổng số ngày
Lưu ý: Không khuyên dùng tham đối “MD” của DATEDIF vì nó có thể cho ra kết quả không chính xác.
- Kết hợp 3 công thức
Công thức:
=DATEDIF(A2,B2,”y”)&”years, “
&DATEDIF(A2,B2,”ym”) &”months, “
&B2-DATE(YEAR(B2),MONTH(B2),1) &”days”
Cách này cho ra nhanh kết quả bằng cách kết hợp 3 công thức trên. Ngày bắt đầu nằm trong ô A2 và ngày kết thúc ở ô B2. Kết quả cho ra ở ô C3.
Kết hợp 3 công thức trong hàm datedif
Kết quả cuối cùng cho ra được như bảng trên
Mẹo nhỏ trong Excel: Nhấn ALT + ENTER để đặt dấu ngắt dòng trong công thức của bạn. Điều này giúp bạn đọc được dễ dàng hơn. Ngoài ra, hãy nhấn CTRL + SHIFT + U nếu bạn không nhìn thấy toàn bộ công thức.
IV. Tổng kết
Như vậy trong bài viết ngày hôm nay Mega đã hướng dẫn chi tiết cho bạn cách sử dụng hàm DATE và hàm DATEIF. Hi vọng những thông tin về hai hàm này sẽ có ích và hỗ trợ được bạn thật nhiều trong công việc.
Ngoài ra nếu bạn muốn tìm hiểu thêm về cách sử dụng các hàm thì tại website mega.com.vn cũng có đầy đủ những thông tin và hướng dẫn bạn cách sử dụng các hàm rất chi tiết.
Trong bài viết này, Học Excel Online sẽ giải thích cách áp dụng các quy tắc được xây dựng trong Excel và tạo các định dạng có điều kiện của riêng bạn dựa trên các công thức.
Và bây giờ chúng ta sẽ tận dụng kiến thức này và tạo ra các bảng tính phân biệt giữa các ngày trong tuần và cuối tuần, làm nổi bật các ngày lễ chính thức và hiển mốc thời gian sắp tới. Nói cách khác, chúng ta sẽ áp dụng định dạng có điều kiện Excel cho ngày. Nếu bạn có một số kiến thức cơ bản về các công thức Excel, bạn có thể quen với một số hàm ngày giờ như NOW, TODAY, DATE, WEEKDAY, vv Trong hướng dẫn này, chúng ta sẽ tìm hiểu sâu hơn các hàm để định dạng có điều kiện ngày theo cách bạn muốn.
Xem nhanh
Định dạng có điều kiện cho ngày tháng (các quy tắc cài sẵn)Công thức để làm nổi bật ngày trong tương lai
Định dạng có điều kiện cho ngày tháng (các quy tắc cài sẵn)
Microsoft Excel cung cấp 10 tùy chọn để định dạng các ô được chọn dựa trên ngày hiện tại. Để áp dụng định dạng, bạn chỉ cần vào tab Home> Conditional Formatting>Highlight Cell Rules và chọn A Date Occurring...
Như bạn có thể biết Microsoft Excel cung cấp hàm TODAY () cho các phép tính khác nhau dựa trên ngày hiện tại. Đây chỉ là một vài ví dụ về cách bạn có thể sử dụng nó để định dạng có điều kiện trong Excel.
Ví dụ 1. Làm nổi bật ngày bằng, lớn hơn hoặc nhỏ hơn ngày hôm nay
Để định dạng có điều kiện các ô hoặc toàn bộ hàng dựa trên ngày hôm nay, bạn sử dụng hàm TODAY như sau:
Bằng với ngày hôm nay: = $ B2 = TODAY ()
Lớn hơn ngày hôm nay: = $ B2> TODAY ()
Nhỏ hơn ngày hôm nay: = $ B2 Hàm IF và cách sử dụng hàm IF trong Excel
Đối với nhiệm vụ này, bạn sẽ cần một cột bổ sung với công thức sau (trong đó E là cột Deliveryvà cột F là Invoice:
= IF (E2> = TODAY (), IF (F2 = “”, 1, 0), 0)
Nếu ngày giao hàng lớn hơn hoặc bằng ngày hiện tại và không có số trong cột Hóa đơn, công thức sẽ trả về 1, nếu không thì số đó là 0.
Sau đó bạn tạo một quy tắc định dạng điều kiện đơn giản cho cột Invoice có công thức = $G2 = 1 trong đó G là cột bổ sung của bạn. Tất nhiên, bạn sẽ có thể ẩn cột này sau.
Giả sử bạn có lịch trình dự án liệt kê các nhiệm vụ, ngày bắt đầu và thời gian thực hiện của chúng. Những gì bạn muốn là ngày kết thúc cho mỗi công việc được tính toán tự động. Một thách thức nữa là định dạng này được xem lại với mỗi cuối tuần. Ví dụ: nếu ngày bắt đầu là 13 tháng 6 năm 2014 và số ngày làm việc (Duration) là 2, ngày kết thúc sẽ là vào ngày 17 tháng 6 năm 2014, vì 14-tháng 6 và 15-tháng 6 là Thứ 7 và Chủ Nhật .
Để làm điều này, chúng tôi sẽ sử dụng WORKDAY.INTL (start_date, days,
Và cuối cùng, bạn có thể muốn làm nổi bật các hàng tùy thuộc vào khoảng thời gian đến hạn. Ví dụ: các quy tắc định dạng có điều kiện dựa trên hai công thức sau đây làm nổi bật ngày sắp kết thúc và ngày kết thúc gần đây, tương ứng:
= VÀ ($ D2-TODAY ()> = 0, $ D2-TODAY () = 0, TODAY () – $ D2 TODAY () – làm nổi bật tất cả các ngày trong tương lai (nghĩa là ngày lớn hơn ngày hiện tại). Bạn có thể sử dụng nó để làm nổi bật các sự kiện sắp tới.
Tất nhiên, có các biến thể vô hạn của các công thức trên, tùy thuộc vào nhiệm vụ cụ thể của bạn. Ví dụ:
= $ D2-TODAY ()> = 6 – làm nổi bật ngày xảy ra trong 6 ngày trở lên.
= $ D2 = TODAY () – 14 – nhấn mạnh các ngày xảy ra chính xác 2 tuần trước.
Làm thế nào để làm nổi bật ngày tháng trong một phạm vi ngày:
Nếu bạn có một danh sách dài các ngày trong bảng tính của mình, bạn cũng có thể muốn làm nổi bật các ô hoặc hàng nằm trong phạm vi ngày nhất định, tức là nhấn mạnh tất cả các ngày nằm giữa hai ngày nhất định.
Bạn có thể hoàn thành nhiệm vụ này bằng cách sử dụng chức năng TODAY (). Bạn sẽ chỉ cần phải xây dựng một công thức tinh vi hơn một chút như trong các ví dụ dưới đây.
Công thức để làm nổi bật ngày tháng vừa qua Hơn 30 ngày trước đây: = TODAY () – $A2> 30 Từ 30 đến 15 ngày trước, bao gồm: = AND (TODAY () – $A2> = 15, TODAY () – $A2 Chưa đến 15 ngày trước: = AND (TODAY () – $A2> = 1, TODAY () – $A2Ngày hiện tại và bất kỳ ngày tháng tương lai nào không được tô màu.
Ngày hiện tại và bất kỳ ngày tháng vừa qua nào không được tô màu.
Làm thế nào để tô màu các khoảng trống và khoảng thời gian:
Trong ví dụ cuối cùng này, chúng ta sẽ sử dụng thêm một hàm date khác của Excel – DATEDIF (start_date, end_date, interval). Hàm này tính toán sự khác biệt giữa hai ngày dựa trên khoảng thời gian quy định. Nó khác với tất cả các hàm khác mà chúng tôi đã thảo luận trong hướng dẫn này theo cách mà nó cho phép bạn bỏ qua tháng hoặc năm và chỉ tính sự khác biệt giữa các ngày hoặc tháng, tùy theo bạn chọn.
Hãy suy nghĩ về nó bằng một cách khác … Giả sử bạn có một danh sách các ngày sinh của các thành viên gia đình và bạn bè của bạn. Bạn có muốn biết có bao nhiêu ngày cho đến ngày sinh nhật tiếp theo? Hơn nữa, còn bao nhiêu ngày nữa là đến ngày cưới và những sự kiện khác mà bạn không muốn bỏ lỡ? Dễ dàng!
Đây là công thức bạn cần (trong đó A là cột Ngày của bạn):
= DATEDIF (TODAY (), DATE ((NĂM (TODAY ()) + 1), MONTH ($A2), DAY ($A2)), “yd”)
Loại khoảng thời gian “yd” ở cuối của công thức được sử dụng để bỏ qua năm và chỉ tính chênh lệch giữa các ngày. Để có danh sách đầy đủ các loại khoảng thời gian khả dụng, hãy nhìn vào đây.
Mẹo. Nếu bạn quên mất hoặc đặt sai tên công thức phức tạp đó, bạn có thể sử dụng công thức phức tạp này: = 365-DATEDIF ($A2, TODAY (), “yd”). Nó tạo ra kết quả chính xác như nhau, chỉ cần nhớ thay thế 365 thành 366 trong năm nhuận:)
Và bây giờ hãy tạo một quy tắc định dạng có điều kiện để tô màu các khoảng trống khác nhau với các màu khác nhau. Trong trường hợp này, bạn nên sử dụng Excel Scales Color (Conditional formatting> Color Scales) hơn là tạo ra một quy tắc riêng cho từng khoảng thời gian.
Xem thêm: Cách Phát Trực Tiếp Facebook Trên Máy Tính Chạy Windows 10, Cách Live Stream Trên Facebook Máy Tính, Laptop
Hình dưới đây cho thấy kết quả trong Excel – một chuỗi màu với mức độ từ màu xanh lá cây đến màu đỏ thông qua màu vàng.
Excel.Online