Cách cộng trừ ngày, tháng, năm trong Excel

Trong bài viết này, Học Excel Online sẽ hướng dẫn cách cộng trừ thời gian trong excel, cộng trừ ngày, cộng tháng trong excel; như trừ hai ngày, cộng ngày, tuần, tháng, năm thành một ngày … giúp ích cho việc tính toán trong công việc cũng như trong học tập.

Cách trừ ngày tháng trong Excel:

Giả sử bạn có hai ngày trong ô A2 và B2 và bây giờ bạn muốn trừ chúng để tìm ra số ngày giữa hai ngày cái này. Kết quả có thể đạt được bằng nhiều cách.

Ví dụ 1. Trừ ngày trực tiếp

Như bạn có thể biết, Microsoft Excel lưu trữ mỗi ngày dưới dạng một số sê-ri duy nhất bắt đầu bằng 1 đại diện cho ngày 1 tháng 1 năm 1900. Vì vậy, khi bạn trừ chúng đi, một phép toán số học bình thường xảy ra:

= B2-A2

Tải game crack việt hoá tại: https://daominhha.com

Ví dụ 2. Ngoại trừ ngày sử dụng DATEDIF. hàm số

Nếu công thức trên có vẻ quá đơn giản, bạn có thể đạt được kết quả tương tự bằng cách sử dụng hàm DATEDIF của Excel:

= DATEDIF (A2, B2, “d”)

Hình sau đây cho thấy rằng cả hai phép tính đều trả về kết quả giống hệt nhau, ngoại trừ dòng 4 trong đó hàm DATEDIF trả về lỗi #NUM. Tìm hiểu lý do tại sao nó xảy ra.

Khi bạn trừ một ngày nhỏ hơn (ngày 1 tháng 5 năm 2015) cho một ngày lớn hơn (ngày 6 tháng 5 năm 2015), kết quả sẽ trả về một số âm (-5). Tuy nhiên, cú pháp của hàm DATEDIF không cho phép ngày bắt đầu lớn hơn ngày kết thúc, và do đó nó trả về lỗi.

Xem thêm: Cách tính tuổi từ ngày sinh trong Excel

Ví dụ 3. Trừ một ngày cho ngày hiện tại

Để trừ một ngày cho ngày hôm nay, bạn có thể sử dụng một trong các công thức trên. Chỉ cần sử dụng hàm TODAY () thay vì ngày:

= TODAY () – A2

hoặc

= DATEDIF (A2, TODAY ()), “d”)

Giống như trong ví dụ trước, cả hai công thức đều hoạt động tốt khi ngày hôm nay lớn hơn ngày bạn đã trừ, nếu không DATEDIF sẽ xuất hiện lỗi:

Ví dụ 4. Trừ ngày bằng DATE. hàm số

Nếu bạn muốn lấy ngày của công thức được cung cấp, hãy nhập ngày, sử dụng hàm DATE (năm, tháng, ngày) và trừ một ngày khác. Ví dụ: công thức sau trừ ngày 20 tháng 5 năm 2015 từ ngày 15 tháng 5 năm 2015 và trả về kết quả 5 ngày:= DATE (2015, 5, 20) – DATE (2015, 5, 15)

Tóm lại, để trừ ngày tháng trong Excel, bạn nên sử dụng tùy chọn đơn giản và rõ ràng nhất – chỉ trừ một ngày trực tiếp với một ngày khác.

Nếu bạn muốn tính số tháng hoặc số năm giữa hai ngày, hàm DATEDIF là giải pháp duy nhất.

Bây giờ chúng ta đã biết cách trừ hai ngày, hãy xem cách chúng ta có thể cộng hoặc trừ ngày, tháng hoặc năm cho một ngày nhất định. Có một số hàm Excel phù hợp cho mục đích này, việc sử dụng hàm nào tùy thuộc vào đơn vị bạn muốn cộng hoặc trừ.

Tìm hiểu thêm: Khóa học hướng dẫn Excel cơ bản

Cách trừ hoặc thêm ngày trong Excel:

Nếu bạn có ngày trong một ô nhất định hoặc danh sách ngày trong cột, bạn có thể cộng hoặc trừ ngày trong excel bằng một phép toán số học tương ứng.

>>> Xem thêm:  Cách tạo, cập nhật mục lục trong Word 2019, 2016, 2013

Ví dụ 1. Cách thêm ngày vào một ngày trong Excel

Công thức chung để thêm một số ngày cụ thể vào một ngày như sau:

=Ngày + N ngày

Ngày có thể được nhập theo một số cách:

  • Dưới dạng tham chiếu ô, ví dụ: = A2 + 10
  • Sử dụng hàm DATE (năm, tháng, ngày), ví dụ: = DATE (2015, 5, 6) + 10
  • Là kết quả của một chức năng khác. Ví dụ: để thêm số ngày vào ngày hiện tại, hãy sử dụng hàm TODAY (): = TODAY () + 10

Hình sau minh họa các công thức trên. Ngày hiện tại là ngày 6 tháng 5 năm 2015:


Chú ý: Kết quả của các công thức trên là một số thứ tự đại diện cho ngày tháng. Để hiển thị nó dưới dạng ngày tháng, hãy chọn (các) ô và nhấn Ctrl + 1 để mở hộp thoại Định dạng ô. Trên tab Số, hãy chọn Ngày trong danh sách Thể loại, rồi chọn định dạng ngày bạn muốn.

Ví dụ 2. Trừ ngày tháng trong Excel

Còn được gọi đơn giản là một phép toán ngày trừ ngày trong excel. Để trừ một số ngày cho một ngày nhất định, bạn thực hiện lại một phép toán số học thông thường. Sự khác biệt duy nhất so với ví dụ trước là bạn nhập dấu trừ cho dấu cộng 🙂

=Ngày N ngày

Dưới đây là một vài ví dụ:

  • = A2 – 10
  • = DATE (2015, 5, 6) – 10
  • = TODAY () – 10

Cách cộng hoặc trừ các tuần (từ ngày):

Trong trường hợp bạn muốn cộng hoặc trừ số tuần cho một ngày nhất định, bạn có thể sử dụng công thức tương tự như cộng / trừ ngày và nhân số tuần với 7:

Thêm tuần vào một ngày trong Excel:

= A2 + N tuần * 7

Ví dụ: bạn thêm 3 tuần vào ngày trong ô A2, sử dụng công thức sau: = A2 + 3 * 7.

Trừ tuần từ ngày trong Excel:

= A2 – N tuần * 7

Để trừ đi 2 tuần kể từ hôm nay, bạn viết = HÔM NAY () – 2 * 7.

Cách thêm và trừ các tháng từ ngày tháng trong Excel:

Nếu bạn muốn thêm tháng trong excel hoặc trừ tháng trong excel, bạn có thể sử dụng hàm DATE hoặc EDATE, như hình dưới đây.

Ví dụ 1. Thêm tháng vào một ngày với DATE. hàm số

Ví dụ: lấy danh sách ngày trong cột A, nhập số tháng bạn muốn cộng (số dương) hoặc trừ (số âm) trong một số ô, được gọi là C2.

Nhập công thức sau vào ô B2 và kéo góc của ô xuống để sao chép công thức sang các ô khác:

= DATE (YEAR (A2), MONTH (A2) + $ C $ 2, DAY (A2))

Bây giờ, chúng ta hãy xem kết quả. Logic của công thức là hiển nhiên và đơn giản. Hàm DATE (năm, tháng, ngày) có các đối số sau:

  • Năm của ngày trong ô A2;
  • Tháng của ngày trong A2 + số tháng mà bạn chỉ định trong ô C2 và
  • Ngày tháng trong A2.
>>> Xem thêm:  Lọc Dữ Liệu Trong Excel

Đơn giản phải không? Nếu bạn nhập một số âm trong C2, công thức sẽ trừ các tháng thay vì cộng chúng:

Tất nhiên, không có gì ngăn bạn gõ trực tiếp dấu trừ vào công thức để trừ các tháng kể từ ngày:

= DATE (YEAR (A2), MONTH (A2) – $ C $ 2, DAY (A2))

Và tất nhiên, bạn có thể nhập số tháng để cộng hoặc trừ trong công thức thay vì tham chiếu ô:

= DATE (YEAR (ngày), MONTH (ngày) + N tháng, DAY (ngày))

Các công thức thực sự có thể trông như thế này:

Thêm vào tháng vào ngày:= NGÀY (NĂM (A2), THÁNG (A2) + 2, NGÀY (A2))

Ngoại trừ tháng vào ngày:= NGÀY (NĂM (A2), MONTH (A2) – 2, NGÀY (A2))

Ví dụ 2. Cộng hoặc trừ các tháng cho một ngày bằng Excel EDATE

Microsoft Excel cung cấp một hàm trả về một ngày là một số nhất định trước hoặc sau ngày bắt đầu n tháng – hàm EDATE. Nó có sẵn trong các phiên bản hiện đại của Excel 2007, 2010, 2013 và 2016.

Trong chức năng EDATE (start_date, tháng), bạn cung cấp hai đối số sau:

  • Start_date – ngày bắt đầu đếm số tháng.
  • Tháng – số tháng để cộng (một giá trị dương) hoặc trừ (một giá trị âm).

Công thức sau được sử dụng trên cột ngày giờ cho kết quả chính xác giống như hàm DATE trong ví dụ trước:


Khi sử dụng hàm EDATE, bạn cũng có thể chỉ định ngày bắt đầu và số tháng để cộng / trừ trực tiếp trong công thức. Ngày phải được nhập bằng hàm DATE hoặc do kết quả của các công thức khác. Ví dụ:

  • Thêm vào: = EDATE (DATE (2015,5,7), 10)

Công thức thêm 10 tháng kể từ ngày 7 tháng 5 năm 2015.

  • Để trừ các tháng: = EDATE (TODAY (), -10)

Công thức trừ đi 10 tháng kể từ ngày hôm nay.

Chú ý. Hàm EDATE trả về một số đại diện cho ngày tháng. Để buộc Excel hiển thị nó dưới dạng ngày tháng, bạn nên áp dụng định dạng Ngày tháng cho các ô chứa công thức.

Excel dành cho chuyên gia: Tăng năng suất lên 50% với VBA trong Excel

Cách trừ hoặc thêm năm vào ngày tháng trong Excel:

Thêm một năm vào một ngày trong Excel được thực hiện giống như cách thêm một tháng. Bạn sử dụng lại hàm DATE (năm, tháng, ngày), nhưng lần này bạn chỉ định thêm bao nhiêu năm nữa mà bạn muốn:

= DATE (YEAR (ngày) + N năm, MONTH (ngày), DAY (ngày))

Trong bảng tính Excel của bạn, các công thức có thể trông giống như sau:

Để thêm năm vào một ngày trong Excel:

= DATE (YEAR (A2) + 5, MONTH (A2), DAY (A2))

Công thức thêm 5 năm vào ngày trong ô A2.

Để trừ năm cho ngày trong Excel:

= DATE (YEAR (A2) – 5, MONTH (A2), DAY (A2))

>>> Xem thêm:  Gợi ý code VBA trong Excel, làm sao để sử dụng đúng

Công thức trừ 5 năm kể từ ngày trong ô A2.

Nếu bạn nhập số năm để cộng (số dương) hoặc trừ (số âm) trong một số ô và sau đó tham chiếu ô đó trong hàm DATE, bạn sẽ nhận được công thức chung:

Thêm / bớt ngày, tháng và năm.

Nếu bạn đã xem xét kỹ hai ví dụ trước, bạn hẳn đã đoán được cách cộng và trừ ngày tháng trong Excel với một ngày trong công thức.

Để thêm năm, tháng, ngày:

= DATE (YEAR (ngày) + X năm, MONTH (ngày) + Y tháng, DAY (ngày) + Z ngày)

Để trừ năm, tháng, ngày:

= DATE (YEAR (ngày) – X năm, MONTH (ngày) – Y tháng, DAY (ngày) – Z ngày)

Ví dụ: công thức sau đây thêm 2 năm, 3 tháng và trừ đi 15 ngày kể từ ngày trong ô A2:

= DATE (YEAR (A2) + 2, MONTH (A2) + 3, DAY (A2) – 15)

Được áp dụng cho cột ngày của ví dụ, công thức có dạng sau:

= DATE (YEAR (A2) + $ C $ 2, MONTH (A2) + $ D $ 2, DAY (A2) + $ E $ 2)

Đường cộng và trừ thời gian trong Excel

Trong Microsoft Excel, bạn có thể cộng hoặc trừ thời gian bằng cách sử dụng hàm TIME. Nó cho phép bạn hoạt động theo đơn vị thời gian (giờ, phút và giây) chính xác như cách bạn xử lý năm, tháng và ngày với chức năng DATE.

Để thêm thời gian trong Excel:

= A2 + TIME (giờ, phút, giây)

Để trừ thời gian trong Excel:

= A2 – TIME (giờ, phút, giây)

Ô A2 chứa giá trị thời gian mà bạn muốn thay đổi.

Ví dụ: để thêm 2 giờ, 30 phút và 15 giây vào thời gian trong ô A2, bạn có thể sử dụng công thức sau:

= A2 + TIME (2, 30, 15)

Nếu bạn muốn thêm và trừ thời gian hợp nhất trong một công thức, chỉ cần thêm dấu trừ vào các giá trị tương ứng:

= A2 + TIME (2, 30, -15)

Công thức trên cộng 2 giờ 30 phút vào ô A2 và trừ đi 15 giây.

Ngoài ra, bạn có thể nhập thời gian bạn muốn thay đổi trong các ô nhất định và tham chiếu các ô đó trong công thức của bạn:

= A2 + TIME ($ C $ 2, $ D $ 2, $ E $ 2)

Công thức trên vẫn có thể được sử dụng nếu dữ liệu nguồn chứa cả ngày và giờ.

Nguồn: Ablebits, được dịch và hiệu đính bởi Hocexcel Online.

Đó là tất cả các trường hợp Thêm và trừ ngày tháng trong Excel. Lưu nó để bạn có thể mở nó ra khi cần.

Để có thể sử dụng tốt Excel trong công việc, chúng ta cũng cần phải biết sử dụng các hàm nâng cao như Sumproduct, Sumifs, Countifs… Đồng thời phải hiểu cách sử dụng các công cụ của Excel như Định dạng có điều kiện, Xác thực dữ liệu. , Bảng tổng hợp. … Những kiến ​​thức này bạn có thể học qua khóa học EX101 – Excel từ người mới bắt đầu đến chuyên gia của Học Excel Online. Hiện tại hệ thống đang có nhiều ưu đãi khi bạn đăng ký khóa học này. Chi tiết xem tại: Học Excel Online

Nguồn : Blog hocexcel