Có thể bạn quan tâm: Cách Kích Hoạt Scroll Lock Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Mục lục bài viết

Giới thiệu chung về Epoch Time và tầm quan trọng trong Excel

Trong thế giới dữ liệu hiện đại, Epoch time (còn gọi là Unix timestamp) là một trong những định dạng thời gian phổ biến nhất. Nó thể hiện số giây (hoặc mili giây) đã trôi qua kể từ thời điểm 00:00:00 UTC ngày 1 tháng 1 năm 1970. Dạng số nguyên này rất tiện lợi cho việc lưu trữ, so sánh và tính toán thời gian trong các hệ thống máy tính, cơ sở dữ liệu và ngôn ngữ lập trình.

Tuy nhiên, khi làm việc với Microsoft Excel, hầu hết người dùng lại quen với định dạng ngày tháng thông thường (dd/mm/yyyy, mm/dd/yyyy, yyyy-mm-dd, …). Vì vậy, việc chuyển đổi Epoch time sang ngày tháng trong Excel là một nhu cầu thường gặp, đặc biệt trong các công việc:

Phân tích log server, file CSV chứa timestamp Unix.
Tích hợp dữ liệu từ API trả về dạng timestamp.
Xây dựng báo cáo thời gian dựa trên dữ liệu thu thập tự động.
Kiểm tra, so sánh thời gian giữa các hệ thống khác nhau.

Bài viết này sẽ cung cấp cho bạn hướng dẫn chi tiết, đầy đủ về cách thực hiện chuyển đổi Epoch time sang ngày tháng trong Excel, bao gồm:

Nguyên lý tính toán thời gian trong Excel.
Các công thức cơ bản và nâng cao.
Sử dụng Power Query để chuyển đổi hàng loạt.
Sử dụng VBA (Macro) cho những trường hợp phức tạp.
Những lưu ý, lỗi thường gặp và cách khắc phục.
Một vài ví dụ thực tế để bạn có thể áp dụng ngay.

Hãy cùng đi sâu vào từng bước, đảm bảo bạn sẽ nắm vững mọi kỹ thuật cần thiết để xử lý Epoch time trong Excel một cách nhanh chóng và chính xác.

Có thể bạn quan tâm: Cách Kích Hoạt Tab Developer Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

1. Nguyên lý thời gian trong Excel

1.1. Excel lưu trữ ngày tháng như thế nào?

Excel sử dụng một hệ thống serial number để biểu diễn ngày và thời gian:

Ngày 1 tương đương với 01/01/1900 (trong Windows) hoặc 01/01/1904 (trong Mac, tùy chọn).
Mỗi số nguyên tăng lên 1 tương đương với một ngày.
Phần thập phân biểu thị thời gian trong ngày, ví dụ:
0.5 = 12:00 PM (buổi trưa)
0.25 = 06:00 AM
0.75 = 06:00 PM

Do đó, giá trị ngày giờ trong Excel luôn là một số thực, trong đó phần nguyên là ngày, phần thập phân là thời gian trong ngày.

1.2. So sánh với Epoch time

Epoch time tính từ 01/01/1970 00:00:00 UTC.
Excel tính từ 01/01/1900 (hoặc 1904). Do đó, để chuyển đổi, chúng ta cần tính sự chênh lệch ngày giữa hai mốc này.

1.2.1. Khoảng cách ngày giữa 1900 và 1970

Từ 01/01/1900 tới 01/01/1970 có 25569 ngày (đối với hệ thống 1900).
Nếu Excel đang ở chế độ 1904, khoảng cách là 24107 ngày.

Lưu ý: Excel có một lỗi lịch “leap year” năm 1900 (Excel tính năm 1900 là năm nhuận, trong khi thực tế không phải). Điều này dẫn tới số ngày +1 so với thực tế, nhưng vì chúng ta luôn làm việc trong môi trường Excel, nên chúng ta chấp nhận số này.

1.3. Công thức chuyển đổi cơ bản

Với Epoch time tính bằng giây (Unix timestamp chuẩn), công thức chuyển đổi sang ngày Excel là:

= (EpochTime / 86400) + 25569

Trong đó:
– 86400 = số giây trong một ngày.
– 25569 = số ngày giữa 01/01/1900 và 01/01/1970.

Nếu Epoch time là mili giây (thường gặp trong JavaScript), công thức sẽ là:

= (EpochTime / 86400000) + 25569

Sau khi có công thức, bạn chỉ cần định dạng ô thành kiểu ngày tháng (hoặc ngày giờ) để hiển thị kết quả.

