Bài viết này sẽ hướng dẫn bạn cách thay đổi định dạng ngày tháng trong Excel và giải thích thêm về cách đặt định dạng mặc định cho những thông tin ngày tháng, cách chuyển đổi ngày tháng theo sự thay đổi của địa điểm.

Dữ liệu ngày tháng và thời gian là thường xử lý phổ biến với người dùng trong Excel. Tuy vậy, các thông tin này có thể hơi phức tạp vì có nhiều cách hiển thị trong cùng 1 thông tin này trên Excel. Bên cạnh đó, Excel luôn tự động thay đổi những thông tin ngày tháng theo một định dạng nhất định dù rằng có các thông tin bạn muốn hiển thị nó một cách khác biệt.

Vì thế, việc nắm được các định dạng ngày tháng của Excel một cách kỹ càng sẽ giúp ích bạn rất nhiều trong việc  tiết kiệm nhiều thời gian và đó cũng chính là mục tiêu của bài hướng dẫn này.

Định dạng ngày tháng của Excel

Trước khi bạn dùng thành thạo được các tính năng của Excel khi xử lý thông tin ngày tháng và thời gian, bạn cần phải hiểu được cách thức Excel lưu trữ các dữ liệu này. Bởi vì đây thường là nguyên nhân chính gây ra khó khăn cho bạn khi xử lý dữ liệu. Thực tế, khi bạn thực hiện nhập dữ liệu, Excel sẽ không ghi nhớ nó là ngày, tháng, năm mà sẽ lưu trữ nó như là 1 dãy số, và định dạng của ô chứa thông tin sẽ chuyển dãy số đó thành những thông tin ngày tháng hoặc giờ phút.

Ngày tháng thường được lưu trữ dưới dạng số nguyên trong Excel, hiển thị số ngày được tính từ ngày 1/1/1990. Bạn cũng có thể hiểu như sau:
Số 1 tượng trưng cho ngày 1/1/1990
Số 2 tượng trưng cho ngày 2/1/1990
Số 3 tượng trưng cho ngày 3/1/1990
Số 42005 tượng trưng cho ngày 1/1/2015 (vì ngày 1/1/2015 cách ngày 1/1/1990 đúng 42,005 ngày)
Số 2958465 tượng trưng cho ngày 31/12/9999

Thời gian trong Excel

Thời gian (giờ phút giây) sẽ được ghi nhận dưới dạng số thập phân trong Excel, giữa .0 và .99999, tượng trưng cho số thời gian được phân chia trong ngày, trong đó bao gồm:
Số 0 tượng trưng cho 00:00:00
Số 0.25 tượng trưng cho 06:00 sáng
Số 0.5 tượng trưng cho 12:00 trưa
Số 0.541655093 tượng trưng cho 12:59:59 tối

Thời gian và ngày tháng trong Excel

Excel lưu trữ thời gian, ngày tháng dưới dạng số nguyên + số thập phân tượng trưng cho ngày tháng, số giờ tương ứng. Bạn có thể xem ví dụ dưới đây:
Số 1.25 là 1/1/1900 vào lúc 6:00 sáng
Số 42005.5 là ngày 1/1/2015 vào lúc 12:00 trưa.

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

Nếu như bạn đang muốn biết dãy số biểu thị cho ngày tháng hay thời gian nào, bạn cũng sẽ có 2 cách để thực hiện.

1. Cửa sổ Định dạng ô (Format Cells)

Tiến hành chọn ô chứa thông tin ngày tháng trong Excel, sau đó nhấn Ctrl + 1 để mở hộp thoại Format Cells và chọn tab General.

Nếu như bạn muốn biết dãy số tượng trưng cho ngày tháng và không có nhu cầu đổi ngày tháng thành số, bạn hãy viết ra dãy số mà bạn đã nhìn thấy ở ô Sample, sau đó nhấn Cancel để đóng hộp thoại. Nếu như bạn muốn đổi ngày tháng thành số, nhấn OK.

2. Chức năng DATEVALUE và TIMEVALUE của Excel

Hãy dùng chức năng DATEVALUE() để thực hiện chuyển 1 thông tin ngày tháng trên Excel thành dãy số, ví dụ như sau: =DATEVALUE(“1/1/2015”).

Hay là dùng chức năng TIMEVALUE() để tiến hành chuyển thời gian thành số thập phân, ví dụ như =TIMEVALUE(“6:30 AM”).

Để biết được dãy số hiển thị cả thông tin ngày và tháng, bạn sử dụng cả 2 chức năng như sau:
=DATEVALUE(“1/1/2015”) & TIMEVALUE(“6:00 AM”)

Lưu ý. Bởi vì dãy số của Excel bắt đầu từ ngày 1 tháng 1 năm 1900, số âm sẽ không được ghi nhận, các ngày tháng trước năm 1900 cũng sẽ không thể chuyển đổi thành số trên Excel.

Nếu như bạn điền thông tin đó vào trang tính, chẳng hạn như ngày 12/31/1899 (tức ngày 31 tháng 12 năm 1899, ở Excel tháng sẽ hiển thị trước). Lúc này, nó sẽ hiển thị là giá trị chữ thay vì là hiển thị ngày tháng. Điều này có nghĩa là bạn không thể thực hiện chuyển đổi thành số theo cách làm thông thường. Để chắc chắn hơn, bạn có thể dùng công thức =DATEVALUE(“12/31/1899”), kết quả nhận lại lúc này sẽ là lỗi #VALUE!

Định dạng ngày tháng mặc định trong Excel

Khi bạn thực hiện làm việc với thông tin ngày tháng trên Excel, các định dạng ngày tháng dài hoặc là ngắn sẽ được quy định dựa trên cài đặt khu vực của Windows. Các định dạng mặc định này sẽ được đánh dấu bằng dấu hoa thị (*) tại hộp thoại Format Cells.

Định dạng ngày tháng tại hộp thoại Format Cells thay đổi nếu như bạn thay đổi cài đặt ngày tháng mặc định của Windows trong Control Panel.

Cách thay đổi định dạng ngày tháng và thời gian trong Excel

Nếu như bạn muốn thay đổi các định dạng ngày tháng và thời gian mặc định trong máy tính, chẳng hạn như ngày tháng theo kiểu Mỹ thành kiểu Anh, hãy di chuyển vào Control Panel và nhấp chọn phần Region and Language. Nếu như hộp Control Panel của bạn mở trong phần Category, thì bạn hãy vào phần Clock, Language and Region > Region and Language > Change the date, time, or number format.

Trong tab Formats, nhấp chọn khu vực phù hợp tại danh sách của Windows theo ảnh bên dưới:

Mẹo. Nếu như cảm thấy không chắc chắn các ký hiệu mmm, ddd, yyy có ý nghĩa thế nào thì bạn hãy click vào đường link “What does the notation mean” nằm dưới phần Date and time formats.

Nếu như bạn không vừa ý với bất cứ định dạng nào có tại phần Formats, bạn hãy vào phần Additional settings nằm phía bên phải dưới cùng tại hộp thoại Region and Language. Hộp thoại Customize lúc này sẽ hiện ra, sau đó bạn hãy chuyển sang tab Date và tiến hành nhập định dạng mong muốn.

Cách áp dụng nhanh định dạng ngày tháng và thời gian mặc định trên Excel

Excel thường có hai định dạng cho ngày tháng và thời gian – dài và ngắn.

Để có thể thay đổi định dạng ngày tháng trong Excel theo cài đặt mặc định, bạn hãy làm như sau:

Bước 1: Tiến hành chọn ngày tháng bạn muốn thay đổi định dạng.

Bước 2: Ở tab Home, tại phần Number, nhấp vào dấu mũi tên ngay cạnh ô Number Format, rồi chọn định dạng bạn muốn – short date, long date hoặc time.

Nếu như bạn muốn nhiều sự lựa chọn hơn, hãy chọn More Number Formats từ danh sách hoặc là chọn Dialog Box Launcher nằm ngay cạnh phần Number. Cách làm này sẽ giúp mở một hộp thoại Format Cells tương tự như trên, từ đó bạn có thể thay đổi mặc định trong đó.

Mẹo. Nếu như bạn muốn nhanh chóng tiến hành thay đổi định dạng trên Excel thành dd-mmm-yy, bạn hãy nhấn Ctrl + Shift + #. Bạn cần lưu ý rằng cách làm tắt này sẽ chỉ áp dụng áp dụng cho định dạng dd-mmm-yy, như 01-Jan-15 và không quan trọng định dạng mặc định của Windows là gì.

Cách thay đổi định dạng ngày tháng trên Excel

Trong Excel, ngày tháng sẽ được hiển thị theo nhiều cách khác nhau. Khi thực hiện thay đổi định dạng ngày tháng trong 1 ô hay là nhiều ô, cách làmdễ dàng nhất chính là mở hộp thoại Format Cells và thực hiện chọn một trong những định dạng dưới đây.

Bước 1: Tiến hành chọn ngày tháng mà bạn muốn thay đổi định dạng, hoặc là ô trống mà bạn muốn điền ngày tháng.
Bước 2: Nhấn Ctrl + 1 để thực hiện mở hộp thoại Format Cells. Hoặc là bạn có thể click chuột phải vào ô đó, rồi chọn Format Cells.
Bước 3: Trong cửa số Format Cells, hãy chọn tab Number và Date ở bảng Category.
Bước 4: Trong phần Type, hãy chọn định dạng ngày tháng mà bạn muốn, sau đó trong ô Sample sẽ hiển thị ví dụ tương ứng với ngày tháng bạn đã nhập vào ô.

Bước 5: Khi bạn đã vừa ý, hãy click OK để lưu thay đổi và đóng cửa sổ.

Nếu như lúc này định dạng không thay đổi, nguyên nhân có thể là do ngày tháng mà bạn điền vào đã bị coi là văn bản (text) và lúc này bạn sẽ cần chuyển nó thành dạng ngày tháng trước.

Cách chuyển đổi định dạng ngày tháng theo vị trí khu vực

Trong Excel, bạn sẽ có 1 loạt các ngày tháng với định dạng lộn xộn, và bạn mong muốn thay đổi chúng cho đồng bộ.Gỉa sử bạn muốn chuyển đổi định dạng ngày tháng theo kiểu Mỹ (tháng/ngày/năm) sang kiểu Châu Âu (ngày/tháng năm), thì bạn có thể thực hiện theo các bước sau:

Bước 1: Tiến hành chọn ô có ngày tháng bạn muốn thay đổi định dạng

Bước 2: Tiếp đó chọn Ctrl+1 để mở hộp thoại Format Cells.

Bước 3: Chọn vị trí ở phần Locale (location) tại hộp thoại và click OK để lưu thay đổi. Bạn cũng có thể xem trước định dạng trong phần sample trước khi nhấn OK nhé.

Nếu như bạn muốn ngày tháng được hiển thị theo ngôn ngữ khác nhau, bạn sẽ cần phải tạo định dạng đặc biệt với mã khu vực.

Xem thêm: Hướng dẫn cách tạo Combo Box trong Excel

Cách tạo định dạng ngày tháng đặc biệt trong Excel

Nếu như bạn thấy rằng không định dạng có sẵn nào trong Excel có thể phù hợp với nhu cầu, bạn hoàn toàn tự tạo được định dạng của riêng mình.

Bước 1: Tại file Excel, chọn ô mà bạn muốn thay đổi định dạng
Bước 2: Tiếp đó, nhấn Ctrl + 1 để mở hộp thoại Format Cells.
Bước 3: Ở tab Number, bạn nhấp chọn Custom ở bảng Category và sau đó điền định dạng bạn muốn trong ô Type bên cạnh.

Mẹo. Cách đơn giản nhất để có thể tạo định dạng mới là hãy bắt đầu từ định dạng có sẵn và nó gần giống nhất với định dạng mà bạn muốn. Để làmđược  việc này, bạn hãy vào ô Date trong bảng Category trước, rồi sau đó chọn một trong những định dạng có sẵn trong ô Type. Tiếp đó,  bạn hãy vào Custom và thay đổi định dạng mà bạn muốn.

Đến bước tạo định dạng ngày tháng trong Excel, bạn hãy sử dụng các ký hiệu sau:

m	Số tháng không có số 0 đứng trước	1
mm	Số tháng có số 0 đứng trước	01
mmm	Tên tháng ở dạng ngắn	Jan
mmmm	Tên tháng ở dạng đầy đủ	January
mmmmm	Tên tháng ký hiệu bằng chữ cái đầu tiên	J (ký hiệu cho January, June and July)
d	Ngày không có số 0 đứng trước	1
dd	Ngày có số 0 đứng trước	01
ddd	Tên thứ ở dạng ngắn	Mon
dddd	Tên thứ ở dạng dài	Monday
yy	Năm với 2 con số	05
yyyy	Năm với 4 con số	2005

Nếu tạo định dạng thời gian trong Excel, bạn hãy dùng các ký hiệu sau:

h	Giờ không có số không đằng trước	0-23
hh	Giờ không có số không đằng trước	00-23
m	Phút không có số không đằng trước	0-59
mm	Phút có số không đằng trước	00-59
s	Giây có số không đằng trước	0-59
ss	Giây không có số không đằng trước	00-59
AM/PM	Thời điểm sáng/ chiều trong ngày	6AM or 6PM

Mẹo. Nếu như bạn định tạo một định dạng ngày tháng, bạn dùng ký hiệu “m” ngay sau “hh” hoặc là “h” hoặc ngay trước “ss” hoặc “s”, Excel lúc này sẽ hiển thị ký hiệu này là phút chứ không phải tháng.

Khi tiến hành tạo định dạng, bạn cũng có thể sử dụng dấu phẩy, dấu gạch ngang hoặc dấu gạch chéo và dấu hai chấm nhé.

Ví dụ, cũng là January 13, 2015 13:03 nhưng có nhiều cách hiển thị thông tin này:

dd-mmm-yy	13-Jan-15
mm/dd/yyyy	01/13/2015
m/dd/yy	1/13/15
dddd, m/d/yy h:mm AM/PM	Tuesday, 1/13/15 1:03 PM
ddd, mmmm dd, yyyy hh:mm:ss	Tue, January 13, 2015 13:03:00

Làm thế nào để tạo định dạng ngày tháng trên Excel đối với những địa điểm khác nhau?

Nếu như bạn muốn ngày tháng của mình được hiển thị theo ngôn ngữ khác, bạn hãy tạo ra định dạng bằng việc sử dụng các ký hiệu khu vực tương ứng. Lưu ý, ký hiệu khu vực nên được để trong dấu ngoặc vuông và đi kèm với ký hiệu $ cùng dấu gạch ngang, ví dụ như:

[$-404] – Tiếng Trung tại Đài Loan

[$-409] – Tiếng Anh kiểu Mỹ
[$-1009] – Tiếng Anh kiểu Canada
[$-407] – Tiếng Đức kiểu Đức

[$-804] – Tiếng Bengali Ấn Độ
[$-804] – Tiếng Trung tại Trung Quốc
[$-807] – Tiếng Đức kiểu Thụy Sĩ

Ví dụ như bạn đang muốn tạo một định dạng ngày tháng theo dạng Trung Quốc với thứ tự năm-tháng-ngày, bạn hãy điền thông tin như sau:

