Bạn đang xem bài viết Cách Cộng Trừ Ngày, Tháng, Năm Trong Excel được cập nhật mới nhất trên website Uta.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Trong bài viết này, Blog Học Excel Online sẽ hướng dẫn cách cộng trừ ngày trong Excel, chẳng hạn như trừ hai ngày, thêm ngày, tuần, tháng và năm cho một ngày… giúp thuận tính cho công việc cũng như trong học tập.
Làm thếnào để trừ ngày 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 để biết số ngày nằm giữa hai ngày này. Kết quả có thể đạt được bằng nhiều cách.
Như bạn có thể biết, Microsoft Excel lưu trữ mỗi ngày như một số serial độc đáo bắt đầu bằng 1 đại diện cho 1 tháng 1 năm 1900. Vì vậy, khi bạn trừ chúng, một phép tính số học bình thường xảy ra:
Nếu công thức trên có vẻ quá đơn giản, bạn có thể đạt được cùng một kết quả 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. Hãy tìm ra lý do tại sao điều đó xảy ra.
Khi bạn trừ một ngày nhỏ hơn (1 tháng 5 năm 2023) cho một ngày lớn hơn (6 tháng 5 năm 2023), kết quả sẽ trả về 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.
Ví dụ 3. Trừ một ngày từ ngày hiện tại
Để trừ một ngày từ ngày hôm nay, bạn có thể sử dụng một trong hai công thức trên. Chỉ cần sử dụng chức năng TODAY () thay vì ngày:
= TODAY () – A2
hoặc là
= DATEDIF (A2, TODAY (), “d”)
Giống như trong ví dụ trước, cả hai công thức đều làm việc tốt khi ngày hôm nay lớn hơn ngày bạn trừ đi, nếu không DATEDIF sẽ báo lỗi:
Nếu bạn muốn tính số tháng hoặc năm giữa hai ngày, hàm DATEDIF là giải pháp duy nhất.
Bây giờ ta đã biết cách trừ hai ngày, ta hãy xem làm thế nào có thể thêm 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 thích hợp cho mục đích này, việc sử dụng cái nào phụ thuộc vào đơn vị bạn muốn cộng hoặc trừ.
Tìm hiểu thêm: Khoá học hướng dẫn học Excel cơ bản
Làm thế nào để trừ hoặc cộng thêm ngày trong Excel:Nếu bạn có một ngày trong một ô nào đó hoặc một danh sách ngày trong cột, bạn có thể cộng hoặc trừ một số ngày nhất định cho những ngày đó bằng cách sử dụng một phép toán số học tương ứng.
Ví dụ 1. Cách cộng 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ể cho một ngày như sau:
Ngày có thể được nhập bằng nhiều cách:
Là một ô 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 hàm khác. Ví dụ: để thêm số ngày cho 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à 6 tháng 5 năm 2023:
Ví dụ 2. Trừ ngày từ ngày trong Excel
Để trừ một số ngày từ một ngày nhất định, bạn thực hiện lại một phép tính số học thông thường. Sự khác biệt duy nhất từ ví dụ trước đó là bạn gõ dấu trừ cho dấu cộng 🙂
Cộng thêm tuần vào một ngày trong Excel:
= A2 + N tuần * 7
Ví dụ, bạn cộng 3 tuần cho ngày trong ô A2, sử dụng công thức sau: = A2 + 3 * 7.
Trừ tuần kể từ ngày trong Excel:
= A2 – N tuần * 7
Để trừ 2 tuần kể từ ngày hôm nay, bạn viết = TODAY () – 2 * 7.
Ví dụ 1. Cộng tháng cho một ngày với hàm DATE
Chẳng hạn, lấy một 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ố ô, 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 vào các ô khác:
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 của ngày trong A2.
Đơn giản phải không nào. Nếu bạn gõ một số âm trong C2, công thức sẽ trừ đi các tháng thay vì thêm chúng:
=DATE (YEAR (A2), MONTH (A2) – $ C $ 2, DAY (A2))
Và dĩ nhiên, bạn có thể gõ 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 giống như sau:
Cộng tháng vào ngày:= DATE (YEAR (A2), MONTH (A2) + 2, DAY (A2))
Ví dụ 2. Cộng hoặc trừ tháng cho một ngày với 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à 2023.
Trong hàm EDATE (start_date, months), bạn cung cấp hai đối số sau:
Start_date – ngày bắt đầu đếm số tháng.
Months – 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 thời gian cho kết quả giống hệt như hàm DATE trong ví dụ trước:
Công thức thêm 10 tháng từ 7 tháng 5 năm 2023.
Công thức trừ 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. Để buộc Excel hiển thị nó như một ngày, bạn nên áp dụng định dạng Ngày cho các ô chứa công thức.
Excel dành cho chuyên gia: Tăng hiệu quả làm việc thêm 50% với VBA trong Excel
Làm thế nào để trừ hoặc thêm năm vào ngày trong Excel:Thêm năm vào một ngày trong Excel được thực hiện tương tự như thêm 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 bạn muốn thêm bao nhiêu năm nữa:
= 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 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 cho ngày trong ô A2.
Để trừ năm kể từ ngày trong Excel:
= DATE (YEAR (A2) – 5, MONTH (A2), DAY (A2))
Công thức trừ 5 năm kể từ ngày trong ô A2.
Nếu bạn gõ số năm để thêm (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 một công thức tổng quát:
Để 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 thêm 2 năm, 3 tháng và trừ 15 ngày kể từ ngày trong ô A2:
= DATE (YEAR (A2) + 2, MONTH (A2) + 3, DAY (A2) – 15)
Áp dụng cho cột ngày tháng của ví dụ, công thức có dạng sau:
= A2 – TIME (giờ, phút, giây)
Để thêm thời gian trong Excel:
Để trừ thời gian trong Excel:
= A2 + TIME (2, 30, 15)
Ô A2 chứa giá trị thời gian mà bạn muốn thay đổi.
= A2 + TIME (2, 30, -15)
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:
Nếu bạn muốn cộng 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:
Vẫn có thể dùng công thức trên nếu dữ liệu nguồn chứa cả ngày tháng v à thời gian.
Hướng Dẫn Cách Cộng Trừ Ngày Tháng Trong Excel
Cách cộng trừ ngày tháng trong Excel Cách trừ ngày trong Excel
Trường hợp 1: Đếm số ngày giữa 2 ngày
Cách 1: Trừ trực tiếp trên ô tham chiếu.
Giả sử bạn có ô A3,A4 chứa dữ liệu ngày tháng là 12/8/2019 và 30/8/2019, bạn cần biết từ ngày 12/8/2019 – 30/8/2019 có bao nhiêu ngày. Bạn sẽ thực hiện như sau:
Tại ô C3,C4 bạn sử dụng phép tính số học = A4-A3
Cách 2: Sử dụng hàm DATEDIF
Tương tự cách như ở cách 1 bạn cũng có dữ liệu ngày tháng tại hai ô A3,A4 và yêu cầu đếm số ngày. Tại cách 2 thay vì bạn sử dụng cách trừ trực tiếp, bạn sử dụng hàm theo cú pháp như sau: =DATEDIF (A3, B4, “d”) thực hiện tương tự với dòng 5 và 6
Kết quả trả về sẽ giống như cách 1. Tuy nhiên, bạn sẽ nhận thấy lỗi #NUM ở D6 thông báo cho việc dữ liệu ngày tháng ở ô D6 nhỏ hơn 0 (số âm). Đây cũng là hạn chế của hàm DATEDIF.
Cách 3: Kết hợp hàm DATEDIF và TODAY
Nếu bạn đang cần đếm số ngày kể cả từ ngày hôm nay bạn sẽ cần đến sự kết hợp của 2 hàm này.
Ví dụ: Bạn muốn biết từ ngày 12/8/2019 (A3) – 15/9/2019 (A4) có bao nhiêu ngày không tính ngày hôm nay là 15/9/2019 thì bạn sẽ thực hiện 1 trong 2 cú pháp sau: = TODAY () – A4 hoặc = DATEDIF (A3, TODAY (), “d”)
Trường hợp 2: Trừ ngày từ ngày
Ví dụ: Bạn đang cần biết trước 10 ngày từ ngày 12/8/2019 (A3) là ngày bao nhiêu?
Có 3 cách sẽ giúp bạn giải quyết vấn đề trên.
Cách 1: Sử dụng cách trừ trực tiếp =A3-10
Cách 2: Sử dụng hàm DATE = DATE (2019, 8, 12)-10
Cách cộng ngày trong ExcelCũng giống như trừ ngày trong excel, việc cộng thêm ngày cũng thực hiện dựa trên 3 cú pháp.
Ví dụ: Hôm nay bạn nhận được thông báo ngày 12/8/2019(A3) sẽ kết thúc chương trình “Chia sẻ yêu thương” gây quỹ từ thiện cho trẻ em đón Trung Thu. Bạn muốn chương trình kéo dài thêm 12 ngày, bạn cần xác định ngày kết thúc chính xác sau 12 ngày.
Để giải quyết vấn đề trên bạn có thể chọn 1 trong 3 cú pháp sau:
Cú pháp 1: Cộng trực tiếp =A3+12
Cú pháp 2: Sử dụng hàm DATE
= DATE (2019, 8, 12)+12
Cú pháp 3: Sử dụng hàm TODAY
=TODAY(A3)-12
Cách cộng trừ tháng trong ExcelĐể thực hiện công việc cộng trừ tháng trong excel cũng tương đối dễ và giống với cách cộng trừ ngày. Để hiểu rõ hơn chúng ta cùng tìm hiểu và giải quyết ví dụ sau:
Ví dụ: Giả sử bạn lên chương trình Teambuilding và có dự định sẽ tổ chức vào cuối mùa hè này (ngày 12 tháng 8 năm 2023) nhưng vì lý do công việc bạn phải dời lại khoảng 3 tháng sau mới thực hiện. Bạn cần xác định lại chính xác ngày tháng của 3 tháng sau.
Để giải quyết vấn đề này bạn thực hiện 1 trong 3 cách sau sau:
Cách 1: Kết hợp các hàm DATE, YEAR, MONTH
= DATE (YEAR (), MONTH () + N tháng, DAY ())
Công thức cụ thể như sau: = DATE (YEAR (C4), MONTH (C4) + 3, DAY (C4))
Trừ ngày tháng trong Excel bằng hàm DATE(Nguồn: CIE Team)
Cách 2: Sử dụng hàm EDATE (start_date, months) trong đó:
Start_date: ngày bắt đầu đếm số tháng.
Months: số tháng để cộng (một giá trị dương) hoặc trừ (một giá trị âm).
Công thức như sau: = EDATE (DATE (2019,8,12), 3)
Cách 1: Kết hợp các hàm DATE, YEAR, MONTH
Công thức như sau: = DATE (YEAR (A3), MONTH (A3) – 2, DAY (A3))
Cách 2: Sử dụng hàm EDATE (start_date, months)
Công thức như sau: = EDATE (TODAY (A3), -3)
Lưu ý: Kết quả sẽ trả về các con số đại diện, để xem được ngày tháng năm bạn cần thực hiện thao tác mở hộp thoại Format Cells – tab Number – Category – Date và chọn định dạng ngày bạn muốn.
Các ví dụ cộng trừ ngày tháng trong ExcelVí dụ 1: Bạn có bảng số liệu như hình bên dưới.
Trừ ngày tháng trong Excel giúp công việc thuận lợi hơn (Nguồn: CIE Team)
Yêu cầu: Tại dự án 1, cột ngày khởi công cộng 8 ngày bằng hàm DATE, cột ngày hoàn thành trừ 5 ngày.
Công thức thực hiện như sau:
= DATE (2019, 8, 25)+8 =DATE(2019,9,15)-5
Kết quả thu được:
Tính ngày hoàn thành dự án (Nguồn: CIE Team)
Ví dụ 2: Giả sử bạn có bảng như bên dưới.
Quản lý thời gian nhập và xuất kho (Nguồn: CIE Team)
Sản phẩm bút, nhập kho trễ 3 ngày, 2 tháng so với dự định và xuất kho sớm 4 ngày, 1 tháng. Yêu cầu chỉnh sửa lại thời gian chính xác.
Công thực hiện như sau:
Sản phẩm 4:
= DATE (YEAR (C4), MONTH (c4) + 2, DAY (C4)+3)
Sản phẩm 4:
= DATE (YEAR (E4), MONTH (E4) + 1, DAY (E4)-4)
Kết quả thu được:
Quản lý thời gian nhập và xuất kho (Nguồn: CIE Team)
Cách sử dụng hàm Subtotal trong Excel : Ngày nay Excel được sử dụng rất phổ biến trong công việc cũng như trong học tập. Bài viết này chúng ta sẽ phân tích một hàm rất thông dụng là hàm Subtotal.
Cách Trừ Ngày Tháng Trong Excel
Lượt Xem:1287
Ngày được lưu trữ dưới dạng số nguyên dương trong Excel. Nó chỉ là định dạng của một ô Excel khiến nội dung của nó được hiển thị dưới dạng một ngày, chứ không phải là một số nguyên.
Hệ thống đánh số được sử dụng cho các ngày Excel khác nhau với các hệ thống máy tính khác nhau, nhưng phổ biến nhất, hệ thống đánh số là:
Vì ngày tháng Excel được lưu trữ nội bộ dưới dạng số nguyên đơn giản, bạn có thể trừ ngày tháng trong Excel, giống như cách bạn có thể trừ bất kỳ số nguyên nào khác.
Nghĩa là trừ các ngày trong Excel:
Nhập ngày bắt đầu và ngày kết thúc vào bảng tính của bạn;
Trừ ngày bắt đầu từ ngày kết thúc;
Đảm bảo kết quả được định dạng dưới dạng số.
(Cách dễ nhất để thực hiện việc này là chọn ô và sau đó chọn định dạng ‘Chung’ từ trình đơn thả xuống trong tab Trang chủ của ruy-băng Excel).
Kết quả từ công thức trừ bằng số ngày giữa hai ngày.
Ví dụ về phép trừ ngày tháng của Excel
Cột C của bảng tính sau đây cho thấy ba ví dụ về công thức trừ ngày tháng của Excel. Trong mỗi trường hợp, ngày trong cột A được trừ từ ngày trong cột B.
Lưu ý rằng, trong bảng tính kết quả ở trên, các ô trong cột A và B được định dạng với định dạng ngày, nhưng các ô trong cột C được định dạng bằng định dạng ‘Chung’.
Nếu kết quả của phép trừ ngày của bạn được hiển thị dưới dạng một ngày, nhưng bạn yêu cầu một số đơn giản, bạn sẽ cần phải thay đổi định dạng của ô kết quả, để hiển thị một số.
Cách dễ nhất để thực hiện việc này là chọn tùy chọn Định dạng chung từ menu định dạng thả xuống của ruy-băng Excel. Điều này thường nằm trong nhóm ‘Số’, trên tab Trang chủ của ruy-băng (xem bên dưới):
Thời gian được lưu trữ dưới dạng giá trị thập phân dương trong Excel. Nó chỉ là định dạng của một ô Excel khiến nội dung của nó được hiển thị dưới dạng thời gian chứ không phải là số thập phân.
Bảng bên dưới hiển thị các ví dụ về số thập phân và số lần chúng đại diện trong Excel.
Phép trừ thời gian trong ExcelKhi thời gian được lưu trữ dưới dạng giá trị thập phân đơn giản, bạn có thể trừ thời gian trong Excel theo cùng một cách mà bạn có thể trừ bất kỳ số nào khác. Do đó, để trừ thời gian trong Excel:
Nhập thời gian bắt đầu và thời gian kết thúc vào bảng tính của bạn;
Rút ngắn thời gian bắt đầu từ thời gian kết thúc;
Định dạng kết quả dưới dạng thời gian.
(Cách dễ nhất để định dạng ô bảng tính để hiển thị thời gian là chọn ô và sau đó chọn định dạng ‘Thời gian’ từ trình đơn thả xuống trong tab Trang chủ của ruy-băng Excel).
Cột C của bảng tính sau đây cho thấy bốn công thức trừ thời gian Excel đơn giản.
Trong mỗi trường hợp, thời gian trong cột A được trừ từ thời gian trong cột B.
, trong bảng tính kết quả ở trên, các ô C2 và C3 được định dạng với định dạng thời gian hh: mm và các ô C4 và C5 sử dụng định dạng thời gian hh: mm: ss .
Nếu bạn có một ô có chứa giá trị thập phân dương, điều này có thể được xem là một thời gian, bằng cách định dạng ô bằng định dạng thời gian.
Cách dễ nhất để thực hiện việc này là chọn định dạng Thời gian từ menu định dạng thả xuống, thường nằm trong nhóm ‘Số’, trên tab Trang chủ của ruy-băng Excel (xem bên dưới):
Nếu bạn muốn sử dụng định dạng thời gian khác, hãy chọn M Định dạng số quặng … từ trình đơn định dạng thả xuống để mở hộp thoại ‘ Định dạng ô‘. Trong hộp thoại này, bạn có thể chọn một trong các định dạng Thời gian được lập trình sẵn hoặc xác định định dạng Tùy chỉnh của riêng bạn .
Xem trang Định dạng Số Excel để biết thêm thông tin về cách xác định định dạng thời gian tùy chỉnh trong Excel.
Cách Tính Ngày Tháng Năm Trong Excel 2010, 2023, 2013, 2003, 2007, Hàm
CÁCH TÍNH NGÀY THÁNG NĂM TRONG EXCEL 2010, 2007, 2023, 2013, 2003
Trong ví dụ này chúng ta sẽ sử dụng hàm DATEDIF để tính tuổi, ngày tháng năm công tác của từng nhân viên
Cú pháp của hàm DATEDIF: DATEDIF(start_date,end_date,unit)
Trong đó:– start_date: Ngày bắt đầu của khoảng thời gian
– end_date: Ngày kết thúc hoặc ngày cuối cùng của khoảng thời gian
– Unit: Kết quả trả về
+ Các loại kết quả trả về:
– Y: Kết quả trả về là số năm
– M: Kết quả trả về là số tháng
– D: Kết quả trả về là số ngày
– MD: Sự khác biệt giữa các ngày trong start_date và end_date. Không phụ thuộc tháng và năm của ngày
– YM: Sự khác biệt giữa các tháng trong start_date và end_date. không phụ thuộc ngày và năm của ngày
– YD: Sự khác biệt giữa các ngày trong start_date và end_date. Không phụ thuộc năm của ngày.
Ví dụ: Chúng ta có một bảng danh sách nhân viên như hình dưới
Chúng ta sẽ sử dụng hàm DATEDIF để tính ra số tuổi của các nhân viên
– Nhập vào ô E7 công thức như sau: =DATEDIF(D7,NOW(),”y”) & ” Năm”. Trong đó start_date là ô D7, ngày sinh của nhân viên Phạm Văn Tú đã có; Now() là hàm lấy thời gian hiện tại từ máy tính – chính là end_date; “y” là unit – lấy kết quả trả về là số năm.
Kết quả hiện ra là số tuổi của nhân viên đó, các bạn kéo xuống để hoàn thành các ô bên dưới.
Tiếp tục, chúng ta sẽ tính toán số thời gian công tác dựa vào ngày bắt đầu làm việc của từng nhân viên. Ở đây chúng tôi sẽ lấy tất cả các Giá trị trả về – unit đã đề cập ở trên.
– Chúng ta nhập vào ô G7 công thức: =DATEDIF(F7,NOW(),”y”)& ” Năm” để tính ra số năm công tác của nhân viên Phạm Thanh Tú. Phần ‘& ” Năm” là để trả về đơn vị tính Năm. Sau đó ấn Enter.
– Chúng ta nhập vào ô G8 công thức: =DATEDIF(F8,NOW(),”m”)& ” Tháng” để tính ra số tháng công tác của nhân viên Lê Thị Đào. Sau đó ấn Enter
– Tiếp tục chúng ta nhập vào ô G9 công thức: =DATEDIF(F9,NOW(),”d”)& ” Ngày” để tính ra số ngày làm việc của nhân viên Nguyễn Văn Quảng. Sau đó ấn Enter để hoàn thành.
Đối với 3 tham số kết quả trả về còn lại, cả 3 tham số này đều cho ra kết quả không phụ thuộc vào số năm nên chúng ta sẽ kết hợp cả các hàm tính năm, tính tháng để tính ra cả năm, tháng, ngày.
– Nhập vào ô G10 công thức: =DATDEIF(F10,NOW(),”y”)&” Năm, “&DATEDIF(F10,NOW(),”ym”)&” Tháng, “&DATEDIF(F10,NOW(),”md”)&” Ngày”. Chúng ta sẽ có được kết quả là 4 Năm, 10 Tháng, 12 Ngày.
DATEDIF là một trong những hàm ngày tháng trong Excel được sử dụng để tính toán ngày tháng, năm, ngoài ra còn nhiều hàm ngày tháng khác mà bạn có thể tìm hiểu như Day, Today, Month…
Cập nhật thông tin chi tiết về Cách Cộng Trừ Ngày, Tháng, Năm Trong Excel trên website Uta.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!