Có thể bạn quan tâm: Cách Nhúng (embed) Tài Liệu Word Vào Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

2. Công thức Excel thực tế

2.1. Chuyển đổi Epoch (giây) sang ngày tháng

Giả sử ô A2 chứa giá trị Epoch time (giây), bạn nhập công thức sau vào ô B2:

= (A2 / 86400) + 25569

Sau khi nhập, định dạng ô B2:

Click chuột phải → Format Cells → Number tab → Date.
Chọn định dạng mong muốn (ví dụ: dd/mm/yyyy hh:mm:ss).

Kết quả sẽ hiển thị ngày tháng tương ứng.

2.2. Chuyển đổi Epoch (mili giây) sang ngày tháng

Nếu dữ liệu là mili giây, công thức:

= (A2 / 86400000) + 25569

Hoặc, để tránh lỗi làm tròn khi số quá lớn, bạn có thể dùng hàm INT cho phần ngày và MOD cho phần thời gian:

=INT(A2/86400000) + 25569 + MOD(A2,86400000)/86400000

2.3. Đổi sang múi giờ địa phương

Epoch time thường là UTC. Nếu bạn muốn chuyển sang múi giờ địa phương (ví dụ: GMT+7), chỉ cần cộng hoặc trừ số giờ tương ứng:

= (A2 / 86400) + 25569 + (7/24) // cộng 7 giờ

Ngược lại, nếu muốn chuyển sang UTC từ múi giờ địa phương:

= (A2 / 86400) + 25569 - (7/24) // trừ 7 giờ

2.4. Công thức tổng hợp (đối phó với cả giây và mili giây)

Bạn có thể tạo một công thức động để tự động nhận diện dữ liệu là giây hay mili giây dựa trên độ dài số:

=IF(LEN(A2)>10, (A2/86400000)+25569, (A2/86400)+25569)

Giải thích:
– Nếu độ dài >10 ký tự → có khả năng là mili giây (thường 13 chữ số).
– Ngược lại → là giây.

Sau khi áp dụng, định dạng lại ô thành ngày giờ.

3. Sử dụng Power Query để chuyển đổi hàng loạt

Khi dữ liệu có hàng nghìn hay hàng triệu dòng, Power Query (hay Get & Transform) là công cụ mạnh mẽ để thực hiện chuyển đổi nhanh chóng mà không cần sao chép công thức.

Có thể bạn quan tâm: Tạo Bảng Trong 1 Ô Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

33.1. Bước chuẩn bị dữ liệu

Đặt dữ liệu Epoch time trong một cột (ví dụ: Timestamp).
Chọn bất kỳ ô nào trong bảng → Data → From Table/Range để mở Power Query Editor.

3.2. Thêm cột tính ngày

Trong Power Query Editor:

Chọn Add Column → Custom Column.
Đặt tên cột mới, ví dụ DateTime.
Nhập công thức M (ngôn ngữ Power Query) như sau:

if Text.Length(Text.From(Timestamp)) > 10 then #datetime(1970,1,1,0,0,0) + #duration(0,0,0, Timestamp / 1000)
else #datetime(1970,1,1,0,0,0) + #duration(0,0,0, Timestamp)

Giải thích:
– #datetime(1970,1,1,0,0,0) là mốc Epoch.
– #duration(0,0,0, seconds) thêm số giây vào mốc.
– Nếu là mili giây, chia cho 1000 để chuyển sang giây.

Nhấn OK → Cột DateTime sẽ chứa giá trị ngày giờ thực.

3.3. Định dạng và tải dữ liệu

Nếu muốn hiển thị dạng ngày giờ trong Excel, đảm bảo cột DateTime có kiểu Date/Time.
Click Home → Close & Load để đưa dữ liệu trở lại Excel.

Power Query sẽ tự động cập nhật khi dữ liệu nguồn thay đổi, rất tiện cho các file log được cập nhật thường xuyên.

4. Sử dụng VBA (Macro) cho các trường hợp phức tạp

4.1. Khi nào cần VBA?

Dữ liệu nằm trong nhiều sheet, hoặc cần xử lý đồng thời nhiều cột.
Muốn tự động hoá quy trình (ví dụ: mỗi khi mở file, timestamp tự động chuyển đổi).
Cần thực hiện các kiểm tra, lọc, hoặc ghi log chi tiết.

4.2. Mã VBA chuyển đổi Epoch sang ngày