Tại bảng dưới đây sẽ là vài ví dụ về ngày tháng với các mã địa điểm khác nhau tương ứng với các ngôn ngữ khác nhau:

Định dạng của Excel không hoạt động – giải pháp

Thông thường, Excel sẽ tiến hành xử lý thông tin dữ liệu rất tốt và sẽ không có bất kì một vấn đề nào xảy ra. Tuy vậy, nếu bạn vẫn không thiết lập được định dạng, bạn hãy thử xem xét những mẹo sau.

Ô không đủ rộng để chứa toàn bộ thông tin

Nếu như bạn thấy ô hiển thị với một loạt dấu ##### thay vì là ngày tháng, điều này có khả năng ô không đủ rộng để chứa thông tin.

Giải pháp: Bạn hãy click chuột đôi vào cạnh bên phải của ô để tiến hành thay đổi kích cỡ vừa vặn với thông tin, hoặc là dùng chuột kéo cạnh bên phải của ô sang bên phải cho đến khi nào ô đủ rộng.

Số âm trở thành ngày tháng

Trong các phiên bản Excel 2010, 2013 và 2017, dãy ##### cũng có thể xuất hiện nếu một ô được định dạng ngày tháng nhưng lúc này lại có chứa một giá trị âm. Thông thường đây sẽ là kết quả của công thức nhưng cũng không ngoại lệ xảy ra khi bạn điền một giá trị âm vào ô rồi sau đó định dạng ô thành ngày tháng. Nếu như bạn muốn hiển thị số âm thành ngày âm, sẽ có hai lựa chọn cho bạn.

Giải pháp 1. Đổi sang hệ thống ngày tháng 1904.

Bạn vào File > Options > Advanced, sau đó kéo xuống đến phần When calculating this workbook, rồi đánh dấu vào ô Use 1904 date system và click OK.

Với hệ thống này sẽ hiểu là, 0 is 1-Jan-1904; 1 là 2-Jan-1904; và -1 sẽ hiển thị cho ngày âm -2-Jan-1904.

Tất nhiên là, cách hiển thị này sẽ rất bất thường và cần thời gian làm quen, nhưng đây sẽ là cách chính xác nếu như bạn muốn hiển thị phép tính cho những ngày trước đó.

Giải pháp 2. Dùng chức năng TEXT của Excel

Có một cách khác để giúp hiển thị số âm là ngày âm trên Excel, đó là dùng chức năng TEXT. Ví dụ như nếu bạn trừ C1 cho B1 và giá trị tại C1 đang lớn hơn B1, bạn có thể dùng công thức sau để cho ra kết quả dưới dạng ngày tháng:
=TEXT(ABS(B1-C1),”-d-mmm-yyyy”)

Nếu như bạn muốn thay đổi thành căn lề phải, bạn hãy dùng bất cứ định dạng ngày tháng nào mà do chính bạn tự tạo ra trong công thức TEXT.

Lưu ý. Không như các giải pháp trước đó, chức năng TEXT cũng sẽ ra kết quả là giá trị chữ, vì vậy bạn không thể dùng kết quả đó trong các phép tính toán khác.

Thời gian được nhập vào EXCEL như là giá trị chữ

Khi bạn thực hiện nhập dữ liệu từ một file .csv hay là một cơ sở dữ liệu bên ngoài nào khác vào Excel,  thì thời gian sẽ thường có định dạng chữ. Lúc này chúng sẽ trông như ngày tháng thông thường, nhưng thực chất Excel đang coi chúng có dạng chữ (Text).

Giải pháp. Bạn hoàn toàn có thể chuyển “ngày tháng dạng chữ” sang định dạng ngày tháng chuẩn bằng việc sử dụng chức năng DATEVALUE hoặc Text to Columns.

Trên đây là toàn bộ các thông tin về cách thay đổi định dạng ngày tháng trong Excel, chúc bạn thao tác thành công!

Xem thêm: Hướng dẫn cách chuyển dấu phẩy thành chấm trong Excel