Lực td
Lực td
Thảo luận 0 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 7486 lượt xem

Hướng dẫn tạo định dạng mặc định và thay đổi ngày tháng trên Excel mới nhất

Jan 07 2021

Bài viết này Gitiho.com sẽ cùng bạn tìm hiểu về cách định dạng ngày tháng trong Excel tuỳ theo thay đổi của địa điểm, cũng như đặt thành thời gian mặc định của Excel.

Đăng ký ngay Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ

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

Trên thực tế, mỗi lần bạn nhập một dữ liệu thời gian ngày tháng năm vào Excel, ứng dụng này sẽ không phân biệt được đây là ngày tháng năm mà đơn giản Excel sẽ lưu trữ lại nó như một dãy số với ký tự tách từng số với nhau. Chính vì vậy mà định dạng ô chứa thông tin sẽ không  "bắt sóng" được đây là định dạng về thời gian.

Bởi vậy đây là lí do chính gây khó khăn khi người sử dụng muốn lưu trữ dữ liệu về thời gian trong Excel.

Cách Excel tính toán thời gian

Định nghĩa thời gian trong Excel là Giờ-phút-giây và được ghi nhận là số thập phân. Thời gian sẽ tính từ giữa .0 và .99999 tính theo thời gian được phân chia trong ngày. Cụ thể như sau.

  • Số 0 nghĩa là 0:00:00.
  • 0.25 nghĩa là 06:00 giờ sáng.
  • 0.5 nghĩa là 12:00 giờ trưa.
  • 0.541655093 là 12:59:59 tối (12:59:59 PM).

Cách Excel tính ngày tháng năm

Theo thông tin từ chính tập đoàn 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 thay đổi định dạng ngày tháng mặc định trên Excel

Hướng dẫn định dạng ngày tháng trong Excel thành số

Tạo cửa sổ chứa định dạng ngày tháng trong Excel theo định dạng ô

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 để hiện hộp thoại Format Cell và chọn General.

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 hãy viết dãy số ấy ở ô Sample và nhân Cancel để đóng hộp thoại. Và để đổi ngày tháng năm thành số chỉ cần nhấn OK. 


Xem thêm: CÁCH ĐỊNH DẠNG Ô TRONG EXCEL

Cách sử dụng hai chức năng Datevalue và Timevalue để đổi định dạng ngày tháng trong Excel

Công thức Datevalue trong Excel để đổi định dạng ngày tháng trong Excel từ ngày tháng năm sang một dãy số dài tượng chưng. Chẳng hạn như =DATEVALUE(“1/1/2021”).


Còn chức năng TIMEVALUE() để chuyển thời gian ngày tháng năm thành một dãy số thập phân, chẳng hạn như =TIMEVALUE(“6:30 AM”).


Để 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”)

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ẽ là chữ thay vì dưới dạng ngày tháng năm. Nếu như bạn nhập =DATEVALUE(“12/31/1899”), thì chắc chắn Excel sẽ hiển thị lỗi #VALUE!

Xem thêm: Hướng dẫn cách dùng hàm TIMEVALUE trong Power BI DAXT

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

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.


Đị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 đổi định dạng ngày tháng trong Excel và thời gian

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ẽ vào Control Panel, chọn phần Region and Language. Còn khi bạn mở Control Panel mà hiện ra Category thì chúng ta vào phần Clock  -> Language and Region > Region and language  > Change the date, time or number format.

Trong tab Format, chỉ cần chỉnh lại khu vực phù hợp trong danh sách của Windows.


Ở đây chúng ta sẽ thấy dòng chữ như dd/MM/yy tức là dd = day = ngày, mm = Month = tháng và cuối cùng là yy = Year = Năm. Hoặc để biết rõ hơn, chúng ta bấm vào dòng chữ xanh "What does the notation mean” bên dưới tab First day of week.

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 Region and Language. 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.

Xem thêm: Cách tính thời gian làm việc giữa hai thời điểm cụ thể theo ngày tháng năm chính xác

Cách thao tác nhanh đổi định dạng ngày tháng trong Excel và thời gian mặc định

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.

Để đổi định dạng 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.

- Trong phần Home sẽ có thêm phần Number , nhấn con trỏ chuột vào cạnh ô Number Format rồi chọn định dạng mà bạn yêu cầu gồm Short date, long date và time.

Bên cạnh đó Excel cũng sẽ đưa ra một vài tuỳ chọn khác qua tab More Number Format từ danh sách, hoặc là Dialog Box Launcher bên cạnh phần Number. Từ đây hộp thoại Format Cells sẽ mở ra tương tự trên và bạn có thể thay đổi mặc định trong đó. 

Lưu ý: nếu bạn muốn chuyển đổi thời gian nhanh thành form thời gian 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à không quan trọng đinh dạng ngày tháng trong Excel của Windows.

Xem thêm: Định dạng ngày tháng trong Excel bằng VBA

Tạo định dạng thời gian ngày tháng năm tuỳ chỉnh

Nếu như những lựa chọn mà Excel đưa ra cho người dụng 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.

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

2. Nhấn Ctrl + 1 để hiện Format Cells.

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

