Một số dữ liệu bạn làm việc với Excel có thể bắt đầu dưới dạng kết quả đầu ra từ các hệ thống lớn trong văn phòng của bạn. Đôi khi các định dạng ngày tháng được sử dụng bởi các hệ thống lớn có thể hoàn toàn bị Excel hiểu nhầm. Ví dụ: đầu ra có thể cung cấp ngày ở định dạng yyyydddtttt, trong đó yyyy là năm, ddd là ngày thứ tự trong năm (từ 1 đến 366) và tttt là thời gian dựa trên đồng hồ 24 giờ. Thoạt nhìn, bạn có thể không biết cách chuyển đổi một ngày như vậy thành một thứ mà Excel có thể sử dụng.

Có nhiều cách để tiếp cận một giải pháp. Tuy nhiên, có lẽ công thức tốt nhất là:

=DATE(LEFT(A1,4),1,1)+MID(A1,5,3)-1+TIME(MID(A1,8,2),RIGHT(A1,2),0)

Công thức này đầu tiên tính ra số sê-ri ngày cho ngày 1 tháng 1 của năm được chỉ định, sau đó thêm số ngày chính xác vào ngày đó.

Sau đó, công thức sẽ tính toán thời gian phù hợp dựa trên những gì được cung cấp.

Khi một công thức như thế này được gọi, kết quả là một số sê-ri ngày.

Điều này có nghĩa là ô vẫn cần được định dạng để hiển thị định dạng ngày tháng.

Cách tiếp cận này sẽ hoạt động tốt, miễn là thông tin mà bạn bắt đầu có ý nghĩa. Ví dụ: bạn sẽ luôn nhận được kết quả mong đợi nếu ddd thực sự nằm trong phạm vi từ 1 đến 366 hoặc nếu tttt là biểu diễn thời gian 24 giờ được định dạng đúng. Nếu bạn dự đoán dữ liệu gốc có thể nằm ngoài giới hạn, thì giải pháp tốt nhất là tạo một hàm tùy chỉnh (sử dụng macro) sẽ chia nhỏ dữ liệu gốc và kiểm tra các giá trị được cung cấp cho từng phần.

Nếu dữ liệu nằm ngoài giới hạn, hàm có thể trả về một giá trị lỗi có thể dễ dàng phát hiện trong trang tính.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (9241) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: