Thời gian đọc ước tính: 10 phút
Những điểm chính
- Cơ chế lưu trữ: Excel xử lý ngày tháng dưới dạng chuỗi số, cho phép thực hiện các phép toán cộng trừ trực tiếp.
- Tính năng Paste Special: Phương pháp nhanh nhất để thay đổi hàng loạt dữ liệu ngày tháng mà không cần tạo cột phụ.
- Hàm số học cơ bản: Sử dụng toán tử cộng (+) hoặc trừ (-) để tính toán ngày đến hạn hoặc ngày trong quá khứ một cách linh hoạt.
- Hàm WORKDAY & WORKDAY.INTL: Giải pháp tối ưu để tính toán ngày làm việc, tự động loại bỏ ngày cuối tuần và ngày lễ.
Microsoft Excel không lưu trữ ngày tháng dưới dạng văn bản thông thường mà quản lý chúng như các chuỗi số liên tiếp. Cơ chế này mang lại lợi thế lớn cho người dùng khi cần thực hiện các phép tính thời gian. Bạn có thể dễ dàng cộng thêm số ngày vào một mốc thời gian cụ thể hoặc trừ đi để tìm ra khoảng cách giữa hai thời điểm.
Bài viết này sẽ hướng dẫn chi tiết ba phương pháp chính để cộng hoặc trừ ngày trong Excel, từ các thao tác cơ bản đến việc sử dụng hàm nâng cao cho môi trường doanh nghiệp.
Cộng Hoặc Trừ Ngày Bằng Tính Năng Paste Special
Trong môi trường làm việc thực tế, đôi khi bạn nhận được một tập dữ liệu và cần điều chỉnh toàn bộ mốc thời gian do thay đổi kế hoạch. Ví dụ, một dự án bị hoãn lại và bạn cần dời tất cả “ngày đến hạn” (Deadline) thêm 10 ngày.
Thay vì sửa thủ công từng ô hoặc lập công thức ở cột mới rồi sao chép lại, tính năng Paste Special là giải pháp tối ưu nhất cho tình huống này. Đây là kỹ thuật giúp bạn thay đổi trực tiếp giá trị của ô gốc.
Danh sách dữ liệu ngày tháng cần được cộng thêm ngày
Dưới đây là quy trình chi tiết để cộng thêm 10 ngày vào danh sách ngày hiện có:
- Nhập số 10 (số ngày cần cộng thêm) vào một ô trống bất kỳ trên bảng tính.
Nhập số ngày cần cộng vào một ô trống - Sao chép ô chứa số 10 này (nhấn
Ctrl + C). - Bôi đen (chọn) toàn bộ vùng dữ liệu chứa các ngày tháng cần thay đổi.
Chọn vùng dữ liệu chứa ngày tháng cần xử lý - Nhấp chuột phải vào vùng đã chọn.
- Chọn mục Paste Special (Dán đặc biệt) trong danh sách tùy chọn. Hộp thoại Paste Special sẽ xuất hiện.
Chọn tính năng Paste Special từ menu chuột phải - Tại phần Paste, tích chọn vào Values (Giá trị). Điều này cực kỳ quan trọng để giữ nguyên định dạng ngày tháng của ô đích.
Chọn tùy chọn Values trong hộp thoại - Tại phần Operation, tích chọn Add (Cộng).
Chọn hành động Add để thực hiện phép cộng - Nhấn OK để hoàn tất.
Nhấn OK để áp dụng thay đổi
Kết quả là toàn bộ các ngày trong vùng chọn sẽ được cộng thêm đúng 10 ngày.
Kết quả cuối cùng sau khi cộng thêm ngày vào dữ liệu gốc
Để thực hiện phép trừ (ví dụ: lùi hạn chót lại 10 ngày), bạn chỉ cần chọn Subtract ở Bước 7 thay vì Add.
Lưu ý quan trọng về định dạng:
Việc chọn Values ở Bước 6 là bắt buộc. Nếu bạn bỏ qua bước này và để mặc định là “All”, Excel sẽ áp dụng định dạng của ô chứa số 10 (thường là định dạng General hoặc Number) lên các ô ngày tháng. Điều này sẽ biến các ngày của bạn thành các con số sê-ri khó hiểu (ví dụ: 44105 thay vì 01/10/2020).
Phương pháp Paste Special phù hợp nhất cho các thay đổi “tĩnh” – nghĩa là khi bạn muốn thay đổi dữ liệu gốc một lần và không cần nó tự động cập nhật sau này. Nếu bạn cần tính linh hoạt cao hơn, hãy xem xét phương pháp sử dụng công thức dưới đây.
Đọc thêm: Cách thêm tuần vào ngày trong Excel để mở rộng kỹ năng xử lý thời gian của bạn.
Sử Dụng Công Thức Để Cộng Trừ Ngày
Công thức Excel mang lại sự linh hoạt vượt trội so với Paste Special. Phương pháp này cho phép bạn tạo ra các dữ liệu động, tự động cập nhật khi ngày gốc thay đổi, đồng thời giữ nguyên vẹn dữ liệu gốc để đối chiếu.
Cộng Một Số Ngày Cố Định
Giả sử bạn có danh sách ngày ký hợp đồng và quy định công ty là ngày thanh toán sẽ diễn ra sau đó 10 ngày. Bạn cần một cột mới để tính “Ngày thanh toán”.
Thêm số ngày cố định bằng công thức đơn giản
Công thức thực hiện rất đơn giản:
=B2 + 10
Minh họa công thức cộng thêm số ngày cố định
Nguyên lý hoạt động dựa trên hệ thống số sê-ri của Excel. Ví dụ, ngày 01/01/1900 được tính là số 1. Mỗi ngày trôi qua sẽ cộng thêm 1 đơn vị. Do đó, việc cộng số 10 vào ô chứa ngày tháng thực chất là yêu cầu Excel tịnh tiến thêm 10 đơn vị thời gian.
Sau khi nhập công thức cho ô đầu tiên, bạn có thể sử dụng tính năng AutoFill (kéo thả chuột tại góc dưới bên phải ô) để áp dụng cho toàn bộ danh sách.
Cộng Số Ngày Biến Đổi Theo Dữ Liệu
Trong thực tế, không phải lúc nào số ngày cần cộng thêm cũng giống nhau. Ví dụ: Thời hạn giao hàng (Shipping Time) có thể khác nhau tùy thuộc vào địa điểm của khách hàng – 5 ngày, 10 ngày hoặc 15 ngày.
Lúc này, bạn nên thiết lập một “Cột phụ” (Helper Column) chứa số ngày cần cộng cho từng dòng tương ứng.
Bảng dữ liệu với số ngày cần cộng khác nhau ở cột C
Công thức sẽ tham chiếu đến địa chỉ ô thay vì một số cụ thể:
=B2 + C2
Trong đó:
- B2: Chứa ngày bắt đầu.
- C2: Chứa số ngày cần cộng thêm.
Công thức cộng hai ô chứa ngày và số ngày
Nếu muốn trừ lùi ngày (ví dụ: tính ngày phải đặt hàng để kịp ngày giao), bạn chỉ cần thay dấu cộng (+) bằng dấu trừ (-): =B2 - C2.
Xử lý lỗi hiển thị:
Đôi khi kết quả trả về là một dãy số (như 43891) thay vì ngày tháng. Đây không phải lỗi tính toán mà là vấn đề định dạng. Bạn chỉ cần chọn ô đó, vào tab Home, tại mục Number Format chọn Short Date hoặc nhấn phím tắt Ctrl + Shift + #.
Tính Toán Chỉ Trên Ngày Làm Việc (Bỏ Qua Cuối Tuần)
Các phép tính cộng trừ số học thông thường (như B2 + 10) sẽ tính cả thứ Bảy và Chủ Nhật. Tuy nhiên, trong quản lý dự án hoặc tính lương, chúng ta thường chỉ quan tâm đến ngày làm việc (Working Days). Excel cung cấp hai hàm chuyên dụng cho nhu cầu này.
Hàm WORKDAY: Bỏ Qua Thứ 7 và Chủ Nhật
Giả sử bạn có ngày bắt đầu dự án và dự kiến hoàn thành sau 15 ngày làm việc. Nếu dùng phép cộng thường, ngày kết thúc có thể rơi vào Chủ Nhật. Hàm WORKDAY sẽ tự động “nhảy” qua các ngày cuối tuần để trả về ngày kết thúc thực tế.
Sử dụng hàm WORKDAY để tính ngày làm việc
Cấu trúc hàm như sau:
=WORKDAY(B2, C2)
Minh họa hàm WORKDAY chỉ tính ngày trong tuần
Các tham số của hàm WORKDAY:
- Start_date (B2): Ngày bắt đầu.
- Days (C2): Số ngày làm việc cần cộng thêm (hoặc trừ đi nếu là số âm).
- [Holidays] (Tùy chọn): Danh sách các ngày lễ (như 30/4, 1/5, Quốc khánh) cần được bỏ qua. Bạn có thể chọn một vùng ô chứa các ngày lễ này.
Ví dụ trên sẽ tính ra ngày kết thúc sau khi đã loại bỏ toàn bộ các ngày Thứ Bảy và Chủ Nhật nằm trong khoảng thời gian đó.
Hàm WORKDAY.INTL: Tùy Chỉnh Ngày Nghỉ Cuối Tuần
Tại Việt Nam, nhiều doanh nghiệp làm việc cả ngày thứ Bảy, hoặc chỉ nghỉ Chủ Nhật. Hàm WORKDAY mặc định (nghỉ T7, CN) sẽ không chính xác trong trường hợp này. Bạn cần sử dụng hàm nâng cấp hơn là WORKDAY.INTL (International).
Công thức cho trường hợp chỉ nghỉ Chủ Nhật:
=WORKDAY.INTL(B2, C2, 11)
Hoặc trường hợp nghỉ Thứ 6 và Thứ 7 (phổ biến ở một số quốc gia Trung Đông):
=WORKDAY.INTL(B2, C2, 7)
Các tham số tùy chọn ngày nghỉ của hàm WORKDAY.INTL
Giải thích tham số thứ 3 (Weekend):
Hàm WORKDAY.INTL cho phép bạn quy định ngày nào là cuối tuần thông qua một mã số:
- 1: Thứ Bảy, Chủ Nhật (Mặc định).
- 7: Thứ Sáu, Thứ Bảy.
- 11: Chỉ Chủ Nhật.
- 17: Chỉ Thứ Bảy.
Sự linh hoạt này giúp WORKDAY.INTL trở thành công cụ không thể thiếu cho các kế toán viên và quản lý nhân sự tại các công ty có lịch làm việc đặc thù.
Mẹo nhỏ: Nếu bạn cần lùi ngày làm việc (ví dụ: tìm ngày bắt đầu để kịp deadline), hãy nhập số ngày là số âm (ví dụ: -10) vào tham số thứ hai của hàm.
Việc nắm vững ba phương pháp trên—Paste Special cho thao tác nhanh, Công thức số học cho sự linh hoạt, và Hàm WORKDAY cho độ chính xác nghiệp vụ—sẽ giúp bạn kiểm soát hoàn toàn dữ liệu thời gian trong Excel.
FAQ: Câu Hỏi Thường Gặp
Tôi nhận được kết quả là chuỗi dấu thăng (#####) khi trừ ngày, tại sao?
Lỗi này xuất hiện khi kết quả phép trừ là một số âm và Excel đang được cài đặt sử dụng hệ thống ngày tháng 1900. Excel không thể hiển thị ngày trước ngày 01/01/1900. Hoặc đơn giản là cột quá hẹp, hãy thử kéo rộng cột ra.
Làm sao để cộng thêm tháng hoặc năm thay vì ngày?
Để cộng tháng hoặc năm chính xác (tự động xử lý năm nhuận hay tháng có 28, 30, 31 ngày), bạn nên sử dụng hàm EDATE (cộng tháng) hoặc hàm DATE. Ví dụ: =EDATE(A1, 3) sẽ cộng thêm 3 tháng vào ngày ở ô A1.
Hàm WORKDAY có tự động trừ ngày nghỉ lễ Tết không?
Mặc định là không. Bạn cần tạo một danh sách riêng chứa các ngày nghỉ lễ (như Tết Nguyên Đán, Giỗ Tổ), sau đó đưa vùng dữ liệu này vào tham số thứ 3 [Holidays] của hàm WORKDAY hoặc WORKDAY.INTL.
Tôi có thể dùng Paste Special để cộng ngày cho một cột chứa công thức không?
Không nên. Paste Special tác động trực tiếp lên giá trị. Nếu ô đích đang chứa công thức, hành động này sẽ xóa mất công thức và thay thế bằng giá trị tĩnh mới. Chỉ dùng Paste Special cho dữ liệu thô.
Nguồn Tham Khảo
- Microsoft Support: Hàm WORKDAY – Hỗ trợ của Microsoft – Tài liệu chính thức về cách sử dụng hàm WORKDAY và các đối số liên quan.
- Microsoft Support: Cộng hoặc trừ ngày – Hỗ trợ của Microsoft – Hướng dẫn tổng quan các phương pháp xử lý ngày tháng.
- GcfGlobal: Excel Formulas: Working with Dates – Kiến thức nền tảng về cách máy tính xử lý dữ liệu thời gian.
- Ablebits: Excel Date Functions – Tổng hợp các hàm ngày tháng nâng cao cho người dùng chuyên nghiệp.
