Trong bài viết này, chúng ta sẽ tìm hiểu về hàm DATEDIFF trong Google Sheets Query và cách sử dụng nó để tìm hiểu sự khác biệt giữa hai ngày hoặc giá trị timestamp.
Giới thiệu về hàm DATEDIFF trong Google Sheets Query
DATEDIFF là một trong các hàm scalar trong Google Sheets Query, trả về một giá trị cho mỗi hàng dữ liệu, không tổng hợp dữ liệu.
Có ba loại hàm và toán tử trong Google Sheets Query để thực hiện các thao tác trên dữ liệu: Các hàm tổng hợp, các hàm scalar và các toán tử toán học. Hàm DATEDIFF thuộc vào loại đầu tiên.
Để tìm hiểu về hàm DATEDIF trong Google Sheets, bạn có thể tham khảo hướng dẫn về các hàm ngày tháng.
Hàm DATEDIFF trong Query (chú ý có hai chữ F) trả về sự khác biệt ngày giữa hai ngày hoặc giá trị timestamp.
Ví dụ về hàm DATEDIFF trong Google Sheets Query
Đầu tiên, chúng ta sẽ tìm hiểu cách sử dụng hàm scalar DATEDIFF trong Google Sheets Query để tìm sự khác biệt ngày giữa hai ngày.
Sau đó, bạn có thể thử nghiệm với một số ví dụ thực tế như:
- Làm thế nào để tìm ngày sinh sắp tới bằng Query?
- Làm thế nào để trích xuất các hợp đồng hết hạn trong vòng một tháng, 30 ngày hoặc một số ngày cụ thể?
1. Tìm số ngày giữa hai ngày trong Query
Cú pháp:
dateDiff(ngày kết thúc, ngày bắt đầu)
Chúng ta có thể sử dụng hàm scalar này trong một cột ngày hoặc DateTime.
Công thức sẽ trả về các giá trị số nguyên vì nó cắt bỏ giá trị thời gian trước khi tính toán.
Ví dụ:
=QUERY(A1:B,"Select A,B,dateDiff(B,A)")Bạn cũng có thể chỉ sử dụng =QUERY(A1:B,"Select dateDiff(B,A)") để trả về một cột với số ngày.
Sự khác biệt giữa DATEDIFF trong Query và hàm DATEDIF trong Worksheet
Hàm DATEDIF là một hàm độc lập trong worksheet, trong khi DATEDIFF là một hàm scalar được sử dụng trong Query.
DATEDIF có nhiều tính năng. Chúng ta chỉ xem xét cách các công thức khác nhau trong việc trả về sự khác biệt trong số ngày giữa hai ngày hoặc giá trị DateTime.
Nếu tôi sử dụng hàm worksheet trong ô D1, công thức phải như sau:
=ArrayFormula(datedif(A2:A5,B2:B5,"D"))Ví dụ dưới đây sẽ cho thấy cách khác nhau giữa DATEDIFF và DATEDIF trong cú pháp.
datedif(ngày bắt đầu, ngày kết thúc, đơn vị)Sự khác biệt trên liên quan đến cú pháp, nhưng sự khác biệt sau liên quan đến tính toán.
Nếu chúng ta vô tình chỉ định một ngày bắt đầu lớn hơn ngày kết thúc, hàm scalar sẽ trả về một số âm, trong khi hàm worksheet sẽ trả về lỗi #NUM.
Ví dụ về việc sử dụng hàm DATEDIFF trong Google Sheets Query trong cuộc sống thực
Xin vui lòng xem qua hai ví dụ dưới đây.
1. Tìm ngày sinh sắp tới bằng công thức Query
Ở đây, dữ liệu mẫu của chúng ta như sau.
Cột A chứa ngày sinh của một số người. Tên của họ nằm trong cột B.
=ArrayFormula(QUERY({A2:B,{(date(year(today()),month(A2:A),day(A2:A)))}},"select Col1,Col2 where Col2<>'' and dateDiff(Col3,now())=30"))Tôi đã sử dụng công thức Query trên ô D2.
Nó trả về ngày sinh và tên của những người có sinh nhật trong 30 ngày kể từ ngày hôm nay.
Giải thích công thức
Ví dụ này có hai cột: DOB (A) và Name (B).
Chúng ta đã thêm một cột thứ ba ảo, trích xuất tháng và ngày từ cột A và thêm năm hiện tại để tạo thành một ngày.
Ví dụ, DOB của người trong ô A2 là 1/30/1980.
Với công thức dưới đây, chúng ta chỉ thay đổi phần năm của ngày này.
=date(year(today()),month(A2),day(A2))Tôi đã sử dụng hàm DATEDIFF trong công thức Query để tìm số ngày giữa ngày hôm nay với ngày mới được tạo ra này.
datediff(Col3,now())
Nếu kết quả là 30, Query sẽ chọn những ngày sinh và tên đó, nếu không, nó sẽ trả về lỗi #N/A.
2. Trích xuất những hợp đồng hết hạn trong vòng một tháng, 30 ngày hoặc N ngày
Nếu bạn hiểu ví dụ trên, việc giải quyết vấn đề này dễ dàng.
Đây là cách sử dụng hàm DATEDIFF trong công thức Query.
=QUERY({A2:B},"select Col1,Col2 where Col2<>'' and dateDiff(Col1,now())=30")Bạn có thể thay đổi =30 thành >30 hoặc một số khác để đặt một thời hạn khác nhau.
Mẹo bổ sung
Ở trên, tôi đã sử dụng hàm NOW() để trả về ngày hôm nay. Nó được cố định cứng trong công thức.
Nếu nó nằm trong một ô, ví dụ, trong ô C1, hãy sử dụng công thức dưới đây.
=QUERY({A2:B},"select Col1,Col2 where Col2<>'' and dateDiff(Col1,date '"&TEXT(C1,"yyyy-mm-dd")&"')=30")Liên quan: Cách sử dụng tiêu chí ngày trong hàm Query trong Google Sheets.
Đó là tất cả về việc sử dụng hàm DATEDIFF trong Google Sheets Query.