Mở Visual Basic for Applications (Alt + F11) → Insert → Module → dán đoạn mã sau:

Option Explicit ' Hàm chuyển Epoch (giây) sang ngày Excel
Public Function EpochToExcel(ByVal epoch As Double, Optional ByVal offsetHours As Double = 0) As Date EpochToExcel = (epoch / 86400) + 25569 + (offsetHours / 24)
End Function ' Hàm chuyển Epoch (mili giây) sang ngày Excel
Public Function EpochMsToExcel(ByVal epochMs As Double, Optional ByVal offsetHours As Double = 0) As Date EpochMsToExcel = (epochMs / 86400000) + 25569 + (offsetHours / 24)
End Function ' Macro áp dụng cho cột A (giả sử chứa Epoch) và ghi kết quả vào cột B
Public Sub ConvertEpochColumn() Dim ws As Worksheet Dim rng As Range, cell As Range Dim lastRow As Long Set ws = ThisWorkbook.Sheets("Sheet1") ' <-- sửa tên sheet nếu cần lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Set rng = ws.Range("A2:A" & lastRow) ' bắt đầu từ A2, bỏ tiêu đề For Each cell In rng If IsNumeric(cell.Value) Then If Len(CStr(cell.Value)) > 10 Then cell.Offset(0, 1).Value = EpochMsToExcel(cell.Value) ' kết quả vào cột B Else cell.Offset(0, 1).Value = EpochToExcel(cell.Value) End If End If Next cell ' Định dạng cột B thành ngày giờ ws.Columns("B").NumberFormat = "dd/mm/yyyy hh:mm:ss" MsgBox "Hoàn thành chuyển đổi Epoch!", vbInformation
End Sub

Cách sử dụng:

Chỉnh sửa tên sheet và phạm vi cột nếu dữ liệu khác.
Chạy macro ConvertEpochColumn (Alt + F8 → chọn → Run).
Kết quả sẽ xuất hiện ở cột B, đã được định dạng ngày giờ.

4.3. Tự động chạy khi mở workbook

Nếu muốn macro tự động thực thi khi mở file:

Private Sub Workbook_Open() Call ConvertEpochColumn
End Sub

Đặt đoạn mã này trong ThisWorkbook. Khi người dùng mở file, quá trình chuyển đổi sẽ diễn ra tự động.

5. Những lỗi thường gặp và cách khắc phục

Lỗi	Nguyên nhân	Giải pháp
#VALUE!	Dữ liệu không phải số, hoặc chứa ký tự trắng.	Kiểm tra và làm sạch dữ liệu (Sử dụng `TRIM`, `CLEAN`).
Kết quả ngày sai 1 ngày	Sử dụng hệ thống ngày 1904 (Mac) thay vì 1900.	Thêm/điều chỉnh hằng số: dùng `24107` thay cho `25569`.
Thời gian lệch múi giờ	Không cộng/ trừ offset giờ.	Thêm `(offset/24)` vào công thức hoặc hàm VBA.
Hiển thị 00:00:00	Phần thập phân bị mất do định dạng cell là Date (không có thời gian).	Đặt định dạng `dd/mm/yyyy hh:mm:ss`.
Số ngày quá lớn (> 2958465)	Epoch time quá lớn (sai đơn vị, ví dụ mili giây chia chưa).	Đảm bảo chia đúng cho 86400 (giây) hoặc 86400000 (mili giây).
Power Query không nhận dạng số	Cột được import dưới dạng Text.	Trong Power Query, chuyển kiểu cột sang Whole Number hoặc Decimal Number trước khi tính.

6. Ví dụ thực tế

6.1. Ví dụ 1: Log server Apache

File CSV chứa cột timestamp (giây) và message. Bạn muốn biết thời gian chi tiết của mỗi bản ghi.

Bước thực hiện:

Nhập file CSV vào Excel → tạo Table.
Thêm cột DateTime với công thức =(@timestamp/86400)+25569.
Định dạng cột DateTime thành yyyy-mm-dd hh:mm:ss.
Lọc, sắp xếp theo thời gian để phân tích.

Kết quả: Bạn có thể nhanh chóng xác định thời gian xảy ra lỗi, tần suất truy cập, v.v.

6.2. Ví dụ 2: Dữ liệu từ API trả về mili giây

Bạn gọi API của một nền tảng quảng cáo, nhận JSON có trường created_at dạng 1627849200123 (mili giây). Sau khi import JSON qua Power Query:

