Cách Dùng Hàm DATEVALUE Trong Excel Để Chuyển Ngày Văn Bản Thành Số Sê-ri

Khi nhập dữ liệu ngày tháng từ tệp PDF, TXT, hoặc CSV vào Excel, dữ liệu thường bị lưu dạng văn bản thay vì định dạng ngày. Điều này khiến bạn không thể sắp xếp theo thứ tự thời gian, lọc theo khoảng ngày, hay tính khoảng cách giữa hai mốc thời gian. Tôi từng mất 30 phút mỗi ngày để chỉnh sửa thủ công các ngày tháng này trước khi biết đến hàm DATEVALUE.

Mục Lục

Hàm DATEVALUE giải quyết vấn đề gì

Excel lưu trữ ngày tháng dưới dạng số sê-ri để có thể tính toán. Ngày 01/01/1900 có số sê-ri là 1, và mỗi ngày sau đó tăng thêm 1 đơn vị. Ví dụ, ngày 01/01/2008 có số sê-ri là 39448 vì cách ngày đầu tiên 39447 ngày.

Hàm DATEVALUE chuyển đổi ngày tháng dạng văn bản thành số sê-ri này. Sau khi chuyển đổi, bạn có thể sử dụng các chức năng sắp xếp, lọc, và tính toán ngày tháng bình thường trong Excel.

Khi nào bạn cần dùng DATEVALUE

Nhiều người thắc mắc tại sao cần dùng hàm này khi có thể chỉnh định dạng ô trực tiếp. Thực tế, chức năng Format Cells chỉ thay đổi cách hiển thị, không chuyển đổi kiểu dữ liệu. Nếu dữ liệu gốc là text, việc đổi định dạng sang Date không giải quyết được vấn đề.

Các trường hợp cần DATEVALUE:

Khi import dữ liệu từ nguồn ngoài như phần mềm kế toán, hệ thống CRM, hoặc database, ngày tháng thường được xuất dưới dạng chuỗi ký tự. Các công thức tính toán như DATEDIF, NETWORKDAYS sẽ trả về lỗi nếu dùng với dữ liệu text.

XEM THÊM: Hàm DAYS360 Trong Excel: Tại Sao Kế Toán Viên Dùng Năm 360 Ngày

Khi cần tách ngày tháng ra khỏi chuỗi chứa cả thời gian. Ví dụ, chuỗi “15/08/2024 10:30 AM” có thể dùng DATEVALUE để lấy phần ngày 15/08/2024 và bỏ qua phần giờ.

Khi làm việc với macro hoặc công thức phức tạp cần đảm bảo dữ liệu đầu vào là số thay vì text để tránh lỗi tính toán.

Cú pháp và tham số của hàm

Công thức hàm DATEVALUE có dạng đơn giản:

=DATEVALUE(date_text)

Trong đó date_text là tham số bắt buộc, có thể là:

Chuỗi văn bản chứa ngày tháng trong ngoặc kép: “15/10/1998”
Tham chiếu đến ô chứa ngày tháng dạng text: A1
Kết hợp nhiều ô bằng toán tử nối chuỗi: A1&”/”&B1&”/”&C1

Lưu ý quan trọng về phạm vi giá trị:

Hàm chỉ chấp nhận ngày trong khoảng từ 01/01/1900 đến 31/12/9999. Bất kỳ giá trị nào nằm ngoài phạm vi này sẽ trả về lỗi #VALUE!. Nếu bạn chỉ nhập ngày và tháng mà không có năm, Excel tự động sử dụng năm hiện tại từ đồng hồ hệ thống.

Ví dụ 1: Chuyển đổi ngày đơn giản

Cách đơn giản nhất là nhập trực tiếp ngày tháng vào hàm:

=DATEVALUE("08/08/2016")

Kết quả trả về là 42590 – số sê-ri tương ứng với ngày 08/08/2016.

Các bước thực hiện:

Chọn ô muốn hiển thị kết quả. Nhập công thức trên và nhấn Enter. Excel hiển thị số sê-ri năm chữ số.

Để xem dưới dạng ngày tháng, nhấn Ctrl + 1 để mở hộp thoại Format Cells. Chọn tab Number, chọn Date trong danh mục Category, chọn định dạng phù hợp như dd/mm/yyyy, rồi nhấn OK.

Một lưu ý nhỏ: ngày tháng phải được đặt trong ngoặc kép khi nhập trực tiếp. Nếu bỏ ngoặc kép, Excel sẽ không nhận diện được và trả về lỗi.

XEM THÊM: Cách Hàm WORKDAY.INTL Excel 2010 Giải Quyết Vấn Đề Cuối Tuần Linh Hoạt

Ví dụ 2: Xử lý ngày tháng năm ở nhiều cột

Trong thực tế, dữ liệu import thường có ngày, tháng, năm tách riêng ở các cột khác nhau. Giả sử ô B2 chứa ngày 15, ô C2 chứa tháng 10, ô D2 chứa năm 2024.

Công thức kết hợp sẽ là:

=DATEVALUE(B2&"/"&C2&"/"&D2)

Toán tử & nối các giá trị thành chuỗi “15/10/2024”, sau đó DATEVALUE chuyển thành số sê-ri.

Mẹo tăng tốc:

Sau khi nhập công thức vào ô đầu tiên, di chuột đến góc dưới bên phải của ô cho đến khi con trỏ chuyển thành dấu cộng đen. Kéo xuống các hàng bên dưới, Excel tự động điều chỉnh tham chiếu ô (B2 thành B3, B4…) và tính toán cho toàn bộ cột.

Tôi thường dùng kỹ thuật này khi xử lý bảng lương có 500-1000 dòng. Thay vì nhập từng công thức, chỉ cần 3 giây kéo xuống là xong.

Ba nguyên nhân gây lỗi #VALUE! và cách khắc phục

Lỗi #VALUE! xuất hiện khi Excel không thể chuyển đổi văn bản thành ngày hợp lệ. Có ba trường hợp phổ biến:

Nguyên nhân 1: Định dạng không hợp lệ

Giá trị nhập vào không theo định dạng ngày tháng chuẩn. Ví dụ:

“32/01/2024” – ngày không tồn tại
“2024-15-08” – tháng vượt quá 12
“08.08.2024” – dùng dấu chấm thay vì gạch chéo

Cách sửa:

Kiểm tra lại định dạng ngày tháng. Excel chấp nhận các dạng dd/mm/yyyy, mm/dd/yyyy, yyyy/mm/dd tùy theo cài đặt vùng miền của hệ thống.

Nếu dữ liệu gốc dùng ký tự ngăn cách khác (dấu chấm, gạch ngang), dùng hàm SUBSTITUTE để thay thế trước khi áp dụng DATEVALUE:

=DATEVALUE(SUBSTITUTE(A1,".","/"))

Nguyên nhân 2: Ô đang ở định dạng Date thay vì Text

Nếu ô chứa giá trị đã được định dạng là Date, DATEVALUE sẽ không hoạt động vì Excel coi đó là số thay vì text.

Cách sửa:

Nhấn Ctrl + 1, chọn tab Number, đổi Category thành Text. Sau đó nhập lại giá trị ngày tháng dưới dạng chuỗi.

XEM THÊM: Hướng Dẫn Đầy Đủ Hàm DATEDIF: Từ Y, M, D Đến YM, YD, MD

Một cách khác là thêm dấu nháy đơn ‘ trước khi nhập ngày tháng. Dấu nháy đơn buộc Excel lưu giá trị dưới dạng text thuần túy.

Nguyên nhân 3: Định dạng khác với cài đặt vùng miền

Đây là lỗi thường gặp nhất khi chia sẻ file giữa nhiều máy tính. Một số máy cài đặt định dạng ngày mm/dd/yyyy (Mỹ), trong khi máy khác dùng dd/mm/yyyy (Việt Nam, Châu Âu).

Khi bạn nhập “08/10/2024” trên máy Việt Nam (có nghĩa là 8 tháng 10), file đó mở trên máy Mỹ sẽ hiểu là 10 tháng 8. Nếu nhập “15/10/2024”, máy Mỹ sẽ báo lỗi vì không có tháng 15.

Cách sửa:

Kiểm tra cài đặt vùng miền trên máy tính. Trên Windows, vào Control Panel > Region > Change date formats. Đảm bảo Short date format khớp với định dạng trong file Excel.

Một giải pháp an toàn hơn là dùng định dạng ISO 8601: yyyy-mm-dd. Định dạng này được Excel nhận diện đồng nhất trên mọi hệ thống bất kể cài đặt vùng miền.

Mẹo định dạng kết quả hiển thị

Sau khi DATEVALUE trả về số sê-ri, bạn có hai cách để hiển thị dưới dạng ngày:

Cách 1: Format Cells thủ công

Chọn ô hoặc vùng ô cần định dạng, nhấn Ctrl + 1, chọn Date, chọn kiểu hiển thị.

Cách 2: Dùng hàm TEXT

Nếu cần hiển thị ngày theo định dạng tùy chỉnh trong công thức, kết hợp DATEVALUE với TEXT:

=TEXT(DATEVALUE("08/08/2016"),"dd-mm-yyyy")

Kết quả hiển thị: 08-08-2016

Hàm TEXT cho phép định dạng linh hoạt như “dddd, dd mmmm yyyy” để hiển thị “Thứ Hai, 08 tháng 8 năm 2016”.

Tương thích và phiên bản

Hàm DATEVALUE có sẵn trong tất cả phiên bản Excel từ Excel 2007 trở đi, bao gồm Excel 2010, 2013, 2016, 2019, 2021, và Microsoft 365. Hàm cũng hoạt động trên Excel cho Mac và Excel Online.

Đối với dữ liệu có cả ngày và giờ, bạn cần dùng thêm hàm TIMEVALUE để tách phần thời gian, hoặc dùng hàm VALUE để chuyển đổi cả ngày lẫn giờ cùng lúc. Hàm DATEVALUE chỉ lấy phần ngày và bỏ qua thông tin giờ trong chuỗi văn bản.

Khi làm việc với file có nhiều công thức DATEVALUE, việc chuyển kết quả từ công thức sang giá trị tĩnh giúp file mở nhanh hơn. Chọn vùng đã tính toán, nhấn Ctrl + C để copy, sau đó nhấn Ctrl + Alt + V, chọn Values, nhấn OK.