Hướng dẫn cách sử dụng hàm DATEDIF trong Google Sheets và Excel

Rất nhiều người dùng bảng tính cảm thấy ngày tháng khó hiểu, nếu không muốn nói là cực kỳ khó xử lý. Nhưng bạn có tin hay không, Goolge Sheets có một vài hàm rất tiện dụng và đơn giản cho nhiệm vụ này và đó là hàm DATEDIF và NETWORKDAYS. 

Hàm DATEDIF trong Google Sheets

Như tên gọi của hàm, DATEDIF trong Google Sheets dùng để tính toán chênh lệch ngày giữa hai ngày. Hàm này yêu cầu ba đối số với công thức tổng quát như sau:

=DATEDIF(start_date, end_date, unit)

Trong đó: 

• Start_date: Là ngày được sử dụng làm điểm bắt đầu và nó phải là một trong những điều sau:

        - Ngày phải đặt trong dấu ngoặc kép: "8/13/2020".
 

        - Tham chiếu đến ô có ngày: A2

        - Công thức trả về ngày: DATE (2020, 8, 13)

      - Một số đại diện cho một ngày cụ thể và có thể được Google Sheets hiểu là ngày, ví dụ: 44056 đại diện cho ngày 13 tháng 8 năm 2020 .

• End_date: Một ngày được sử dụng làm điểm cuối. Nó phải có cùng định dạng với start_date.
• Unit: Được sử dụng để cho hàm này biết sự khác biệt cần trả về. Dưới đây là danh sách đầy đủ các đơn vị bạn có thể sử dụng:

       - "D" - (Viết tắt của Date) trả về số ngày giữa hai ngày.

       - "M" - (Months) số tháng đầy đủ giữa hai ngày.

       - "Y" - (Year) số năm đầy đủ.

       - "MD" - (Days ignoring Months): Số ngày sau khi trừ đi cả tháng.

       - "YD" - (Days ignoring Years): Số ngày sau khi trừ đi cả năm.

       - "YM" - (Months ignoring years): Số tháng đầy đủ sau khi trừ đi các năm đầy đủ.

Lưu ý: Tất cả các đơn vị phải được đặt vào công thức giống như cách chúng xuất hiện ở trên - trong dấu ngoặc kép.

Bây giờ, hãy ghép tất cả các phần này lại với nhau và xem công thức DATEDIF hoạt động như thế nào trong Google Sheets.

Xem thêm: Cách cộng trừ ngày tháng năm trong Excel chuẩn

Tính số ngày giữa hai ngày trong Google Sheets

Ví dụ 1. Đếm tất cả các ngày

Bạn có một bảng dữ liệu Goolge Sheets để theo dõi một số đơn đặt hàng. Tất cả chúng đã được xuất xưởng vào nửa đầu tháng 8 - Ngày vận chuyển (Shipping date) sẽ là ngày bắt đầu. Ngoài ra còn có một ngày giao hàng gần đúng - Ngày đến hạn (Due date).

Bây giờ bạn cần tính số ngày - "D" - giữa ngày vận chuyển và ngày đến hạn để xem mất bao lâu để các mặt hàng đến nơi. Đây là công thức nên sử dụng:

=DATEDIF(B2,C2,"D")

Bạn chỉ cần nhập công thức DATEDIF vào ô D2 và sau đó sao chép xuống các ô khác trong cột để áp dụng cho các hàng khác.

Lưu ý: Bạn cũng có thể tính toán toàn bộ cột cùng một lúc bằng một công thức duy nhất thông qua hàm ARRAYFORMULA:

=ArrayFormula(DATEDIF(B2:B13,C2:C13,"D"))

Xem thêm: Khóa học Google Sheets online

Ví dụ 2. Đếm ngày bỏ qua tháng

Nếu bạn muốn tính số ngày giữa vài tháng như trong bảng dữ liệu dưới đây. Vậy làm thế nào để bạn chỉ đếm ngày như thể chúng thuộc cùng một tháng?

Bằng cách bỏ qua những tháng đầy đủ đã trôi qua. DATEDIF tính toán điều này tự động khi bạn sử dụng đơn vị "MD" :

=DATEDIF(A2, B2,"MD")

Như vậy hàm DATEDIF sẽ trừ các tháng đã qua và đếm số ngày còn lại.

Ví dụ 3. Đếm ngày bỏ qua năm

Một đơn vị khác là "YD" sẽ hỗ trợ khi các ngày có hơn một năm giữa chúng:

=DATEDIF(A2,B2,"YD")

Công thức trên của hàm DATEDIF sẽ trừ năm đầu tiên, sau đó tính các ngày còn lại giống như các ngày đó trong cùng một năm.

Đếm ngày làm việc trong Google Sheets

Có một trường hợp đặc biệt khi bạn chỉ cần tính ngày làm việc trong Google Sheets. Hàm DATEDIF sẽ không hữu ích nhiều ở đây. Và bạn sẽ đồng ý rằng trừ các ngày cuối tuần theo cách thủ công không phải là cách nhanh nhất. Tuy nhiên Google Sheets có một vài cách để giúp bạn làm điều đó nhanh hơn.

Ví dụ 1. Hàm NETWORKDAYS

Hàm đầu tiên mà bạn có thể sử dụng là NETWORKDAYS. Hàm này sẽ tính toán số ngày làm việc giữa hai ngày không bao gồm cuối tuần (thứ bảy và chủ nhật) và thậm chí cả ngày lễ nếu cần:

=NETWORKDAYS(start_date, end_date, [holidays]) 

Trong đó:

•  Start_date: Ngày được sử dụng làm điểm bắt đầu. Cần thiết.

Lưu ý: Nếu ngày này không phải là ngày nghỉ thì nó được tính là ngày làm việc.

•  End_date: Một ngày được sử dụng làm điểm cuối. Cần thiết.

Lưu ý: Nếu ngày này không phải là ngày nghỉ thì nó được tính là ngày làm việc.

•  Holidays: Cái này là tùy chọn khi bạn cần chỉ ra các ngày lễ cụ thể. Nó phải là một phạm vi ngày hoặc số đại diện cho ngày tháng.

Để minh họa cách hoạt động của hàm trên, chúng tôi sẽ thêm danh sách các ngày lễ diễn ra giữa ngày giao hàng và ngày đến hạn:

Vì vậy, cột B là ngày bắt đầu, cột C là ngày kết thúc. Các ngày trong cột E là những ngày nghỉ cần xem xét. Dưới đây sẽ là cách công thức làm việc:

=NETWORKDAYS(B2,C2,$E$2:$E$4)

Mẹo: Nếu bạn định sao chép công thức sang các ô khác, hãy sử dụng tham chiếu ô tuyệt đối cho ngày lễ để tránh lỗi hoặc kết quả không chính xác. Hoặc xem xét việc xây dựng một công thức mảng.

Từ kết quả trên bạn có thể thấy số ngày giảm so với sử dụng công thức DATEDIF ở trên. Điều này là vì lúc này hàm NETWORKDAYS sẽ tự động trừ đi tất cả các ngày thứ bảy, chủ nhật và hai ngày lễ diễn ra vào thứ sáu và thứ hai.

Lưu ý: Không giống như hàm DATEDIF trong Google Sheets, hàm NETWORKDAYS sẽ tính Start_day và End_day là ngày làm việc trừ khi chúng là ngày lễ. Do đó, D7 trả về 1.

Ví dụ 2. NETWORKDAYS.INTL cho Google Sheets

Nếu bạn có lịch làm việc cuối tuần tùy chỉnh, trong trường hợp này bạn sẽ cần sử dụng hàm NETWORKDAYS.INTL. Nó cho phép bạn tính số ngày làm việc trong Google Sheets dựa trên các ngày cuối tuần do cá nhân đặt. Công thức tổng quát của hàm này như sau:

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Trong đó:

• Start_date: Ngày được sử dụng làm điểm bắt đầu. Cần thiết.
• End_date: Một ngày được sử dụng làm điểm cuối. Cần thiết.

Lưu ý: Hàm NETWORKDAYS.INTL trong Google Sheets cũng tính start_day và end_day là ngày làm việc trừ khi chúng là ngày lễ.

• Weekend: Cái này là tùy chọn. Nếu bỏ qua, thứ bảy và chủ nhật được coi là ngày nghỉ cuối tuần. Nhưng bạn có thể thay đổi điều đó bằng hai cách là Masks và Numbers.
• Masks: là một mẫu gồm bảy chữ số gồm 1 và 0. 1 là viết tắt của một ngày cuối tuần, 0 cho một ngày làm việc. Chữ số đầu tiên trong mẫu luôn là thứ Hai, chữ số cuối cùng sẽ là Chủ nhật.

Ví dụ: "1100110" có nghĩa là bạn làm việc vào Thứ Tư, Thứ Năm, Thứ Sáu và Thứ Bảy.

