Chuyển số thành ngày trong Excel và hiểu vì sao ô ngày lại hiện 45292
Excel lưu ngày tháng dưới dạng số: 45292 chính là 01/01/2024, còn phần sau dấu phẩy là giờ trong ngày. Công cụ chuyển số thành ngày trong Excel và ngược lại cho cả hệ 1900 của Windows, hệ 1904 của Mac đời cũ và Google Sheets, đổi hàng loạt cả cột dán vào, chỉ ra ô nào là chữ giả dạng ngày và đưa công thức sửa đúng cho máy của bạn.
Tính năng nổi bật
- Đổi số serial Excel sang ngày giờ và đổi ngày giờ ngược lại thành số serial
- Ba hệ ngày: 1900 (Excel Windows), 1904 (Excel Mac đời cũ), Google Sheets và LibreOffice
- Xử lý đúng ngày giả 29/02/1900 (serial 60) và độ lệch của serial 1 đến 59 trong hệ 1900
- Phần thập phân đổi thành giờ phút giây, ví dụ 45123,75 là 16/07/2023 18:00:00
- Đổi hàng loạt một cột dán từ Excel, tự nhận dòng nào là số, dòng nào là chữ ngày, dòng nào là số yyyymmdd
- Cảnh báo ngày dễ nhầm ngày tháng, ngày kiểu Mỹ, năm hai chữ số, dấu nháy đơn và khoảng trắng đặc biệt
- Xuất theo dd/mm/yyyy, có giờ, ISO 8601 hoặc kiểu Mỹ, sao chép cột kết quả dán lại vào Excel
- Bộ công thức TEXT, DATE, DATEVALUE đã đổi sẵn dấu phân cách chấm phẩy hoặc phẩy
Vì sao cột ngày trong Excel biến thành những con số như 45292
Excel không lưu chữ 01/01/2024 mà lưu số ngày kể từ mốc 01/01/1900 (hệ 1900), rồi dùng định dạng ô để hiển thị số đó thành ngày. Khi định dạng bị mất, chẳng hạn ô bị đổi về General, dữ liệu dán từ phần mềm khác, hay xuất ra CSV rồi mở lại, bạn sẽ thấy con số trần 45292 thay vì ngày. Chiều ngược lại cũng hay gặp: cột trông như ngày nhưng thực chất là chữ, căn lề trái, không cộng trừ hay lọc theo tháng được. Công cụ giúp bạn biết chính xác mỗi con số ứng với ngày nào, mỗi chữ ngày ứng với số nào, và nên sửa bằng cách định dạng lại hay bằng công thức.
Lợi ích khi sử dụng
- Biết ngay một con số lạ trong file là ngày nào mà không phải mò định dạng ô
- Sửa cột ngày bị lỗi khi nhập từ phần mềm kế toán, CRM, file CSV
- Tránh nhầm ngày tháng khi dữ liệu trộn cả định dạng Việt Nam và Mỹ
- Đổi đúng giữa file Excel Mac hệ 1904 và Excel Windows hệ 1900
- Có công thức đúng cú pháp cho máy dùng dấu chấm phẩy lẫn dấu phẩy
Cách chuyển số thành ngày trong Excel bằng công cụ
- 1Chọn hệ ngày: để 1900 nếu file tạo trên Excel Windows hoặc Excel Mac đời mới, chọn 1904 nếu file cũ từ Mac có ngày lệch khoảng 4 năm, chọn Google Sheets nếu số lấy từ Sheets.
- 2Chọn thứ tự trong chữ ngày (ngày/tháng hay tháng/ngày) và dấu thập phân mà dữ liệu của bạn đang dùng.
- 3Gõ một số vào ô số serial để xem ngày, hoặc gõ một ngày vào ô bên cạnh để xem số serial.
- 4Muốn đổi cả cột, sao chép cột từ Excel rồi dán vào ô đổi hàng loạt; mỗi dòng được nhận diện và đổi riêng.
- 5Đọc cột ghi chú để biết dòng nào dễ nhầm hoặc có lỗi, rồi bấm sao chép cột kết quả và dán lại vào Excel.
- 6Nếu muốn sửa ngay trong file, dùng công thức ở phần cuối, chọn đúng dấu phân cách đối số của máy bạn.
Số serial ngày của Excel hoạt động thế nào
Trong hệ 1900, số 1 là ngày 01/01/1900, mỗi ngày sau đó cộng thêm 1: 36526 là 01/01/2000, 45292 là 01/01/2024, và 2958465 là 31/12/9999, ngày lớn nhất Excel hỗ trợ. Phần thập phân là tỷ lệ của một ngày: 0,5 là 12 giờ trưa, 0,75 là 18 giờ, nên 45292,5 là 12:00 ngày 01/01/2024. Số âm và số lớn hơn 2958465 không phải ngày hợp lệ, ô định dạng ngày sẽ hiện chuỗi dấu thăng. Serial 0 được Excel hiển thị là 00/01/1900, thường xuất hiện khi công thức tham chiếu tới một ô trống.
Lỗi năm nhuận 1900: vì sao serial 60 là ngày 29/02/1900
Năm 1900 không phải năm nhuận vì chia hết cho 100 mà không chia hết cho 400, nên trên thực tế không có ngày 29/02/1900. Lotus 1-2-3 đã tính nhầm năm 1900 là năm nhuận, và Excel giữ nguyên lỗi này để mở đúng file Lotus. Hệ quả: serial 60 là một ngày không có thật, serial 61 mới là 01/03/1900, và mọi ngày từ 01/03/1900 trở đi đều bị cộng thêm một so với số ngày thật kể từ 31/12/1899. Với serial 1 đến 59 (tháng 1 và tháng 2 năm 1900), hàm WEEKDAY của Excel trả về thứ lệch một ngày so với lịch thật. Google Sheets và LibreOffice né lỗi này bằng cách lấy mốc 30/12/1899, nên từ serial 61 trở đi ba chương trình khớp nhau, chỉ khác ở serial 1 đến 60.
Hệ 1904 là gì và khi nào cần cộng 1462
Excel trên Mac đời cũ dùng hệ 1904: serial 0 là 01/01/1904. Excel hiện tại vẫn giữ tùy chọn này trong File, Options, Advanced, mục Use 1904 date system. Khi sao chép ngày giữa một file hệ 1904 và một file hệ 1900, ngày bị lệch đúng 1462 ngày, tức khoảng 4 năm 1 ngày. Ví dụ 01/01/2024 là 45292 ở hệ 1900 nhưng là 43830 ở hệ 1904. Cách sửa: cộng 1462 khi đổi từ 1904 sang 1900, trừ 1462 khi đổi ngược lại, hoặc dùng Paste Special với phép cộng. Công cụ này tính riêng cho từng hệ nên bạn có thể đối chiếu trực tiếp.
Chữ trông như ngày nhưng Excel không hiểu
Dấu hiệu dễ thấy nhất là ô căn lề trái trong khi ngày thật căn lề phải, hàm SUM, lọc theo tháng, PivotTable nhóm ngày đều không chạy. Nguyên nhân hay gặp: thứ tự ngày tháng trong chữ khác cài đặt vùng của Windows (dữ liệu 25/03/2024 trên máy cài kiểu Mỹ), có dấu nháy đơn ở đầu ô, có khoảng trắng không ngắt (non-breaking space) dính theo khi sao chép từ web, hoặc ngày được lưu dạng số liền 20240315. Hàm DATEVALUE chỉ hiểu chữ theo đúng định dạng vùng của máy, nên cùng một công thức có thể chạy trên máy này và báo lỗi #VALUE! trên máy khác. Công thức DATE kết hợp LEFT, MID, RIGHT không phụ thuộc cài đặt vùng nên an toàn hơn.
Ba cách sửa cột ngày ngay trong Excel
Nếu ô chứa số serial đúng nhưng hiển thị sai, chỉ cần định dạng lại: chọn cột, bấm Ctrl+1, chọn Date hoặc Custom rồi gõ dd/mm/yyyy. Cách này giữ nguyên giá trị nên vẫn tính toán được. Nếu cần ngày dạng chữ để ghép chuỗi hoặc xuất sang hệ thống khác, dùng =TEXT(A2;"dd/mm/yyyy"). Nếu ô là chữ ngày, chọn cột rồi vào Data, Text to Columns, đến bước 3 chọn Date với thứ tự DMY, Excel sẽ đổi cả cột thành ngày thật theo đúng thứ tự bạn chỉ định. Với dữ liệu lẫn lộn nhiều kiểu, dùng công thức DATE(RIGHT;MID;LEFT) ở cột phụ rồi dán giá trị đè lên cột gốc.
Dấu phẩy hay dấu chấm phẩy trong công thức
Excel dùng ký tự phân cách danh sách của Windows để ngăn các đối số trong công thức. Máy cài định dạng Việt Nam (dấu phẩy là dấu thập phân) thường dùng dấu chấm phẩy: =DATE(2024;3;15). Máy cài định dạng Mỹ dùng dấu phẩy: =DATE(2024,3,15). Gõ sai ký tự này là nguyên nhân phổ biến của thông báo There's a problem with this formula. Công cụ có nút chuyển để hiện công thức theo đúng kiểu máy bạn. Tương tự, số serial có phần giờ sẽ được xuất với dấu phẩy thập phân nếu bạn chọn, để dán vào Excel Việt Nam không bị hiểu thành chữ.
Giới hạn của công cụ
Công cụ đổi ngày theo lịch Gregory, không đổi âm lịch. Chế độ Google Sheets chỉ đổi ngày từ 30/12/1899 trở đi, không xử lý serial âm. Chữ ngày được nhận ở các dạng dd/mm/yyyy, d-m-yy, dd.mm.yyyy, yyyy-mm-dd và giờ kèm theo; tên tháng bằng chữ như 15 tháng 3 hay March 15 chưa được hỗ trợ. Năm hai chữ số được hiểu theo quy tắc mặc định của Excel: 00 đến 29 là 2000 đến 2029, 30 đến 99 là 1930 đến 1999. Mọi phép tính chạy trong trình duyệt, dữ liệu bạn dán không được gửi đi đâu.
Câu hỏi thường gặp (FAQ)
Vì sao ngày trong Excel hiện thành số như 45292?
Excel lưu ngày dưới dạng số ngày kể từ 01/01/1900. Khi ô mất định dạng ngày, bạn thấy con số gốc. 45292 là 01/01/2024. Chọn ô, bấm Ctrl+1 và chọn định dạng Date để hiển thị lại thành ngày.
Làm sao chuyển số thành ngày trong Excel bằng công thức?
Dùng =TEXT(A2;"dd/mm/yyyy") nếu cần chữ ngày, hoặc giữ nguyên số và định dạng ô thành Date nếu vẫn cần tính toán. Máy dùng dấu phẩy phân cách đối số thì thay dấu chấm phẩy bằng dấu phẩy.
Số 1 trong Excel là ngày nào?
Trong hệ 1900, số 1 là ngày 01/01/1900. Trong hệ 1904, số 0 là 01/01/1904 và số 1 là 02/01/1904.
Vì sao Excel có ngày 29/02/1900 dù năm 1900 không nhuận?
Đây là lỗi cố ý giữ lại để tương thích Lotus 1-2-3. Serial 60 là ngày giả 29/02/1900, serial 61 là 01/03/1900. Mọi ngày từ tháng 3 năm 1900 trở đi đều tính đúng, chỉ riêng tháng 1 và 2 năm 1900 bị lệch thứ trong tuần.
Phần thập phân của số ngày trong Excel là gì?
Là giờ trong ngày tính theo tỷ lệ của 24 giờ. 0,25 là 06:00, 0,5 là 12:00, 0,75 là 18:00. Ví dụ 45123,75 là 18:00 ngày 16/07/2023.
Đổi ngày sang số serial trong Excel thế nào?
Nếu ô đã là ngày thật, đổi định dạng ô sang General hoặc Number sẽ thấy số serial. Nếu là chữ ngày, dùng =DATEVALUE(A2) khi chữ khớp định dạng vùng của máy, hoặc =DATE(RIGHT(A2;4);MID(A2;4;2);LEFT(A2;2)) cho chữ dd/mm/yyyy.
Vì sao hàm DATEVALUE báo lỗi #VALUE!?
Thường do thứ tự ngày tháng trong chữ không khớp cài đặt vùng của Windows, chữ có khoảng trắng đặc biệt, hoặc ô đã là ngày thật chứ không phải chữ. Hãy dùng công thức DATE kết hợp LEFT, MID, RIGHT, không phụ thuộc cài đặt vùng.
Hệ ngày 1904 khác 1900 bao nhiêu ngày?
Chênh đúng 1462 ngày. Cùng ngày 01/01/2024, hệ 1900 là 45292 còn hệ 1904 là 43830.
Google Sheets có dùng cùng số ngày với Excel không?
Từ ngày 01/03/1900 trở đi thì giống nhau. Google Sheets lấy mốc 30/12/1899 và không có ngày giả 29/02/1900, nên serial 1 đến 60 lệch một ngày so với Excel: số 1 trong Sheets là 31/12/1899.
Số 20240315 có phải là ngày không?
Đó là ngày viết liền dạng yyyymmdd, không phải số serial. Dùng =DATE(LEFT(A2;4);MID(A2;5;2);RIGHT(A2;2)) để đổi thành ngày thật. Công cụ tự nhận diện dạng này khi đổi hàng loạt.
Làm sao biết 03/04/2024 là ngày 3 tháng 4 hay ngày 4 tháng 3?
Không thể biết chỉ từ chữ đó. Công cụ hiểu theo thứ tự bạn chọn và gắn cảnh báo dễ nhầm. Hãy xem các dòng khác trong cùng cột: nếu có dòng như 25/03/2024 thì cột đang dùng ngày/tháng.
Ngày lớn nhất Excel hỗ trợ là bao nhiêu?
31/12/9999, ứng với serial 2958465 ở hệ 1900 và 2957003 ở hệ 1904.
Đổi Unix timestamp sang ngày trong Excel thế nào?
Unix timestamp tính bằng giây kể từ 01/01/1970 giờ UTC. Dùng =A2/86400+DATE(1970;1;1)+7/24 để ra giờ Việt Nam, rồi định dạng ô thành ngày giờ.
Từ khóa liên quan
- chuyển số thành ngày trong excel
- chuyển ngày thành số trong excel
- excel hiện số thay vì ngày
- số serial ngày excel
- đổi số sang ngày tháng năm
- 45292 là ngày nào
- hàm text định dạng ngày
- hàm datevalue lỗi value
- hàm date trong excel
- sửa lỗi định dạng ngày excel
- chuyển chữ thành ngày excel
- hệ ngày 1904 excel
- lỗi năm nhuận 1900 excel
- excel serial date converter
- đổi ngày tháng hàng loạt excel
- ngày tháng bị đảo trong excel
- chuyển yyyymmdd sang ngày excel
- đổi giờ thập phân excel
- google sheets serial date
