Hàm DATEDIF & NETWORKDAYS: Chênh lệch ngày, tháng và năm

Bài đăng trên blog hôm nay là tất cả về việc tìm ra sự khác biệt giữa hai ngày trong Google Trang tính. Bạn sẽ thấy nhiều công thức hàm DATEDIF để đếm ngày, tháng và năm, đồng thời tìm hiểu cách NETWORKDAYS được sử dụng để chỉ tính ngày làm việc ngay cả khi ngày nghỉ của bạn dựa trên lịch trình tùy chỉnh.

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, có một vài chức năng tiện dụng và đơn giản cho mục đích đó. Hàm DATEDIF và NETWORKDAYS là một vài trong số đó.

Hàm DATEDIF trong Google Trang tính

Khi nó xảy ra với các chức năng, tên của chúng gợi ý hành động. Tương tự với DATEDIF. Nó phải được đọc là ngày dif, không phải ngày nếu, và nó là viết tắt của ngày khác biệt. Do đó, hàm DATEDIF trong Google Trang tính tính toán chênh lệch ngày giữa hai ngày.

Hãy chia nó thành nhiều mảnh. Hàm yêu cầu ba đối số:

= DATEDIF (ngày bắt đầu, ngày kết thúc, đơn vị)

  • start_date – một ngày được sử dụng làm điểm bắt đầu. Nó phải là một trong những điều sau:
    • một ngày chính nó trong dấu ngoặc kép: “13/8/2020”
    • tham chiếu đến ô có ngày tháng: A2
    • một công thức trả về một ngày: DATE (2020, 8, 13)
    • một số đại diện cho một ngày cụ thể và có thể được Google Trang tính hiểu là ngày tháng, ví dụ: 44056 đại diện Ngày 13 tháng 8 năm 2020.
  • ngày cuối – 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.
  • đơn vị – được sử dụng để cho hàm này biết sự khác biệt cần trả về. Đây là danh sách đầy đủ các đơn vị bạn có thể sử dụng:
    • “D” – (viết tắt của ngày) trả về số ngày giữa hai ngày.
    • “M” – (tháng) số tháng đầy đủ giữa hai ngày.
    • “Y” – (năm) số năm đầy đủ.
    • “MD” – (số ngày bỏ qua tháng) số ngày sau khi trừ đi cả tháng.
    • “YD” – (số ngày bỏ qua năm) số ngày sau khi trừ đi cả năm.
    • “YM” – (tháng bỏ qua năm) số tháng đầy đủ sau khi trừ đi các năm đầy đủ.

Ghi chú. 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 Trang tính.

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

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

Tôi có một bảng nhỏ để 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 – sẽ là ngày bắt đầu của tôi. Cũng có một ngày giao hàng gần đúng – Ngày đáo hạn.

Một bàn nhỏ với các đơn đặt hàng.

Tôi sẽ tính ngày – “D” – giữa ngày giao hàng 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 tôi nên sử dụng:

=DATEDIF(B2, C2, "D")

Tính số ngày giữa hai ngày với hàm DATEDIF trong Google Trang tính.

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

Tiền boa. Bạn luôn có thể tính toán toàn bộ cột cùng một lúc với một công thức duy nhất bằng cách sử dụng ARRAYFORMULA:

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

Công thức mảng để tính số ngày giữa hai ngày trong Google Trang tính.

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

Hãy tưởng tượng có một vài tháng giữa hai ngày:

Có bao nhiêu tháng giữa các ngày?

Làm thế nào để bạn chỉ đếm ngày như thể chúng thuộc cùng một tháng? Đúng vậy: bằng cách bỏ qua những tháng đầy đủ đã trôi qua. Hàm DATEDIF tính toán điều này tự động khi bạn sử dụng “MD” đơn vị:

=DATEDIF(A2, B2, "MD")

Tính số ngày giữa hai ngày trong Google Trang tính sau khi trừ các tháng.

Hàm trừ các tháng đã trôi 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 – “YD” – sẽ hỗ trợ khi các ngày có hơn một năm giữa chúng:

=DATEDIF(A2, B2, "YD")

Trừ năm và tính số ngày còn lại giữa hai ngày trong Google Trang tính.

