Cách chuyển đổi số serial thành ngày tháng trong Excel

Mặc định Excel lưu trữ các giá trị ngày và giờ dưới dạng số serial. Điều này có nghĩa là mặc dù bạn có thể thấy một ngày chẳng hạn như “10 tháng 1 năm 2020” hoặc “01/10/2020” trong một ô, nhưng thực ra Excel đang lưu trữ ngày đó dưới dạng số. Điều này rất hữu ích vì nó cũng cho phép người dùng dễ dàng thêm/trừ ngày và giờ trong Excel.

Xem thêm: Thành thạo các thao tác với ngày tháng năm trong Excel nhờ khóa học Excel này:

EXG01: Tuyệt đỉnh Excel | Khóa học Excel online từ cơ bản đến nâng cao

G-LEARNING

499,000^đ 799,000^đ

Đăng ký Học thử

Trong Microsoft Excel dành cho Windows, “01/01/1900” được lưu trữ dưới dạng 1, “02/01/1900” được lưu trữ dưới dạng 2 … 

Bên dưới, cả hai cột đều có số giống nhau, nhưng cột A hiển thị số và cột B hiển thị ngày được biểu thị bằng số đó.

Đây cũng là lý do mà khi bạn muốn xem ngày trong một ô của một tệp Excel nhưng cuối cùng lại nhìn thấy một con số. Điều này xảy ra chủ yếu khi bạn tải tệp Excel từ cơ sở dữ liệu hoặc các công cụ dựa trên web.

Trong trường hợp này, nguyên nhân mà tệp Excel đó hiển thị các con số thay vì ngày là do định dạng các ô được đặt để hiển thị một số dưới dạng số thay vì ngày. Vậy dưới đây Gitiho sẽ hướng dẫn các bạn cách để chuyển những số serial này thành ngày tháng trong Excel.

Trong bài hướng dẫn này, Gitiho sẽ hướng dẫn các bạn hai cách thực sự dễ dàng để chuyển đổi số serial thành ngày tháng trong Excel.

Lưu ý: Excel cho Windows sử dụng hệ thống ngày 1900 (có nghĩa là 1 sẽ đại diện cho ngày 1 tháng 1 năm 1900, trong khi Excel cho Mac sử dụng hệ thống ngày 1904 (có nghĩa là 1 sẽ đại diện cho ngày 1 tháng 1 năm 1904)
 

Chuyển đổi số serial thành ngày bằng định dạng số

Cách dễ nhất để chuyển đổi ngày tháng số serial thành ngày tháng thuông thường là thay đổi định dạng của các ô có những số này.

Bạn có thể tìm thấy một số định dạng ngày tháng năm thường được sử dụng trong tab Home trên thanh công cụ ribbon, cách thực hiện như sau:

Sử dụng tùy chọn Date Format trên thanh công cụ ribbon

Giả sử bạn có một tập dữ liệu Excel như hình dưới đây và muốn chuyển đổi tất cả các số này trong cột A thành ngày tương ứng mà nó đại diện. Dưới đây là các bước để thực hiện việc này:

Bước 1: Chọn các ô có số bạn muốn chuyển đổi thành ngày tháng.

Bước 2: Truy cập vào thẻ Home.

Bước 3: Trong nhóm tính năng ' Number', hãy kích vào menu thả xuống của mục Number Formatting (mặc định sẽ là General). 

Bước 4: Trong menu thả xuống, hãy chọn tùy chọn ‘Long Date’ hoặc ‘Short Date’ (dựa trên định dạng mà bạn muốn những con số này hiển thị)

Các bước trên sẽ chuyển đổi các số serial thành định dạng ngày mà bạn đã chọn.

Tuy nhiên các bước trên sẽ không thay đổi giá trị trong ô mà chỉ là cách nó được hiển thị.

Lưu ý rằng Excel chọn định dạng ngày ngắn dựa trên cài đặt khu vực của hệ thống (system regional) trên máy tính của bạn. Ví dụ: Nếu ở Mỹ, thì định dạng ngày sẽ là MM/DD/YYYY và nếu ở Anh, thì định dạng ngày sẽ là DD/MM/YYYY, đây cũng là định dạng sử dụng ở Việt Nam.

Mặc dù đây là một phương pháp nhanh chóng để chuyển đổi số serial thành ngày tháng, nhưng nó có một số hạn chế mà bạn cần lưu ý:

- Chỉ có hai định dạng ngày đó là ‘Long Date’ và ‘Short Date’ (và đó cũng là một định dạng cụ thể). Vì vậy, nếu bạn chỉ muốn lấy tháng và năm chứ không phải ngày, bạn sẽ không thể làm điều đó bằng cách sử dụng các tùy chọn này.

- Không thể hiển thị ngày cũng như thời gian bằng các tùy chọn định dạng trong menu thả xuống của mục Number Formatting. Bạn có thể chọn hiển thị ngày hoặc giờ nhưng không thể hiển thị cả hai.

Vì vậy, nếu bạn cần linh hoạt hơn trong cách muốn hiển thị ngày tháng trong Excel, bạn cần sử dụng tùy chọn định dạng số tùy chỉnh Custom Number Formatting (sẽ được Gitiho giới thiệu tiếp theo trong hướng dẫn này).

Lưu ý: Đôi khi khi bạn chuyển đổi một số thành ngày tháng trong Excel. Nhưng thay vì hiển thị ngày tháng, bạn có thể thấy một số dấu thăng (giống như #####). Điều này xảy ra khi chiều rộng cột không đủ để chứa toàn bộ ngày. Trong trường hợp như vậy, chỉ cần tăng chiều rộng của cột.

Tạo định dạng ngày tùy chỉnh 

Giả sử bạn có một tập dữ liệu Excel như hình dưới đây và muốn chuyển các số serial trong cột A thành ngày tháng ở định dạng sau - 01.01.2020

Lưu ý rằng tùy chọn này không có sẵn theo mặc định trong phương pháp sử dụng thanh ribbon đã đề cập ở trên. 

Dưới đây là các bước để thực hiện việc này:

Bước 1: Chọn các ô có số serial bạn muốn chuyển đổi thành ngày tháng.

Bước 2: Truy cập vào thẻ Home.

Bước 3: Trong nhóm tính năng Numbers, kích vào biểu tượng mũi tên hướng xuống ở góc dưới cùng bên phải của nhóm, giống như hình ảnh dưới đây.

Bước 4: Trong hộp thoại ‘Format Cells’ hiển thị, hãy đảm bảo rằng tab ‘Number’ được chọn.

Bước 5: Trong danh mục (Category) các tùy chọn ở bên trái, hãy chọn mục 'Date'.

Bước 6: Chọn định dạng mong muốn sử dụng dưới mục ‘Type’ ở bên phải. 

Bước 7: Bấm nút OK để hoàn tất. 

Các bước trên sẽ lập tức chuyển đổi các số serial thành định dạng ngày đã chọn.

Như bạn có thể thấy, có nhiều tùy chọn định dạng ngày hơn trong trường hợp này (so với Short date và Long date ở trên). Trong trường hợp bạn không tìm thấy định dạng đang tìm kiếm, bạn cũng có thể tạo định dạng ngày tháng của riêng mình.

Ví dụ: Giả sử bạn muốn hiển thị ngày ở định dạng sau - 01/01/2020 (không phải là tùy chọn trong hộp thoại Format Cells ở trên).

Dưới đây là cách để bạn có thể thực hiện:

Bước 1: Chọn các ô có số serial bạn muốn chuyển đổi thành ngày tháng.

Bước 2: Truy cập vào thẻ Home.

Bước 3: Trong nhóm tính năng Numbers, kích vào biểu tượng mũi tên hướng xuống ở góc dưới cùng bên phải của nhóm, giống như hình ảnh dưới đây.

Bước 4: Trong hộp thoại ‘Format Cells’ hiển thị, hãy đảm bảo rằng tab ‘Number’ được chọn.

Bước 5: Trong danh mục (Category) các tùy chọn ở bên trái, hãy chọn mục ‘Custom’ thay vì chọn 'Date' như ở trên.

Bước 6: Trong trường bên phải, hãy nhập: mm/dd/yyyy vào khung Type, giống như hình dưới đây.

Bước 7: Bấm nút OK để hoàn tất.

Các bước trên sẽ thay đổi tất cả các số serial thành định dạng số được chỉ định.

Với định dạng tùy chỉnh, bạn có toàn quyền kiểm soát và cho phép hiển thị ngày ở bất kỳ định dạng nào bạn muốn. Bạn cũng có thể tạo một định dạng mà nó hiển thị ngày cũng như thời gian trong cùng một ô.

Ví dụ, trong cùng một ví dụ như trên, nếu bạn muốn hiển thị ngày cũng như thời gian thì có thể sử dụng định dạng dưới đây: mm/dd/ yyyy hh:mm AM/PM

Dưới đây là bảng hiển thị các mã định dạng ngày bạn có thể sử dụng:

Chuyển đổi số serial thành ngày bằng hàm TEXT

Các phương pháp được đề cập ở trên làm việc bằng cách thay đổi định dạng của các ô có số. Nhưng trong một số trường hợp, bạn có thể không sử dụng được các phương pháp này.

Ví dụ: Bên dưới có một tập dữ liệu Excel trong đó các số serial đều có dấu nháy đơn ở đầu và bạn cần chuyển đổi các số này thành văn bản.

Nếu bạn thử và sử dụng các tùy chọn có sẵn để thay đổi định dạng của các ô với tập dữ liệu này thì sẽ không có hiệu quả, điều này là do Excel không coi đây là số mà nhận diện chúng là văn bản thuần túy. 

Trong trường hợp như vậy, bạn có thể chuyển đổi những văn bản này thành số và sau đó sử dụng các phương pháp định dạng được đề cập ở trên hoặc sử dụng một kỹ thuật khác để chuyển đổi số serial thành ngày tháng đó là sử dụng hàm TEXT .

Hàm TEXT nhận hai đối số - số cần được định dạng và mã định dạng.

Giả sử bạn có một tập dữ liệu như hình dưới đây và muốn chuyển đổi tất cả các số và cột A thành ngày tháng.

Dưới đây là công thức để thực hiện điều này:

=TEXT(A2,"MM/DD/YYYY")

Lưu ý rằng trong công thức, chúng tôi đã chỉ định định dạng ngày là MM/DD/YYYY. Nếu bạn cần sử dụng bất kỳ định dạng nào khác thì có thể chỉ định mã cho nó làm đối số thứ hai của công thức TEXT.

Bạn cũng có thể sử dụng công thức này để hiển thị ngày cũng như thời gian.

Ví dụ: Nếu muốn kết quả cuối cùng có định dạng - 01/01/2020 12:00 SA, hãy sử dụng công thức dưới đây:

=TEXT(A2,"mm/dd/yyyy hh:mm AM/PM")

Trong công thức trên, chúng tôi cũng đã thêm định dạng thời gian, vì vậy nếu có phần thập phân trong các số, nó sẽ được hiển thị dưới dạng thời gian theo giờ và phút.

Nếu chỉ muốn hiển thị ngày (chứ không phải công thức cơ bản), hãy chuyển đổi công thức thành giá trị tĩnh bằng cách sử dụng tính năng dán đặc biệt Paste Special.

Một ưu điểm của việc sử dụng hàm TEXT để chuyển đổi số serial thành ngày tháng là bạn có thể kết hợp kết quả công thức TEXT với các công thức khác. 

Lưu ý: Hãy nhớ ngày và giờ được lưu trữ dưới dạng số, trong đó mỗi số nguyên sẽ đại diện cho một ngày đầy đủ và phần thập phân sẽ đại diện cho phần đó của ngày. Vì vậy, 1 có nghĩa là 1 ngày trọn vẹn và 0,5 có nghĩa là nửa ngày hoặc 12 giờ.

Trên đây Gitiho vừa hướng dẫn các bạn hai cách đơn giản có thể sử dụng để chuyển đổi số serial thành ngày tháng trong Microsoft Excel. Nếu biết thêm các cách nào khác, các bạn hãy chia sẻ với chúng tôi bằng cách để lại comment ở phía dưới nhé. Chúc các bạn thành công.