Cách định dạng ngày tháng năm trong Excel đơn giản, ví dụ dễ hiểu

Nội dung được viết bởi Lực td

Trong Excel, bạn có thể dễ dàng định dạng ngày tháng năm bằng cách sử dụng các công cụ có sẵn. Bài viết này sẽ cùng bạn tìm hiểu về cách định dạng ngày tháng trong Excel tuỳ theo đất nước mà bạn đến, cũng như đặt thành thời gian mặc định của Excel.

1. Cách đổi định dạng ngày tháng năm trong Excel

Trong Excel, khi bạn nhập dữ liệu ngày tháng năm, Excel không tự hiểu nó là ngày tháng. Thay vào đó, nó lưu trữ chúng ở dạng Text (mặc định là General). Vì vậy, định dạng của ô không nhận biết rằng đó là ngày tháng, gây khó khăn cho việc xử lý dữ liệu.

Cách định dạng ngày tháng năm trong Excel đơn giản, ví dụ dễ hiểu
Ngày tháng được định dạng Text

Để giải quyết vấn đề này, bạn có thể sử dụng các công cụ định dạng ngày tháng năm trong Excel để giúp Excel hiểu và hiển thị dữ liệu thời gian chính xác.

Mỗi khi thay đổi định dạng trong một hay nhiều ô hãy mở hộp thoại Format Cells và làm theo các bước sau để thay đổi định dạng ngày tháng trong Excel:

Bước 1. Chọn ô ngày tháng năm mà bạn muốn thay đổi định dạng hoặc một ô trống bất kỳ mà bạn muốn điền ngày tháng năm.

Chọn ô mà muốn định dạng ngày tháng
Ô muốn định dạng ngày tháng

Bước 2. Bấm tổ hợp Ctrl + 1 để mở cửa sổ Format Cells.

Bước 3. Cửa sổ Format Cells hiện ra, ta chọn tab Number > Chọn Date.

Bước 4. Ở phần Type, lựa chọn đình dạng ngày tháng năm mà bạn muốn. Xem định dạng ngày tháng năm trong Excel sẽ hiển thị như thế nào ở mục Sample.

chọn định dạng ngày trong Excel
chọn định dạng ngày trong Excel

Bước 5. Nhấn OK để lưu và đóng cửa sổ.

Lưu ý: Nếu định dạng bạn yêu cầu không thay đổi thì có thể do ban đầu dữ liệu ngày tháng năm ban đầu bạn điền vào đã dưới dạng văn bản từ trước. Lúc này bạn cần sẽ phải chuyển nó về ngày tháng năm dạng chữ số.

Xem thêm: 2 cách làm hiển thị đầy đủ dữ liệu ngày tháng trong Excel

2. Định dạng ngày tháng năm trong Excel thành số (Number)

Cách Excel quy ước ngày tháng năm ở dạng Number

Theo thông tin từ Microsoft thì thời gian của Excel bắt đầu được hiển thị số từ ngày 1/1/1990. Dữ liệu thời gian trong Excel phải được lưu trữ là số nguyên. Ví dụ như sau:

  • Số 1 tức ngày 1/1/1990
  • Số 2 tức ngày 2/1/1990
  • Số 42005 tức là ngày 1/1/2015 (có nghĩa ngày 1/1/1990 cách ngày 1/1/2015 tới 42005 ngày).
  • Tương tự với con số 2958465 tức ngày 31/12/2999.

Xem thêm: Hướng dẫn cách định dạng số và ngày tháng trong Excel

Định dạng ngày tháng năm trong Excel thành số bằng Format Cells

Chẳng hạn yêu cầu công việc của bạn là điền dãy số tượng trưng cho thời gian thay vì đổi ngày tháng năm thành số, chúng ta định dạng lại như sau:

Chọn ô bất kỳ chứa dữ liệu ngày tháng năm thời gian trong Excel, nhấn Ctrl+1 (hoặc click chuột phải) để hiện hộp thoại Format Cell >> chọn General > nhấn OK.

Định dạng ngày tháng năm bằng số
Định dạng ngày tháng năm bằng số bởi format cells

Dùng hàm Datevalue và Timevalue để đổi định dạng ngày tháng năm trong Excel

Công thức Datevalue trong Excel để đổi từ ngày tháng năm sang một dãy số dài tượng trưng ở dạng Text.

=DATEVALUE("dd/mm/yyyy") 

Chẳng hạn ví dụ bên dưới =DATEVALUE(“1/1/2021”)

Định dạng ngày tháng năm bằng hàm
Định dạng ngày tháng năm bằng hàm Datevalue

Còn Hàm TIMEVALUE() dùng để chuyển thời gian ngày tháng năm thành một dãy số thập phân, 

=TIMEVALUE(datetime)