Lưu ý: Masks phải được đặt trong dấu ngoặc kép và cách này là hoàn hảo cho những ngày nghỉ của bạn rải rác trong tuần.

• Numbers: Là các con số và bạn có thể sử dụng các số có một chữ số (1-7) biểu thị một cặp số cuối tuần đã định. Chi tiết các số tương ứng theo bảng dưới đây.

Hoặc làm việc với các số có hai chữ số (11-17) biểu thị một ngày nghỉ trong vòng một tuần. Chi tiết tương ứng theo bảng dưới đây.
 

• Holidays: Là tham số tùy chọn và được sử dụng để chỉ định ngày nghỉ.
   

Chức năng này có vẻ phức tạp vì tất cả những con số đó, nhưng lời khuyên là bạn nên thử. Trước tiên, chỉ cần hiểu rõ về những ngày nghỉ của bạn. Sau đó, quyết định cách để chỉ ra những ngày cuối tuần của bạn.

Nếu bạn chọn Masks thì dãy số sẽ có cấu tạo là 1000001, nên hàm NETWORKDAYS.INTL trong trường hợp này sẽ là:

=NETWORKDAYS.INTL(B2,C2,"1000001")

Nhưng vì bạn muốn có hai ngày cuối tuần liên tiếp nên có thể sử dụng một số từ các bảng trên, 2 trong trường hợp này:

=NETWORKDAYS.INTL(B2, C2, 2)

Sau đó, chỉ cần thêm đối số cuối cùng - tham chiếu đến các ngày lễ trong cột E và công thức đã sẵn sàng:

=NETWORKDAYS.INTL(B2,C2,2,$E$2:$E$4)

Google Sheets và sự khác biệt về ngày tháng

Đôi khi vài tháng quan trọng hơn ngày. Nếu điều này đúng với bạn và muốn nhận được chênh lệch ngày theo tháng hơn là ngày, hãy để Google Sheets với hàm DATEDIF thực hiện công việc.

Ví dụ 1. Tính số tháng đầy đủ giữa hai ngày

Cách thực hiện cũng giống như vậy: Start_date là đối số đầu tiên, tiếp theo là end_date và "M" - viết tắt của Months như một đối số cuối cùng:

=DATEDIF(A2,B2,"M")

Mẹo: Đừng quên về hàm ARRAUFORMULA có thể giúp bạn đếm tháng trên tất cả các hàng cùng một lúc:

=ARRAYFORMULA(DATEDIF(A2:A13, B2:B13,"M"))

Ví dụ 2. Tính số tháng bỏ qua năm

Bạn có thể không cần tính số tháng trong suốt tất cả các năm giữa ngày bắt đầu và ngày kết thúc. Và hàm DATEDIF cho phép bạn làm điều đó.

Chỉ cần sử dụng đơn vị "YM" và công thức sẽ trừ nguyên năm trước, sau đó đếm số tháng giữa các ngày với công thức sau:

=DATEDIF(A2,B2,"YM")

Tính số năm giữa hai ngày trong Google Sheets

Điều cuối cùng (nhưng không kém phần quan trọng) cho bạn thấy là cách Google Sheets sử dụng hàm DATEDIF để tính toán chênh lệch ngày tháng theo năm.

Ví dụ tính số năm của các cặp vợ chồng đã kết hôn dựa trên ngày cưới của họ và ngày hiện tại:

Như bạn có thể đã đoán ra, trong trường hợp này sẽ sử dụng đơn vị "Y" cho điều đó với công thức sau:

=DATEDIF(A2, B2,"Y")

Tất cả các công thức sử dụng hàm DATEDIF này là công thức đầu tiên cần thử khi tính toán ngày, tháng và năm giữa hai ngày trong Google Sheets.

Nếu trường hợp của bạn không thể giải quyết được bằng những cách này hoặc trong quá trình sử dụng bạn có bất kỳ câu hỏi nào hãy để lại comment ở phía dưới để chúng tôi có thể giúp bạn. 

Thời đại công nghệ 4.0 đang dần đi vào cuộc sống đòi hỏi mọi người phải tự trang bị kiến thức tin học cho phù hợp để có thể bắt kịp nhưng thay đổi nhanh chóng này. Chẳng ai khác ngoài bạn hiểu mình cần trang bị thêm kiến thức gì. Hãy tìm hiểu ngay TẠI ĐÂY những khóa học hấp dẫn, có tính thực tế cao của Gitiho và đăng ký nhận tư vấn ngay hôm nay.