Khi sử dụng Google Sheet có các dữ liệu liên quan đến ngày/ tháng, khá nhiều người gặp hàm DATEDIF nhưng vẫn chưa cách áp dụng như thế nào. Để sử dụng hàm này hiệu quả hơn trong công việc, bạn hãy theo dõi bài viết sau để biết đây là hàm gì và công thức dùng hàm ra sao.
Hàm DATEDIF là hàm gì? Công thức dùng hàm?
Đây là một hàm trong Google Sheet dùng để tính toán mức chênh lệch giữa hai giá trị thời gian. Người dùng có thể sử dụng hàm này để tính toán khoảng chênh lệch giữa 2 số liệu được định dạng ngày/ tháng/ năm để cho ra kết quả là giá trị thực trong khoảng bao nhiêu ngày.
Công thức dùng hàm DATEDIF trong Google Sheet cụ thể như sau:
=DATEDIF(start_date, end_date, unit)
Trong công thức này:
- Start_date: Ngày bắt đầu trong phép tính. Dữ liệu đầu vào này phải được định dạng kiểu Date.
- End_date: Ngày kết thúc trong phép tính. Dữ liệu này cũng cần định dạng theo kiểu Date.
- Unit: Chữ viết tắt của đơn vị thời gian dành cho kết quả mà hàm trả về. Trường hợp này thì mọi người có thể chọn những cách hiển thị như sau:
- “D”: Hàm trả về kết quả được làm tròn lên theo ngày
- “M”: Hàm trả về kết quả được làm tròn lên theo tháng
- “Y”: Hàm trả về kết quả được làm tròn lên theo năm
- “MD”: Hàm trả về kết quả là mức chênh lệch theo ngày. Hàm bỏ qua yếu tố tháng, năm của ngày
- “YM”: Hàm trả về kết quả là mức chênh lệch theo tháng. Hàm bỏ qua yếu tố ngày, năm của tháng
- “YD”: Hàm trả về kết quả là mức chênh lệch theo ngày. Hàm bỏ qua yếu tố là năm của ngày
Những đối số ở trên đều là những đối số bắt buộc phải có trong hàm DATEDIF. Bên cạnh đó, người dùng nên chú ý khi dùng Unit với định dạng là “MD”. Tiêu chuẩn này sẽ mặc định 2 ngày này cùng tháng và cùng năm. Vì vậy mà có vài tình huống sẽ làm cho phép tính bị nhầm lẫn.
Cách dùng hàm DATEDIF tính chênh lệch giữa hai thời điểm trong Google Sheet
Sau đây là những ví dụ về cách dùng hàm DATEDIF để tính mức chênh lệch giữa hai ngày trong Google Sheet:
Ví dụ đếm hết các ngày
Ví dụ ta có bảng dữ liệu trên Google Sheet để quản lý đặt hàng như hình sau. Tất cả đơn đặt hàng này đã được cho xuất xưởng trong đầu tháng 8. Ngày bắt đầu vận chuyển được ký hiệu là Shipping Date trong bảng. Còn ngày giao hàng hay còn gọi là ngày đến hạn được ký hiệu là Due Date trong bảng.
Lúc này bạn phải tính số ngày chênh lệch giữa ngày bắt đầu vận chuyển và ngày tới hạn để xem mất bao lâu để đơn hàng tới nơi (số ngày chênh lệch này được ký hiệu là “D”). Sau đây là cách sử dụng hàm DATEDIF mà bạn nên nhập vào để tính toán:
=DATEDIF(B2,C2,”D”)
Bạn chỉ cần điền công thức trên vào ô D2 rồi sao chép vào các ô khác ở trong cột đó là có thể tính toán nhanh chóng cho những hàng ở dưới.
Chú ý: Bạn có thể tìm ra số ngày chênh lệch cho toàn cột cùng lúc với 1 công thức bằng hàm ARRAYFORMULA như sau:
=ArrayFormula(DATEDIF(B2:B13,C2:C13,”D”))
Ví dụ đếm ngày và loại trừ tháng
Nếu như mọi người muốn tính toán số ngày chênh lệch giữa vài tháng trong bảng dưới đây thì bạn hãy bỏ qua các tháng đã qua rồi dùng hàm DATEDIF để tính toán bằng đơn vị “MD” như sau:
=DATEDIF(A2, B2,”MD”)
Khi tính toán bằng công thức trên thì bạn sẽ tính toán được số ngày còn lại và loại trừ được những tháng đã qua.
Ví dụ đếm ngày và loại trừ năm
“YD” là đơn vị hỗ trợ tính toán khi giữa hai thời điểm cần tính toán có hơn 1 năm. Sau đây là hàm DATEDIF sẽ giúp bạn đếm ngày và bỏ qua năm:
=DATEDIF(A2,B2,”YD”)
Công thức trên sẽ giúp bạn trừ đi năm đầu. Tiếp theo thì công thức sẽ tính những ngày còn lại và xem những ngày đó ở trong cùng 1 năm.
Hướng dẫn đếm ngày làm việc trong Google Sheets
Trong một số tình huống khi người dùng chỉ cần đếm số ngày đã làm việc trong tại Google Sheets thì hàm DATEDIF không được áp dụng hiệu quả. Sau đây là một số ví dụ minh họa giúp bạn đếm số ngày làm việc bằng một số hàm hữu ích:
Ví dụ dùng hàm NETWORKDAYS để đếm ngày làm việc
Hàm đầu tiên giúp bạn đếm số ngày làm việc đó là hàm NETWORKDAYS. Công thức của hàm này sẽ giúp bạn đếm số lượng ngày làm việc khi đặt giữa 2 ngày và không tính cả thứ 7, Chủ Nhật, ngày lễ (nếu có). Ta có công thức sử dụng hàm như sau:
=NETWORKDAYS(start_date, end_date, [holidays])
Trong công thức:
- Start_date: Ngày khởi điểm. Nếu ngày khởi điểm không phải ngày được nghỉ, nó mặc định là ngày đi làm.
- End_date: Ngày làm điểm kết thúc. Nếu ngày làm điểm kết thúc không phải ngày được nghỉ, nó mặc định là ngày đi làm.
- Holidays: Tùy chọn giúp bạn đưa ra những ngày lễ. Bạn phải xác định nó là một phạm vi về ngày hay một số tượng trưng cho ngày/ tháng.
Để giúp bạn hình dung cụ thể về cách đếm ngày làm việc của hàm trên, bảng tính trên sẽ được bổ sung danh sách những ngày lễ xen giữa ngày vận chuyển và ngày tới hạn như sau:
Ta có cột B chính là cột của ngày khởi điểm và cột C là cột của ngày tới hạn. Những ngày được liệt kê ở cột E chính là ngày lễ cần được xem xét. Sau đây là cách áp dụng hàm NETWORKDAYS để đếm số ngày làm việc:
=NETWORKDAYS(B2,C2,$E$2:$E$4)
Lưu ý: Nếu như bạn muốn sao chép công thức hàm trên qua ô khác thì phải dùng tham chiếu ô tuyệt đối cho các ngày lễ để tránh nhận về kết quả không đúng. Hoặc bạn có thể cân nhắc việc sử dụng công thức mảng để tính toán chính xác hơn.
Ví dụ dùng hàm NETWORKDAYS.INTL để đếm ngày làm việc
Nếu lịch làm việc cuối tuần của bạn đã bị chỉnh thì không dùng được hàm DATEDIF để đếm số ngày đi làm. Mà bạn phải dùng đến hàm NETWORKDAYS.INTL. Hàm này giúp bạn được đếm số ngày đi làm nhanh chóng trên Google Sheets dựa theo những ngày quy định là cuối tuần do bạn tự đặt ra. Công thức sử dụng NETWORKDAYS.INTL như sau:
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
Trong công thức trên có những đối số như sau:
Start_Date và End_Date
- Start_Date: Ngày làm điểm bắt đầu.
- End_Date: Ngày làm điểm kết thúc.
Chú ý: Trong Google Sheet, hàm NETWORKDAYS.INTL cũng tính ngày làm điểm bắt đầu và ngày làm điểm kết thúc là ngày đi làm (ngoại trừ nếu hai ngày này là ngày lễ).
Weekend
Đây là tùy chọn, nếu như bạn bỏ qua thì thứ 7 và ngày chủ nhật được xem là các ngày nghỉ vào cuối tuần. Nếu cần thiết thì bạn có thể tùy chỉnh bằng việc dùng Masks và Numbers.
Masks
Đây là mẫu gồm 7 chữ số 0 và 1. Số 1 thể hiện 1 ngày cuối tuần, còn số 0 thể hiện 1 ngày làm việc. Số đầu tiên trong mẫu mặc định luôn là thứ 2, số cuối cùng trong mẫu mặc định là ngày Chủ nhật. Masks luôn được đặt trong dấu ngoặc kép khi các ngày nghỉ rải rác trong một tuần.
Ví dụ mẫu “1100110” có nghĩa là bạn làm việc vào các ngày thứ 4, thứ 5, thứ 6, thứ 7.
Numbers
Đây là những con số và bạn có thể dùng những số có một chữ số từ 1 đến 7 để thể hiện cho 1 cặp số ngày cuối tuần đã xác định. Mỗi số sẽ tương ứng với hai ngày cuối tuần như sau:
| Con số | Ngày cuối tuần |
| 1 | Thứ bảy, Chủ Nhật |
| 2 | Chủ nhật, thứ hai |
| 3 | Thứ hai, thứ ba |
| 4 | Thứ ba, thứ tư |
| 5 | Thứ tư, thứ năm |
| 6 | Thứ năm, thứ sáu |
| 7 | Thứ sáu, thứ bảy |
Hoặc bạn cũng có thể sử dụng những số có 2 chữ số từ 11 đến 17 để thể hiện 1 ngày nghỉ trong 1 tuần. Chi tiết từng số tương ứng với ngày cuối tuần được thể hiện ở bảng sau:
| Con số | Ngày cuối tuần |
| 11 | Chủ nhật |
| 12 | Thứ hai |
| 13 | Thứ ba |
| 14 | Thứ tư |
| 15 | Thứ năm |
| 16 | Thứ sáu |
| 17 | Thứ bảy |
Holiday
Đây là tham số có thể tùy chỉnh và được dùng để chỉ định cho ngày nghỉ. Chức năng này nghe có vẻ khá khó hiểu. Đầu tiên, bạn chỉ cần nắm rõ ngày nghỉ của mình. Sau đó bạn chỉ ra các ngày mà bạn quy địnhh là cuối tuần của mình. Nếu bạn chọn vào Masks thì dãy số sẽ là 1000001 và hàm NETWORKDAYS.INTL ở trường hợp này được thể hiện như sau:
=NETWORKDAYS.INTL(B2,C2,”1000001″)
Nếu bạn muốn mình có 2 ngày nghỉ cuối tuần kế tiếp nhau thì có thể dùng 1 số từ bảng trên, ví dụ như dùng số 2 thì được công thức như sau:
=NETWORKDAYS.INTL(B2, C2, 2)
Tiếp theo thì bạn chỉ cần bổ sung một đối số cuối để tham chiếu tới những ngày lễ đã được liệt kê tại cột E và hàm hoàn chỉnh thể hiện như sau:
=NETWORKDAYS.INTL(B2,C2,2,$E$2:$E$4)
Cách dùng hàm DATEDIF tính số tháng giữa 2 ngày trong Google Sheet
Nếu bạn muốn tính chênh lệch số tháng giữa hai ngày thì có thể dùng hàm DATEDIF để tính toán như những ví dụ sau đây:
Ví dụ tính toàn bộ số tháng giữa hai ngày
Trong bảng sau đây, Start_date là đối số đặt đầu tiên, sau đó đến End_date và ký hiệu “M” là đơn vị Months, được coi là đối số cuối cùng. Ta sẽ có công thức tính số tháng đầy đủ giữa 2 ngày như sau:
=DATEDIF(A2,B2,”M”)
Chú ý: Bạn có thể dùng hàm ARRAUFORMULA để đếm cùng lúc số tháng trên toàn bộ hàng như sau:
=ARRAYFORMULA(DATEDIF(A2:A13, B2:B13,”M”))
Ví dụ đếm số tháng và loại trừ năm
Bạn không cần tính số lượng tháng trong các năm giữa hai ngày khởi điểm và ngày tới hạn và công thức DATEDIF có thể giúp bạn tính toán nhanh chóng. Bạn chỉ cần dùng đơn vị là “YM” là công thức này tự động trừ đi năm trước. Tiếp đó sẽ tính số lượng tháng giữa 2 ngày với công thức hàm như sau:
=DATEDIF(A2,B2,”YM”)
Cách dùng hàm DATEDIF tính số lượng năm giữa 2 ngày trong Google Sheet
Cách dùng cuối cùng mà bạn cần tìm hiểu về hàm DATEDIF chính là sử dụng hàm để tính số năm chênh lệch giữa 2 ngày. Sau đây là ví dụ tính số lượng năm của những cặp đã cưới, căn cứ theo ngày cưới đến ngày hiện tại.
Trong trường hợp này thì chúng ta sẽ dùng đơn vị “Y” cho công thức hàm như sau:
=DATEDIF(A2, B2,”Y”)
Toàn bộ bài viết trên đã hướng dẫn cho mọi người cách sử dụng hàm DATEDIF để tính chênh lệch giữa 2 ngày trong Google Sheet. Hàm này sẽ giúp mọi người làm việc hiệu quả hơn với những bảng dữ liệu có ngày/ tháng/ năm và tính toán theo cách nhanh gọn nhất.
Tham khảo bài viết liên quan:
