Có thể bạn quan tâm: Chuyển Tên Tiếng Việt Sang Tiếng Anh Trong Excel: Hướng Dẫn Chi Tiết Và Các Cách Thực Hiện Hiệu Quả
Giới thiệu
Trong thế giới công việc văn phòng, Excel luôn là công cụ không thể thiếu. Một trong những thao tác thường xuyên mà người dùng gặp phải là chuyển đổi ngày tháng sang giá trị số (serial number) để thực hiện các phép tính phức tạp, so sánh, hoặc lưu trữ dữ liệu một cách chuẩn xác hơn. Mặc dù Excel có thể tự động chuyển đổi khi nhập dữ liệu, việc hiểu rõ cơ chế hoạt động của nó sẽ giúp bạn tránh được những sai lầm phổ biến, tối ưu hoá công việc và nâng cao kỹ năng phân tích dữ liệu.
Bài viết này sẽ cùng bạn khám phá quy trình chuyển đổi ngày tháng sang số trong Excel, từ khái niệm cơ bản, công thức đơn giản, tới các phương pháp demain, VBA, và cách xử lý những trường hợp đặc biệt thường gặp trong thực tế làm việc.
Có thể bạn quan tâm: Chuyển Pdf Sang Excel Không Lỗi Font Trực Tuyến: Hướng Dẫn Chi Tiết Và Các Công Cụ Đáng Tin Cậy
1. Định nghĩa “Số ngày” trong Excel
1.1 Hệ thống ngàyResearchers của Excel
Excel lưu trữ ngày tháng dưới dạng số thập phân, gọi là serial number Bedeut. Mỗi số đại diện cho một ngày kể từ ngày 0 của hệ thống. Có hai hệ thống chính:
| Hệ thống | Ngày bắt đầu | Đặc điểm |
|---|---|---|
| Windows | 1/1/1900 | 1 = 1/1/1900, 2 = 2/1/1900, … |
| Mac (trước 2026) | 1/1/1904 | 1 = 1/1/1904, వివిధ |
- Windows: Đây là hệ thống mặc định trên hầu hết các máy tính cá nhân chạy Windows. Nếu bạn nhập
=A1+1với A1 chứa ngày 1/1/1900, kết quả sẽ là 2, tương ứng với 2/1/1900.
Wilhelm. - Mac: Trước năm 2026, macOS sử dụng hệ thống 1904. Khi chuyển đổi dữ liệu giữa hai hệ thống, cần chú ý về offset wet.
1.2 Lưu ý về ngày 29/2/1900
Excel (cũng như Lotus 1-2-3) giả định rằng 29/2/1900 là một ngày hợp lệ, mặc dù lịch Gregory không có ngày này. Điều này tạo ra một offset sai lệch: պահ, khi bạn nhập ngày 1/3/1900, Excel thực sự lưu trữ giá trị 61 (thay vì 60). Do đó, khi làm việc với dữ liệu cũ hoặc chuyển đổi giữa các hệ thống, cần lên dề.
Có thể bạn quan tâm: Bảng Excel Tính Bảo Hiểm Xã Hội 1 Lần
2. Cách chuyển đổi ngày sang số bằng công thức
2.1 Công thức đơn giản =DATEVALUE
Công thức =DATEVALUE("23/07/2026") sẽ trả về giá trị số thập phân tương ứng. Tuy nhiên, cần lưu ý:
- Định dạng chuỗi: Chuỗi phải ở dạng ngày nhận diện được bởi Excel (ví dụ
dd/mm/yyyyhoặcmm/dd/yyyytùy thuộc vào cài đặt khu vực). - Kết quả: Nếu bạn muốn hiển thị số nguyên, hãy định dạng ô thành “General” hoặc “Number” (đặt số thập phân = 0).
Ví dụ:
| Ô | Công thức | Kết quả (General) |
|---|---|---|
| A1 | =DATEVALUE("23/07/2026") | 43843 |
2.2 Sử dụng =INT(A1) hoặc =TRUNC(A1,0)
Nếu ô A1 chứa một giá trị ngày (định dạng ngày), bạn có thể lấy phần số nguyên (serial number) bằng =INT(A1) hoặc =TRUNC(A1,0). Điều này tự động loại bỏ phần thời gian (nếu có).
Ví dụ:
| Ô | Giá trị | Công thức | Kết quả |
|---|---|---|---|
| B1 | 23/07/2026 14:30 | =INT(B1) | 43843 |
2.3 Định dạng ô thành “General” để hiển thị số
Một kỹ thuật nhanh:
1. Nhấp đúp vào ô chứa ngày.
2. Chọn Format Cells > Number > General.
3. Nhấn OK.
Số serial sẽ hiện ra ngay lập tức. Bạn có thể chuyển lại định dạng ngày nếu cần.
3. Khi sao chép dữ liệu từ nguồn khác
Khi bạn nhập dữ liệu từ Outlook, Access, hoặc các tệp CSV, Excel có thể nhận dạng ngày theo cách khác nhau. Để chuyển số ngay từ nguồn:
- Sử dụng
Text to Columns: - Chọn cột chứa ngày.
- Vào
Data>Text to Columns. - Chọn
Delimited>Next>Date: DMY(hoặc Mdy tùy theo). Kết thúc và Excel sẽ chuyển đổi thành ngày.
Sử dụng
DATE:- Nếu dữ liệu được chia thành ba cột (ngày, tháng, năm), bạn có thể dùng
=DATE(YEAR, MONTH, DAY).
Ví dụ: =DATE(C1, B1, A1) với A1=23, B1=7, C1=2026.
- Sử dụng
DATEVALUEtrực tiếp trong cột dữ liệu: - Dùng công thức
=DATEVALUE(TEXT(A1,"dd/mm/yyyy")).
4. Xử lý ngày giờ
Có thể bạn quan tâm: Chuyển Đổi Thời Gian Từ Vn Time Sang Time New Roman Trong Excel: Hướng Dẫn Chi Tiết Và Các Lưu Ý
Excel lưu trữ thời gian dưới dạng phần thập phân của ngày. Ví dụ, 0.5 đại diện cho 12:00 PM (nửa ngày).
- Lấy phần thời gian:
=A1-INT(A1)hoặc=MOD(A1,1). - Chuyển phần thời gian sang phút hoặc giây:
=MOD(A1,1)2460(đổi thành phút).
Nếu bạn muốn chuyển đổi ngày giờ thành số thập phân đầy đủ:
=INT(A1) + MOD(A1,1)
Điều này thường không cần vì Excel đã tự động lưu trữ cả ngày và giờ.
5. Chuyển đổi ngược: Từ số sang ngày
Để kiểm tra tính đúng đắn, bạn có thể chuyển đổi ngược:
- Công thức:
=TEXT(A1, "dd/mm/yyyy"). - Định dạng ô: Định dạng ô thành “Date”.
Nếu A1 = 43843, kết quả sẽ là 23/07/2026.
6. Ứng dụng thực tế: Tính tuổi, khoảng thời gian
6.1 Tính tuổi
Giả sử A1 chứa ngày sinh, B1 chứa ngày hiện tại (hoặc ngày cuối cùng trong dữ liệu). Công thức tuổi:
=DATEDIF(A1, B1, "Y")
DATEDIF cho phép tính số năm, tháng, ngày. Khi bạn muốn tính tuổi dưới dạng số thập phân:
=(B1-A1)/365.25
6.2 Tính số ngày giữa hai ngày
=B1-A1
Nếu muốn hiển gwy, bạn có thể lấy =INT(B1-A1).
6.3 So sánh ngày và số
Khi bạn có dữ liệu số và muốn so sánh với ngày, chuyển ngay số thành ngày:
=DATE(1900,1,1)+A1- ступ
Nhớ điều chỉnh offset 1 nếu dữ liệu bắt đầu từ 0.
7. Xử lý lỗi phổ biến
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
#VALUE! khi dùng DATEVALUE | Chuỗi không nhận dạng ngày | Kiểm tra định dạng chuỗi, dùng TEXT để chuẩn hóa |
| Số ngày sai lệch 1 khi chuyển giữa Windows/Mac | Offset 0 vs 1 | Thêm hoặc trừ 1 tùy hệ thống |
| Thời gian không hiển thị | Ô định dạng là “Number” | Định dạng ô thành “Time” hoặc “Date & Time” |
#NUM! khi nhập ngày 29/02/1900 | Ngày không hợp lệ trong lịch Gregorian | Tránh sử dụng ngày này, hoặc dùng DATE(1900,2,28) |
8. Lưu trữ dữ liệu số ngày trong cơ sở dữ liệu
Khi xuất dữ liệu Excel sang SQL hoặc một hệ quản trị cơ sở dữ liệu, thường bạn cần chuyển số ngày sang định dạng yyyy-mm-dd. Công thức:
=TEXT(A1, "yyyy-mm-dd")
Hoặc dùng DATEVALUE và TEXT kết hợp:
=TEXT(DATEVALUE(A1), "yyyy-mm-dd")
9. Sử dụng VBA để chuyển đổi
Đối với quy trình tự động, bạn có thể viết macro:
Sub ConvertDateToSerial() Dim rng As Range For Each rng In Selection If IsDate(rng.Value) Then rng.Value = CDbl(CDate(rng.Value)) rng.NumberFormat = "General" End If Next rng
End Sub
Macro này sẽ duyệt qua vùng chọn, kiểm tra nếu ô chứa ngày, chuyển đổi thành số, và hiển thị dạng số.
10. Tổng kết và lời khuyên
- Hiểu rõ hệ thống: Biết ngày bắt đầu và offset giúp tránh nhầm sorr.
- Kiểm tra định dạng: Định dạng ô đúng luôn là chìa khóa.
- Sử dụng công thức:
DATEVALUE,INT,TRUNClà những công cụ chủ chốt. - Xử lý lỗi: Kiểm tra chuỗi, kiểm tra offset, và sử dụng
DATEDIFkhi cần tính tuổi. - Tự động hoá: Sử dụng VBA hoặc Power Query để xử lý hàng loạt.
Bằng cách nắm vững những kiến thức và công cụ trên, bạn sẽ có thể chuyển плох ngày tháng sang số trong Excel một cách chính xác, nhanh chóng và linh hoạt, từ đó nâng cao hiệu suất làm việc và độ chính xác của dữ liệu.
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.