Trong Power Query, thêm cột Custom:
m #datetime(1970,1,1,0,0,0) + #duration(0,0,0, created_at / 1000)
Đổi kiểu cột sang Date/Time.
Đóng và tải lại.

Bây giờ bạn có ngày giờ chuẩn, có thể tính ROI theo ngày, so sánh chiến dịch…

6.3. Ví dụ 3: Tự động cập nhật mỗi ngày

Bạn có một sheet RawData chứa timestamp mới mỗi ngày. Bạn muốn một sheet Report luôn hiển thị ngày hiện tại của timestamp cuối cùng.

Macro tự động:

Public Sub UpdateLatestTimestamp() Dim wsRaw As Worksheet, wsRpt As Worksheet Dim lastRow As Long, latestEpoch As Double, latestDate As Date Set wsRaw = ThisWorkbook.Sheets("RawData") Set wsRpt = ThisWorkbook.Sheets("Report") lastRow = wsRaw.Cells(wsRaw.Rows.Count, "A").End(xlUp).Row latestEpoch = wsRaw.Cells(lastRow, "A").Value latestDate = EpochToExcel(latestEpoch, 7) ' cộng 7h cho GMT+7 wsRpt.Range("B2").Value = latestDate wsRpt.Range("B2").NumberFormat = "dd/mm/yyyy hh:mm:ss"
End Sub

Kết hợp với Workbook_Open → mỗi khi mở file, báo cáo luôn cập nhật thời gian mới nhất.

7. Tối ưu hiệu suất khi xử lý hàng triệu dòng

Khi dữ liệu lên tới hàng triệu bản ghi, việc sử dụng công thức trong từng ô sẽ làm Excel chậm. Dưới đây là một số chiến lược:

Sử dụng Power Query: Chỉ tính một lần khi tải, không giữ công thức trong ô.
Sử dụng VBA với mảng:
vba Dim arr, outArr() arr = ws.Range("A2:A" & lastRow).Value ReDim outArr(1 To UBound(arr, 1), 1 To 1) For i = 1 To UBound(arr, 1) outArr(i, 1) = EpochToExcel(arr(i, 1)) Next i ws.Range("B2").Resize(UBound(outArr, 1), 1).Value = outArr
Việc đọc/ghi dữ liệu một lần thay vì lần lượt vào từng ô giảm đáng kể thời gian.
Tắt tính toán tự động trong quá trình chạy macro:
vba Application.Calculation = xlCalculationManual '...code... Application.Calculation = xlCalculationAutomatic
Sử dụng tính năng “Data Model” (Power Pivot) nếu bạn cần phân tích dữ liệu lớn hơn 1 triệu dòng. Power Pivot hỗ trợ kiểu dữ liệu DateTime và có thể nhập trực tiếp Epoch time sau khi chuyển đổi trong Power Query.

8. Tổng kết

Epoch time là một định dạng số nguyên đại diện cho giây (hoặc mili giây) kể từ 01/01/1970 UTC.
Excel dùng hệ thống serial number ngày bắt đầu từ 01/01/1900, vì vậy chúng ta cần chuyển đổi bằng cách chia cho số giây trong một ngày và cộng thêm 25569 ngày.
Công thức cơ bản:
Giây: =(A2/86400)+25569
Mili giây: =(A2/86400000)+25569
Đối với múi giờ, chỉ cần cộng/trừ (offset/24).
Khi dữ liệu lớn, Power Query và VBA là các công cụ mạnh mẽ để tự động hoá và tối ưu hiệu suất.
Lưu ý các lỗi thường gặp: định dạng ngày, hệ thống ngày 1900/1904, đơn vị thời gian sai, và offset múi giờ.

Bằng cách áp dụng các phương pháp trên, bạn sẽ biến đổi Epoch time thành ngày tháng trong Excel một cách nhanh chóng, chính xác và linh hoạt, phục vụ mọi nhu cầu từ phân tích log, báo cáo kinh doanh, đến tự động hoá quy trình dữ liệu.

Bạn đã sẵn sàng áp dụng những kiến thức này vào dự án thực tế của mình chưa? Nếu có bất kỳ thắc mắc nào, hoặc muốn chia sẻ kinh nghiệm của bạn về việc xử lý Epoch time trong Excel, hãy để lại bình luận bên dưới. Chúng tôi luôn sẵn sàng hỗ trợ và cùng nhau phát triển!

Tiến sĩ Lê Thống Nhất

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 hì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ẻ.

Chuyển Đổi Epoch Time Sang Date Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z