Công thức sẽ trừ năm đầu tiên, sau đó tính các ngày còn lại như thể chúng thuộc cùng một năm.

Đếm ngày làm việc trong Google Trang tính

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 Trang tính. Các công thức DATEDIF sẽ không hữu ích nhiều ở đây. Và tôi tin rằng 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à phương án thanh lịch nhất.

May mắn thay, Google Trang tính có một vài phép thuật không quá ảo diệu cho điều đó 🙂

Ví dụ 1. Hàm NETWORKDAYS

Cái đầu tiên có tên là NETWORKDAYS. Hàm này 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])

  • start_date – một ngày được sử dụng làm điểm bắt đầu. Cần thiết.Ghi chú. Nếu ngày này không phải là ngày nghỉ thì được tính là ngày làm việc.
  • ngày cuối – ngày được sử dụng làm điểm cuối. Cần thiết.Ghi chú. Nếu ngày này không phải là ngày nghỉ thì được tính là ngày làm việc.
  • ngày lễ – 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, 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:

Một bảng đơn đặt hàng và ngày lễ của Hoa Kỳ.

Vì vậy, cột B là ngày bắt đầu của tôi, cột C – 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. Đây là cách công thức sẽ trông như thế nào:

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

Đếm ngày làm việc trong Google Trang tính.

Bạn có nhận thấy số ngày giảm như thế nào so với công thức DATEDIF không? Vì lúc này chức năng 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.

Ghi chú. Không giống như Hàm DATEDIF trong Google Trang tính, NETWORKDAYS tính ngày bắt đầuend_day như ngày làm việc trừ khi chúng là ngày nghỉ. Do đó, D7 trả về 1.

Ví dụ 2. NETWORKDAYS.INTL cho Google Trang tính

Nếu bạn có lịch trình cuối tuần tùy chỉnh, bạn sẽ được hưởng lợi từ một chức năng khác: NETWORKDAYS.INTL. Nó cho phép bạn tính số ngày làm việc trong Google Trang tính dựa trên các ngày cuối tuần do cá nhân đặt:

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

Chức năng này có vẻ phức tạp vì tất cả những con số đó, nhưng tôi khuyến khích 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. Hãy làm cho nó chủ nhậtThứ hai. Sau đó, quyết định cách để chỉ ra những ngày cuối tuần của bạn.

Nếu bạn đeo mặt nạ, nó sẽ như thế này – 1000001:

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

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

=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)

Đặt các ngày cuối tuần tùy chỉnh của bạn bằng cách sử dụng NETWORKDAYS.INTL trong Google Trang tính.

Google Trang tính 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à bạn muốn nhận được chênh lệch ngày theo tháng hơn là ngày, hãy để Google Sheets DATEDIF thực hiện công việc.

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

Mũi khoan cũng vậy: start_date đi đầu tiên, tiếp theo là ngày cuối“M” – đó là viết tắt của nhiều tháng – như một lý lẽ cuối cùng:

=DATEDIF(A2, B2, "M")

Hàm Datedif tìm ra số tháng giữa hai ngày.

Tiền boa. Đừng quên về ARRAUFORMULA chức năng 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. 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 “YM” đơn vị và công thức sẽ trừ nguyên năm trước, sau đó đếm số tháng giữa các ngày:

=DATEDIF(A2, B2, "YM")

Datedif Tính số tháng giữa hai ngày bỏ qua số năm đã trôi qua.

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

Đ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 Trang tính DATEDIF tính toán chênh lệch ngày tháng theo năm.

Tôi sẽ tính số năm 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 hôm nay:

Đã bao nhiêu năm trôi qua kể từ đám cưới của họ?

Như bạn có thể đã đoán, tôi sẽ sử dụng “Y” đơn vị cho điều đó:

=DATEDIF(A2, B2, "Y")

Hàm Datedif Tính toán chênh lệch ngày trong các năm đầy đủ.

Tất cả các công thức 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 Trang tính.

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 nếu bạn có bất kỳ câu hỏi nào, tôi khuyến khích bạn chia sẻ chúng với chúng tôi trong phần bình luận bên dưới.

Source link

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *