Tính ngày khá đơn giản khi các khoảng thời gian không cách nhau quá xa, tuy nhiên nếu cần tính số ngày từ ngày 28/1/2012 đến ngày 17/9/2020 thì việc tính nhẩm không hề nhanh và dễ dàng vì các tháng có số ngày không đều và năm nhuận.
Bạn có thể sử dụng một công cụ hữu hiệu giúp tính toán với dữ liệu ngày tháng, đó là bảng tính Excel.
Trong bài viết này, hoctin.vn sẽ hướng dẫn các bạn cách tính số ngày trong Excel, ứng dụng đếm ngày giữa 2 thời điểm, điều chỉnh lịch trình bằng cách cộng, trừ ngày, tuần, tháng, năm cho một ngày ấn định trước…
1, Lưu Ý Khi Tính Ngày Tháng Trong Excel
Các kiểu dữ liệu ngày tháng năm có nhiều định dạng khác nhau:
- Định dạng văn bản: là ngày tháng mặc định, ví dụ 01/01/2019.
-
Định dạng số sê-ri: Excel mặc định ngày 1 tháng 1 năm 1900 có số sê-ri là 1.
Những ngày tháng trước năm 1900 không thể chuyển đổi thành số trên Excel.
Ngày 1 tháng 1 năm 2019 có số sê-ri là 43466 vì nó là 43465 ngày sau ngày 1 tháng 1 năm 1900.
Cách hiển thị/ định dạng ngày tháng có thể khác nhau, nhưng khi xử lý dữ liệu tính ngày tháng trong Excel thì chỉ định dạng số sê-ri được dùng làm dữ liệu cộng, trừ ngày tháng.
Do đó, bạn không thể viết công thức = 1/1/2020 – 8/3/2019 để tính ngày chênh lệch trong Excel, vì Excel sẽ hiểu đây là phép trừ của 2 phép chia:
Để thực hiện tính số ngày trong Excel, bạn có thể điền các ngày vào các ô rồi tham chiếu vào công thức, điền ngày 1/1/2020 vào ô A3, ngày 8/3/2019 vào ô A4 rồi tính chênh lệch giữa 2 ngày bằng biểu thức =A3-A4.
Bạn cũng có thể sử dụng hàm DATE để tính cộng trừ ngày tháng trong Excel:
=DATE (year, month, day)
Ví dụ: DATE(2020,1,1)-DATE(2019,3,8)
2, Cách Trừ Ngày Trong Excel Bằng Hàm DATEDIF
Hoctin.vn đã có bài viết hướng dẫn cách sử dụng hàm DATEDIF tính chênh lệch khoảng thời gian trong Excel.
Cách tính số ngày từ ngày bắt đầu đến ngày kết thúc bằng hàm đếm ngày trong Excel:
=DATEDIF(Start_date, End_date, “d”)
Nhược điểm của hàm DATEDIF so với 2 cách trừ ngày đã nêu ở mục 1 là hàm DATEDIF sẽ trả về lỗi #NUM! nếu bạn trừ 1 ngày nhỏ hơn (10/9/2020) cho 1 ngày lớn hơn (15/9/2020).
3, Công Thức Tính Số Ngày Trong Excel Kể Từ Ngày Hiện Tại
Để tính số ngày chênh lệch từ 1 ngày bất kỳ đến ngày hôm nay, bạn có thể sử dụng 1 trong ba công thức đã nêu ở trên: phép trừ, hàm DATE, hàm DATEDIF kết hợp với 1 trong hai hàm trả về giá trị ngày/ thời gian hiện tại sau:
- TODAY(): trả về số sê-ri của ngày hiện tại khi quy đổi sẽ là dữ liệu ngày tháng năm.
- NOW(): trả về số sê-ri của ngày và thời gian hiện tại khi quy đổi sẽ là dữ liệu ngày tháng năm giờ phút giây.
Ví dụ dưới đây sử dụng các công thức:
= TODAY() – A8
= DATEDIF(A8, TODAY(), “d”)
= INT(NOW()-A8)
= DATEDIF(A8, NOW(), “d”)
Như đã trình bày tại mục 2, các công thức DATEDIF chỉ hoạt động khi ngày hôm nay lớn hơn ngày bạn cần trừ đi. Trường hợp ngày hiện tại nhỏ hơn ngày cần được trừ, hàm DATEDIF sẽ báo lỗi #NUM!:
Bạn cần cân nhắc khi trừ ngày trong Excel bằng hàm DATEDIF để tránh lỗi trong trang tính.
4, Tính Ngày Trong Excel Bằng Cách Cộng Thêm Hoặc Trừ Bớt Một Số Ngày Từ Một Ngày Cụ Thể
4.1, Tăng Giá Trị Ngày Trong Excel Thêm 1 Số Đơn Vị
Giả sử bạn có một hóa đơn đến hạn thanh toán trong 45 ngày kể từ ngày hóa đơn. Bạn muốn biết hạn thanh toán là ngày nào thì bạn cần phải cộng thêm 45 ngày vào ngày hóa đơn.
Hoặc bạn muốn điều chỉnh kế hoạch của một dự án bằng cách cộng thêm ba tuần. Để biết ngày hoàn thành mới là ngày nào, bạn cần thực hiện phép cộng ngày tháng trong Excel.
CỘNG THÊM N NGÀY
Công thức cộng ngày đơn giản nhất:
= Date + N ngày
Trong đó: Date có thể là:
Tham chiếu ô như B4 khi giá trị ngày được nhập vào ô B4
Hàm DATE(năm, tháng, ngày) như DATE(2013,9,15)
Là kết quả của một hàm ngày tháng khác.
CỘNG THÊM T TUẦN
Vận dụng công thức cộng ngày ở trên, áp dụng tính thêm số tuần vào ngày ban đầu:
= Date + T tuần * 7
CỘNG THÊM X THÁNG
Cách 1: Công thức cộng thêm 1 số tháng vào ngày ban đầu mà không cần phải quan tâm tháng có bao nhiêu ngày (28, 29 ngày với tháng 2; 30 hay 31 ngày với các tháng còn lại):
= DATE (năm, tháng+X, ngày)
Ví dụ DATE(2019, 9+2, 15) trả về ngày 15/11/2019 là ngày sau ngày 15/9/2019 hai tháng.
Ô B4 có giá trị 15/9/2013. Công thức tính ngày DATE(year(B4), month(B4)+5, year(B4)) trả về ngày tại ô B4 cộng thêm 5 tháng là ngày 15/2/2014.
Cách 2: Một công thức Excel khác đơn giản hơn – Công thức hàm EDATE tính ngày trước hoặc sau ngày đã biết đã xác định trước:
=EDATE (ngày bắt đầu, số tháng cần cộng thêm X)
Ví dụ =EDATE(B9,3) trả về ngày sau ngày tại ô B9 ba tháng.
CỘNG THÊM Y NĂM
Vận dụng công thức tính thêm 1 số tháng trong Excel ở trên, áp dụng cho năm (12 tháng) như sau:
Cách 1: Công thức cộng thêm 1 số năm vào ngày ban đầu mà không cần phải quan tâm năm nhuận:
= DATE(năm+Y, tháng, ngày)
Ví dụ DATE(2019+1, 9, 15) trả về ngày 15/9/2020 là ngày sau ngày 15/9/2019 một năm.
Ô B4 có giá trị 15/9/2013. Công thức tính ngày DATE(year(B4)+2, month(B4), year(B4)) trả về ngày tại ô B4 cộng thêm 2 năm là ngày 15/9/2021.
Cách 2: Công thức hàm EDATE tính ngày trước hoặc sau ngày đã biết đã xác định trước:
=EDATE(ngày bắt đầu, Y*12)
Ví dụ: Để cộng thêm 3 năm từ ngày ở ô A1: viết công thức EDATE(A1,3*12).
TỔNG HỢP CÔNG THỨC
Như vậy, bạn có thể cộng thêm một số ngày, một số tuần, một số tháng, một số năm từ một ngày cụ thể bằng cách sử dụng phép cộng, hàm DATE, EDATE như bảng tổng hợp sau:
4.2, Trừ Ngày Trong Excel Để Tính Ngày Đến Hạn
Giả sử một hóa đơn của bạn được thông báo đã hết hạn thanh toán vào ngày hôm nay. Hạn thanh toán là 90 ngày từ ngày nhận hóa đơn.
Bạn nhớ là mình đã thanh toán hóa đơn ngay vào ngày nhận được nhưng bạn không nhớ cụ thể là ngày nào. Bạn cần kiểm tra Bank Statement (sao kê ngân hàng) vào 1 ngày cụ thể là ngày bạn đã thanh toán.
Bạn có thể tìm ngày thanh toán bằng cách lấy ngày hôm nay trừ đi 60 ngày.
Tương tự như cách thêm 1 số ngày vào một ngày cụ thể, cách trừ ngày để tìm ngày tháng năm cũng sử dụng các công thức tương tự, thay số ngày cần thêm từ số dương thành số âm để trừ ngày trong Excel.
TRỪ ĐI N NGÀY
= Date – N ngày
TRỪ ĐI T TUẦN
= Date – T tuần * 7
TRỪ X THÁNG
Cách 1: DATE (năm, tháng-X, ngày)
Cách 2: EDATE (ngày bắt đầu, X)
TRỪ Y NĂM
Cách 1: DATE(năm-Y, tháng, ngày)
Cách 2: EDATE(ngày bắt đầu, -Y*12)
Trên đây là các cách tính số ngày trong Excel. Hi vọng bài viết của hoctin.vn đã mang lại cho các bạn các thông tin hữu ích.
Ngoài ra, để xử lý các dữ liệu ngày tháng, ví dụ lọc/ sắp xếp ngày tháng, định dạng ngày tháng trong Excel, bạn có thể tham khảo bài viết:
Cách lọc và sắp xếp dữ liệu trong Excel