Mẹo: Cách đơn giản nhất là dựa trên định dạng có sẵn mà tạo nên định dạng gần giống với định dạng mà bạn muốn. Bước đầu tiên là ta vào ô Date trong bảng Category, chọn một trong những định dạng có sẵn trong Type, rồi tiếp đến phần Custom thay đổi định dạng mà bạn muốn.

Sau khi tạo định dạng ngày tháng trong Excel, bạn dùng những ký hiệu phù hợp sau đây.


MSố tháng chỉ có hàng đơn vị1
mmSố tháng có thêm hàng chục hoặc thêm số 0 đứng trước (như tháng 10-11-1201
mmmTên tháng với 3 ký tựFeb
mmmmTên tháng đầy đủFebuary

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
yyNăm hiển thị bằng hai con số21
yyyyNă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.

hGiờ không có số 0 đứng trước 0 - 23
hhGiờ có số 0 đằng trước00-23
mPhút không có số 0 đằng trước 0-59
mmPhút có số 0 đằng trước 00-59
Giây không có sô 0 đằng trước0-59
ssGiây có số 0 đằng trước00-59
AM/P,Buổi sáng/buổi tối6AM và 6PM

Bên cạnh đó, khi bạn muốn tạo một định dạng ngày tháng trên Excel và sử dụng những ký hiệu như "m" sau "hh" hoặc "h" hoặc đứng trước "ss" hoặc "s" thì Excel sẽ hiển thị ký hiệu 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:ss

Wed, January 6, 2021 15:30:00

Xem thêm: Chuyển dữ liệu ngày tháng về dạng chữ trong Excel

Tạo định dạng ngày tháng trên Excel theo địa điểm khác nhau

Nếu như bạn là người thường xuyên đi công tác nước ngoài thì đây là một cách hữu hiệu để hiện thị đúng thời gian của nơi bạn đang làm việc 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, đu 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 trong Excel theo giờ của người hàng xóm 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.

Xem thêm: Định dạng ngày tháng có địa danh bằng VBA

Cách sửa lỗi định dạng ngày tháng của Excel không hoạt động và cách khắc phục

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.

Dữ liệu thời gian ngày tháng năm nhập vào Excel coi như giá trị chữ

Khi bạn nhập bất kỳ dữ liệu nào khác bên ngoài Excel thì thời gian sẽ thường thể hiện qua dạng chữ (kể cả file .csv). Về ngữ nghĩa thì vẫn đúng ngôn ngữ nhưng Excel vẫn chỉ coi đây là một dòng văn bản không hơn không kém.

Để khắc phục lỗi này bạn có thể dùng một trong hai chức năng là Datavalue hoặc Text to Clolumns

Ô 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ông khỏi khả năng ô này không đủ rộng để chứa đầy đủ thông tin.

Đầu tiên, bạn nháy chuột đôi vào bên phải của ô để đư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 trong Excel

Lỗi #### không chỉ hiện ra khi ô trong Excel không đủ rộng mà cả các phiên bản Excel từ 2010 đến 2017, ký hiệu #### cũng sẽ xuất hiện như một ô 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.


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.

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 TEXT

Một phương pháp khác để hiển thị ngày âm trên Excel là chức năng TEXT. Nói dễ hiểu, bạn trừ C1 cho B1 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”)


Bên cạnh đó bạn có thể dùng bất kỳ định dạng ngày tháng trong Excel nào do bạn tạo ra từ TEXT thành căn lề phải.

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.

Xem thêm: Hướng dẫn chuyển đổi định dạng date thành text trong Excel bằng hàm TEXT và không sử dụng công thức

Thay đổi định dạng ngày tháng trong Excel

Đơn vị thời gian trong Excel sẽ được hiển thị theo nhiều cách khác nhau theo tùy chỉnh của người sử dụng. Và mỗi khi thay đổi định dạng trong một hay nhiều ô mà không lặp đi lặp lại thì bạn hãy mở hộp thoại Format Cells và chọn một trong những định dạng dưới đây.

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.

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

3. Cửa sổ Format Cells hiện ra, ta chọn tab Number Date ở bảng Category.

4. Ở phần Type, lựa chọn đình dạng ngày tháng năm mà bạn muốn. Sau đó trong ô Sample sẽ hiển thị ví dụ tương tự như dữ liệu ngày tháng năm bạn nhập vào ô.


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ố.

Chuyển đổi định dạng ngày tháng 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.

- Ở hộp thoại Locale (Location) trong hộp thoại sẽ là nơi mà bạn chọn vị trí, sau đó nhấn Ok để lưa 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.

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 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.

Bạn đọc cũng có thể tìm hiểu những bài đọc bổ ích khác.

Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất

Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel

Hướng dẫn cách sắp xếp ngày trong Excel

Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao

Hướng dẫn cách sử dụng hàm IF với nhiều điều kiện: AND, OR, hàm IF lồng nhau và hơn thế

Cùng tham gia cộng đồng hỏi đáp về chủ đề Excel Cơ Bản

Thảo luận 0 câu trả lời
Lượt xem 7486 lượt xem
Vỗ tay vỗ tay

0 Bình 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