Ví dụ: =TIMEVALUE(“6:30 AM”) đối với máy tính cài chế độ 12 giờ, và =TIMEVALUE("6:30") đối với máy cài chế độ 24 giờ.

Định dạng ngày tháng năm bằng hàm
Định dạng ngày tháng năm bằng hàm TIMEVALUE

 

Để hiển thị thông tin của cả ngày lẫn tháng trên một con dãy số thập phân, ta có thể sử dụng cả hai chức năng trên như sau: 

=DATEVALUE(“1/1/2015”) & TIMEVALUE(“6:00 AM”)
Định dạng ngày tháng năm bằng hàm kết hợp

Chú ý: Thời gian trong Excel được tính từ ngày 1/1/1900, đồng nghĩa với việc thời điểm trước năm 1900 sẽ không được chuyển đổi sang số trong Excel. Ngoài ra thời gian trên Excel cũng không được là số âm.

Còn trong trường hợp bạn muốn điền thông tin thời điểm 12/31/1899 (tức ngày 31 tháng 12 năm 1899  theo cách viết của Excel và phương Tây) thì giá trị xuất ra sẽ hiển thị lỗi #VALUE!

3. Thao tác nhanh để đổi định dạng ngày tháng năm trong Excel

Excel đưa ra hai lựa chọn linh hoạt cho người sử dụng về dữ liệu thời gian và ngày tháng năm là dài và ngắn.

Cách đổi vị trí ngày tháng trong Excel về mặc định, ta sẽ làm như sau:

  • Chọn dữ liệu ngày tháng năm mà bạn muốn định dạng.
Chọn dữ liệu ngày tháng năm muốn định dạng lại
Chọn dữ liệu ngày tháng năm
  • Trong phần Home, tại nhóm Number , nhấn con trỏ chuột ô đổi định dạng như hình bên dưới:
Định dạng ngày tháng năm trong Excel nhanh
Định dạng ngày tháng năm trong Excel nhanh
  • Chọn định dạng trong danh sách xổ xuống mà bạn yêu cầu gồm Short date, long date và time.
Đổi vị trí ngày tháng trong Excel nhanh
Đổi vị trí ngày tháng trong Excel nhanh

Bên cạnh đó Excel bạn cũng có thể nhấn vào mũi tên ở góc dưới bên phải nhóm Number. Từ đây hộp thoại Format Cells sẽ mở ra tương tự trên và bạn có thể thay đổi cài đặt mặc định thời gian trong đó. 

Mở Format cells nhanh
Mở Format cells nhanh

Lưu ý: nếu bạn muốn đổi vị trí ngày tháng trong Excel nhanh thành dạng dd-mm-yy thì chỉ cần nhấn Ctrl + Shift + #. Bằng cách chuyển nhanh này thì sẽ áp dụng định dạng dd-mm-yy mà áp dụng định dạng ngày tháng trong Excel của Windows.

4. Chuyển định dạng ngày tháng năm trong Excel theo khu vực

Chẳng hạn bạn nhận được một bảng dữ liệu với định dạng ngày tháng năm bị xếp lộn xộn và công việc của bạn là xếp làm sao cho chúng đồng bộ với nhau. Chẳng hạn lúc này bạn nhận dữ liệu từ Mỹ (tháng/ngày/năm) và chuyển qua thành kiểu châu Âu (Ngày/tháng/năm), sau đây Gitiho sẽ chỉ cho bạn cách thực hiện hóa nhiệm vụ này.

  • Chọn ô ngày tháng năm cần định dạng.
  • Nhấn Ctrl +1 để hiện cửa sổ Format Cells > Number > Date.
  • Ở mục Locale (Location) trong hộp thoại sẽ là nơi mà bạn chọn vị trí, sau đó nhấn Ok để lưu thay đổi. Ngoài ra bạn cũng có thể xem trước định dạng trong phần Sample trước khi nhấn OK.
Chọn định dạng ngày giờ theo khu vực
Chọn định dạng ngày giờ theo khu vực

Xem thêm: Mách bạn 4 cách định dạng thứ trong Excel

5. Tuỳ chỉnh định dạng ngày tháng năm trong Excel 

Nếu như những lựa chọn Excel đưa ra mà bạn không thấy ưng ý thì ta có thể tự tạo cho mình định dạng ngày tháng năm tuỳ chỉnh phù hợp theo yêu cầu.

Bước 1. Lựa chọn ô Excel bạn cần sửa.

Chọn các ô cần tùy chỉnh
Chọn các ô cần tùy chỉnh

Bước 2. Nhấn Ctrl + 1 để hiện Format Cells.

Bước 3. Ở cửa sổ Number, bạn chọn Custom, cùng với đó là điền định dạng bạn muốn trong ô Type bên cạnh.

Custom định dạng ngày tháng năm trong Excel
Custom định dạng ngày tháng năm trong Excel

Ký hiệu phổ biến khi tùy chỉnh ngày tháng năm trong Excel

m

Số tháng chỉ có hàng đơn vị1

mm

Số tháng có thêm hàng chục hoặc thêm số 0 đứng trước (như tháng 10-11-12)01

mmm

Tên tháng với 3 ký tựFeb

mmmm

Tên tháng đầy đủJuly

mmmmm

Tên tháng được ký hiệu bằng chữ cái đứng đầuJ ký hiệu cho June, July, January

d
 

Số ngày không có số 0 đứng trước
 
1

dd
 

Số này có số 0 đứng trước hoặc hàng chục
 
01
 

ddd
 

Tên thứ dạng ngắnWed
 

dddd
 

Tên thứ dạng dài
 
Wednesday
 

yy

Năm hiển thị bằng hai con số21

yyyy

Năm hiển thị đầy đủ 2021

Cần chú ý những ký hiệu sau khi tạo định dạng ngày tháng trong Excel:

h

Giờ không có số 0 đứng trước 0 - 23

hh

Giờ có số 0 đằng trước00-23

m

Phút không có số 0 đằng trước 0-59

mm

Phút có số 0 đằng trước 00-59

Giây không có sô 0 đằng trước0-59

ss

Giây có số 0 đằng trước00-59

AM/P

Buổi sáng/buổi tối6 AM và 6 PM

Bên cạnh đó, khi tạo một định dạng và sử dụng những ký hiệu như "m" sau “hh”, h" hoặc đứng trước “ss”, "s" thì Excel sẽ hiển thị ký hiệu m này là phút chứ không phải tháng.

Khi tạo định dạng, Excel vẫn cho phép bạn sử dụng những ký hiệu đơn giản như dấu phẩy, gạch ngang, gạch chéo, hai chấm.

Chẳng hạn như ta muốn hiểu thị ngày 6 tháng 1 năm 2021, 15 giờ 30 phút thì có rất nhiều cách hiển thị.

dd-mmyy6-Jan-21
mm/dd/yy01/6/2021
m/dd/yy1/6/2021
dddd, m/d/yy h:mm AM/PMWednesday, 1/6/21 3:30PM
ddd, mmmm dd, yyyy hh:mm:ssWed, January 6, 2021 15:30:00
 

Xem thêm: Cách chuyển định dạng ngày tháng trong Excel (DATE) sang TEXT & NUMBER

Tùy chỉnh định dạng ngày giờ theo quốc gia

Nếu như bạn thường xuyên có ra nước ngoài, bạn cần hiện thị đúng thời gian của nơi bạn đang ở bằng cách sử dụng các ký hiệu khu vực tương ứng. Ký hiệu khu vực phải được để trong dấu ngoặc vuông, đi cùng với ký hiệu $ và dấu gạch ngang, chẳng hạn như.

[$-404] Tiếng Trung của Đài Loan

[$-804] Tiếng Trung của Trung Quốc

[$-807] Tiếng Đức của Thụy Sĩ

[$-407] Tiếng Đức của Đức

[$-1009] Tiếng Anh của Canada

[$-409] Tiếng Anh của Mỹ

Chẳng hạn bạn muốn tạo định dạng ngày tháng năm trong Excel theo giờ của Trung Quốc với thứ tự năm/tháng/ngày thì bạn sẽ cần điền thông tin như sau.

Tùy chỉnh định dạng ngày giờ theo quốc gia
Tùy chỉnh định dạng ngày giờ theo quốc gia

Xem thêm: Cách định dạng Ngày Tháng Năm trong Excel chuẩn và thống nhất

6. Sử dụng ngày tháng năm mặc định theo máy tính

Cách này sẽ được sử dụng khi chúng ta muốn chỉnh thời gian về khu vực thời gian của Microsoft đã đặt trước. Định dạng mặc định này sẽ được đánh dấu (*) trong hộp thoại Format Cells.

Sử dụng ngày tháng năm mặc định theo máy tính
Sử dụng ngày tháng năm mặc định theo máy tính

Định dạng này sẽ thay đổi khi bạn thay đổi vùng thời gian ngày tháng năm mặc định của Windows trong Control Panel.

Cách chỉnh ngày tháng năm mặc định trên máy tính

Cách này đặc biệt hữu dụng dành cho những người hay phải đi công tác nước ngoài bởi sự linh hoạt của cách chỉnh thời gian này. 

Ví dụ ta cần đổi định dạng ngày tháng trong Excel từ kiểu Mỹ sang kiểu Anh, ta sẽ làm như sau:

  • Đầu tiên vào Control Panel, chọn Clock and Region
Mở Control Panel
Mở Control Panel để cài đặt định dạng thời gian
  • Lúc này, hộp thoại Region hiện lên, trong tab Format, chỉ cần chỉnh lại khu vực phù hợp trong danh sách của Windows.
Cài đặt lại định dạng về thời gian
Cài đặt lại định dạng về thời gian

Ở hộp thoại này, chúng ta cũng có thể thử cách chuyển đổi từ ngày tháng năm sang tháng/ngày/năm trong Excel ở mục Short Date (từ d/M/yyyy đổi thành M/d/yyyy)

Cài đặt mặc định thời gian bổ sung trên máy tính

Nếu như bạn chưa tìm được lựa chọn phù hợp trong phần Formats, ta đi tới phần Additional Setting phía bên dưới cùng của hộp thoại Region. 

Cài đặt bổ sung của Region
Cài đặt bổ sung của Region

Từ đây hộp thoại Customize sẽ hiện ra và chuyển sang tab Date và nhập định dạng ngày tháng trong Excel mong muốn.

Tùy chỉnh cài đặt mặc định thời gian trên máy tính
Tùy chỉnh cài đặt mặc định thời gian trên máy tính

Xem thêm: Hướng dẫn 3 cách đơn giản sửa lỗi ngày tháng có dấu chấm ngăn cách dạng 14.03.2021

7. Sửa lỗi định dạng ngày tháng năm trong Excel không hoạt động

Tuy việc Excel xảy ra lỗi định dạng là không thường xuyên, nhưng nếu bạn gặp phải trục trặc trong việc thiết lập định dạng thì chúng ta nên xem qua những mẹo sau.

Ô không đủ rộng để chứa hết thông tin ngày tháng năm

Nếu có trường hợp bạn bắt gặp một ô dữ liệu có một loạt ký hiệu ##### thay vì hiện đơn vị thời gian ngày tháng năm thì khả năng ô này không đủ rộng để chứa đầy đủ thông tin.

Chỉnh độ rộng của cột ngày tháng
Chỉnh độ rộng của cột ngày tháng

Đầu tiên, bạn nháy chuột đôi vào bên phải của cột chứa ngày giờ để đưa kích cỡ về vừa đúng với ô thông tin, hoặc kéo cạnh bên phải của ô sang phải cho đến khi ô đủ rộng để chứa thông tin của bạn.

Biến số âm thành định dạng ngày tháng năm trong Excel

Lỗi #### cũng sẽ xuất hiện khi ô dữ liệu được định dạng ngày tháng nhưng lại chứa giá trị âm. Nếu bạn vẫn muốn hiển thị số âm thành ngày âm, Gitiho sẽ hướng dẫn bạn hai cách để thực hiện hóa công đoạn này.

 Cách 1: Đổi sang hệ thống thời gian 1904

Vào phần File -> Option -> Advanced, nhìn xuống phần When calculating this workbook rồi đánh dấu vào ô Use 1904 date system.

Đổi sang hệ thống thời gian 1904
Đổi sang hệ thống thời gian 1904

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

Hiển thị giá trị âm cho phép trừ ngày
Hiện giá trị âm cho phép trừ ngày

Cách hiển thị thời gian kiểu này trông tương đối "kỳ quái" nhưng là cách chính xác nhất để bạn hiển thị phép tính cho ngày trước đó.

Cách 2: Dùng hàm TEXT

Một phương pháp khác để hiển thị ngày âm trên Excel là chức năng hàm TEXT. Nói dễ hiểu, bạn trừ B1 cho C1 và giá trị của C1 lớn hơn B1 thì công thức sau sẽ lợi dụng điều này để ra kết quả dưới dạng ngày tháng năm.

=TEXT(ABS(B1-C1),”-d-mmm-yyyy”)
Dùng hàm TEXT để hiển thị ngày âm
Dùng hàm TEXT để hiển thị ngày âm

Xem thêm: Cách cộng, trừ ngày tháng năm trong Excel để tính số ngày siêu nhanh cho kế toán

Chú ý: Đúng như cái tên của chức năng này, kết quả của TEXT là giá trị chữ, bởi vậy mà bạn không thể dùng kết quả này vào những phép tính toán khác.

Kết luận

Trên đây là những mẹo và thủ thuật sẽ giúp các bạn thuận tiện hơn trong việc thiết lập định dạng ngày tháng năm trong Excel. Gitiho.com mong rằng những kiến thức này sẽ giúp bạn đọc rút ngắn thời gian làm việc mà vân giữ được tính chính xác trong công việc. 

Chúc bạn thành công!

Hiện gói hội viên đang được ưu đãi giảm 200K. Chỉ với 182K/ tháng - Học hơn 500 khóa bất kỳ tại Gitiho! Nhấn vào banner bên dưới để tìm hiểu thêm!

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

0 thảo luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội
Giấy phép mạng xã hội số: 588, cấp bởi Bộ thông tin và truyền thông