Bài này, chúng tôi tập trung về định dạng ngày tháng trong Excel và giải thích làm thế nào để thiết lập các định dạng ngày mặc định và thời gian, làm thế nào để thay đổi định dạng ngày tháng trong Excel, làm thế nào để tạo ra định dạng ngày tùy chỉnh, và chuyển đổi ngày của bạn đi nơi khác.
Cùng với con số, ngày tháng và thời gian là những kiểu dữ liệu phổ biến nhất mà mọi người sử dụng trong Excel. Tuy nhiên, họ có thể được khá khó hiểu để làm việc, bởi vì cùng ngày có thể được hiển thị trong Excel theo nhiều cách khác nhau, và thứ hai, bởi vì Excel luôn trong nội bộ cửa hàng ngày theo định dạng như nhau bất kể thế nào bạn đã định dạng ngày tháng trong một ô nhất định.
Hiểu biết các định dạng ngày của Excel một cách sâu sắc có thể giúp bạn tiết kiệm được rất nhiều thời gian. Và đây chính là mục đích của hướng dẫn toàn diện để làm việc với các ngày trong Excel. Trong phần đầu tiên, chúng tôi sẽ tập trung vào các tính năng sau:
Thiết lập Định dạng ngày Excel
Trước khi bạn có thể tận dụng các tính năng mạnh mẽ của Excel, bạn phải hiểu cách Microsoft Excel lưu trữ ngày tháng và thời gian vì đây là nguồn gây nhầm lẫn chính. Trong khi bạn mong đợi Excel nhớ ngày, tháng và năm cho một ngày, đó không phải là cách hoạt động …
Excel lưu các ngày theo thứ tự và nó chỉ là định dạng của một ô gây ra một số được hiển thị như một ngày tháng, thời gian, hoặc ngày giờ.
Ngày trong Excel
Tất cả các ngày được lưu trữ dưới dạng số đại diện cho số ngày kể từ ngày 1 tháng 1 năm 1900, được lưu trữ như số 1, đến ngày 31 tháng 12 năm 9999 được lưu trữ như là 2958465.
Trong hệ thống này:
- 2 là 2 tháng 1 năm 1900
- 3 là 3 tháng 1 năm 1900
- 42005 là từ 1 tháng 1 năm 2015 (vì đã là 42.005 ngày sau ngày 1 tháng 1 năm 1900)
Thời gian trong Excel
Thời gian được lưu trữ trong Excel dưới dạng thập phân, giữa 0,0 và .99999, biểu thị một tỷ trọng trong ngày, 0,00 là 00:00:00 và 0,99999 là 23:59:59.
Ví dụ:
- 0,25 là 06:00 AM
- 0.5 là 12:00 PM
- 0.541655093 là 12:59:59 PM
Ngày và thời gian trong Excel
Excel lưu trữ các ngày và giờ theo số thập phân bao gồm số nguyên đại diện cho ngày tháng và phần số thập phân đại diện cho thời gian.
Ví dụ:
- 1.25 là ngày 1 tháng 1 năm 1900 6:00 AM
- 42005.5 là ngày 1 tháng 1 năm 2015 12:00 PM
Làm thế nào để chuyển đổi ngày đến số trong Excel
Nếu bạn muốn biết số serial đại diện cho một ngày hoặc thời gian nhất định được hiển thị trong một ô, bạn có thể làm điều này theo hai cách.
1. Hộp thoại Format cells
Chọn ô với một ngày trong Excel, nhấn Ctrl + 1 để mở cửa sổ Format cells và chuyển sang tab General.
Nếu bạn chỉ muốn biết số thứ tự phía sau ngày, mà không thực sự chuyển đổi ngày thành số, hãy ghi lại số bạn thấy dưới Sample và nhấp Cancel để đóng cửa sổ. Nếu bạn muốn thay thế ngày với số trong ô, bấm OK.
2. Các hàm Excel DATEVALUE và TIMEVALUE
Sử dụng hàm DATEVALUE () để chuyển đổi một ngày Excel thành số serial =DATEVALUE("1/1/2015").
Sử dụng hàm TIMEVALUE () để lấy số thập phân đại diện cho thời gian, ví dụ =TIMEVALUE("6:30 AM").
Để biết cả hai, ngày tháng và thời gian, hãy nối hai chức năng này theo cách sau:
=DATEVALUE("1/1/2015") & TIMEVALUE("6:00 AM")
Nếu bạn nhập một ngày như vậy trong một trang tính, nói 12/31/1899, đó sẽ là một giá trị văn bản chứ không phải là một ngày tháng, có nghĩa là bạn không thể thực hiện phép tính số ngày thông thường vào những ngày đầu. Để chắc chắn, bạn có thể gõ công thức =DATEVALUE("12/31/1899")trong một số ô, và bạn sẽ nhận được kết quả mong đợi – #VALUE! error.
Định dạng ngày tháng mặc định trong Excel
Khi bạn làm việc với các ngày tháng trong Excel, các định dạng ngày ngắn và dài được lấy ra từ cài đặt Vùng Windows của bạn. Các định dạng mặc định này được đánh dấu bằng dấu hoa thị (*) trong cửa sổ hộp thoại Format Cell :
Các định dạng ngày và giờ mặc định trong ô Format Cell thay đổi ngay khi bạn thay đổi cài đặt ngày và giờ trong Bảng điều khiển, điều này dẫn chúng ta tới phần kế tiếp.
Làm thế nào để thay đổi định dạng ngày tháng và thời gian mặc định trong Excel
Nếu bạn muốn đặt định dạng ngày hoặc thời gian mặc định khác trên máy tính của mình, ví dụ như thay đổi định dạng ngày ở Hoa Kỳ sang kiểu Anh, hãy chuyển đến Bảng điều khiển và nhấp vào Region and Language. . Nếu trong bảng điều khiển của bạn mở ở chế độ Chuyên mục, sau đó nhấp vào Clock, Language, và Region > Region and Language > Change the date, time, or number format.
Trên tab Format , chọn khu vực trong Format , sau đó đặt định dạng ngày và giờ bằng cách nhấp vào mũi tên bên cạnh định dạng bạn muốn thay đổi và chọn một mục mong muốn từ danh sách thả xuống:
Nếu bạn không hài lòng với bất kỳ định dạng ngày và giờ nào có sẵn trên tab Formats , hãy nhấp vào nút Additional settings ở phía dưới bên phải của cửa sổ hộp thoại Region and Language . Thao tác này sẽ mở hộp thoại Customize , nơi bạn chuyển sang tab Date và nhập định dạng ngày tháng ngắn và / hoặc dài ngày tuỳ chỉnh vào hộp tương ứng.
Cách nhanh chóng áp dụng định dạng ngày tháng và thời gian mặc định trong Excel
Microsoft Excel có hai định dạng mặc định cho ngày tháng và thời gian – ngắn và dài, như được giải thích trong định dạng ngày tháng mặc định của Excel .
Để nhanh chóng thay đổi định dạng ngày trong Excel sang định dạng mặc định, hãy thực hiện theo các bước sau:
- Chọn ngày bạn muốn định dạng.
- Trên tab Home , trong nhóm Number, nhấp vào mũi tên nhỏ bên cạnh hộp Number Format và chọn định dạng bạn muốn – ngày ngắn, ngày dài hoặc thời gian.
Nếu bạn muốn thêm các tùy chọn định dạng ngày, hãy chọn More Forms Number từ danh sách thả xuống hoặc nhấp vào Dialog Box Launcher bên cạnh Number. Thao tác này sẽ mở hộp thoại Format Cells và bạn có thể thay đổi định dạng ngày ở đó.
Làm thế nào để thay đổi định dạng ngày trong Excel
Trong Microsoft Excel, ngày có thể được hiển thị theo nhiều cách khác nhau. Khi nói đến việc thay đổi định dạng ngày của một ô hoặc một dải ô nào đó, cách đơn giản nhất là mở hộp thoại Định dạng ô và chọn một trong các định dạng được xác định trước.
- Chọn ngày có định dạng bạn muốn thay đổi hoặc bỏ trống các ô mà bạn muốn chèn ngày.
- Nhấn Ctrl + 1 để mở hộp thoại Format Cells . Ngoài ra, bạn có thể nhấp chuột phải vào ô đã chọn và chọn Format Cells … từ trình đơn ngữ cảnh.
- Trong cửa sổ Format Cells , chuyển sang tab Number , và chọn Date trong danh mục Category .
- Trong Type, chọn một định dạng ngày mong muốn. Một khi bạn làm điều này, ô Sample sẽ hiển thị xem trước định dạng với ngày đầu tiên trong dữ liệu bạn đã chọn.
- Nếu bạn hài lòng cho bản xem trước, hãy nhấp vào nút OK để lưu thay đổi định dạng và đóng cửa sổ lại.
Nếu định dạng ngày không thay đổi trong bảng tính Excel, nhiều khả năng ngày của bạn được định dạng dưới dạng văn bản và bạn phải chuyển đổi chúng sang định dạng ngày đầu tiên.
Cách chuyển đổi định dạng ngày sang ngôn ngữ khác
Một khi bạn đã có một tập tin đầy đủ các ngày nước ngoài và rất có thể bạn muốn thay đổi chúng sang định dạng ngày được sử dụng trong phần của bạn trên thế giới. Giả sử bạn muốn chuyển đổi định dạng ngày tháng ở Mỹ (tháng / ngày / năm) sang định dạng kiểu châu Âu (ngày / tháng / năm).
Cách dễ nhất để thay đổi định dạng ngày trong Excel dựa trên cách một ngôn ngữ khác hiển thị ngày như sau:
- Chọn cột ngày mà bạn muốn chuyển sang ngôn ngữ khác.
- Nhấn Ctrl + 1 để mở Format Cells
- Chọn ngôn ngữ bạn muốn trong Locale (location) và nhấp vào OK để lưu thay đổi.
Nếu bạn muốn ngày được hiển thị bằng ngôn ngữ khác thì bạn sẽ phải tạo một định dạng ngày tùy chỉnh với mã vùng địa lý .
Tạo một định dạng ngày tùy chỉnh trong Excel
Nếu không có định dạng ngày định dạng Excel nào được xác định trước cho phù hợp với bạn, bạn sẽ được tự do tạo ra.
- Trong một bảng tính Excel, chọn các ô mà bạn muốn định dạng.
- Nhấn Ctrl + 1 để mở hộp thoại Format Cells .
- Trên Số tab, chọn Custom từ type danh sách và gõ định dạng ngày tháng mà bạn muốn trong hộp Type .
Khi thiết lập định dạng custom date trong Excel, bạn có thể sử dụng các mã sau đây.
| Mã | Sự miêu tả | Ví dụ (ngày 1 tháng 1 năm 2005) |
| m | Số tháng mà không có số 0 đứng đầu | 1 |
| mm | Số tháng có số 0 đứng đầu | 01 |
| mmm | Tên tháng, dạng ngắn | Jan |
| mmmm | Tên tháng, đầy đủ mẫu | tháng Giêng |
| mmmmm | Tháng như chữ cái đầu tiên | J (viết tắt của tháng Giêng, tháng Sáu và tháng Bảy) |
| d | Số ngày không có số 0 đứng đầu | 1 |
| đ | Số ngày với số 0 đứng đầu | 01 |
| ddd | Ngày trong tuần, dạng ngắn | Thứ Hai |
| dddd | Ngày trong tuần, hình thức đầy đủ | Thứ hai |
| yy | Năm (2 chữ số cuối) | 05 |
| yyyy | Năm (4 chữ số) | Năm 2005 |
Khi thiết lập định dạng custom time trong Excel, bạn có thể sử dụng các mã sau đây.
| Mã | Sự miêu tả | Hiển thị như |
| h | Số giờ không có số 0 đứng đầu | 0-23 |
| hh | Số giờ với số 0 đứng đầu | 00-23 |
| m | Phút không có số 0 đứng đầu | 0-59 |
| mm | Biên bản với số 0 đứng đầu | 00-59 |
| S | Giây không có số 0 đầu | 0-59 |
| ss | Giây với số 0 đứng đầu | 00-59 |
| AM / PM | Các khoảng thời gian trong ngày (nếu bỏ qua, sử dụng định dạng thời gian 24 giờ) |
AM hoặc PM |
Khi tạo định dạng ngày tùy chỉnh trong Excel, bạn có thể sử dụng dấu phẩy (,) dash (-), dấu gạch chéo (/), dấu hai chấm (:) và các ký tự khác.
Ví dụ: cùng ngày tháng và thời gian, ví dụ January 13, 2015 13:03 , có thể được hiển thị theo nhiều cách khác nhau:
| định dạng | Hiển thị như |
| dd-mmm-yy | 13-Jan-15 |
| mm / dd / yyyy | 01/13/2015 |
| m / rn / 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 một định dạng ngày Excel tùy chỉnh cho một ngôn ngữ khác
Nếu bạn muốn hiển thị ngày tháng bằng một ngôn ngữ khác, bạn phải tạo một định dạng tùy chỉnh và đặt trước một ngày với mã vùng địa lý tương ứng. Mã miền địa phương nên được bao gồm trong [dấu ngoặc vuông] và trước với dấu đô la ($) và dấu gạch ngang (-). Đây là vài ví dụ:
- [$ -409] – Tiếng Anh, Các quốc gia chưa có tiêu đề
- [$ -1009] – Tiếng Anh, Canada
- [$ -407] – Đức, Đức
- [$ -807] – Đức, Thụy Sĩ
- [$ -804] – Bengali, Ấn Độ
- [$ -804] – Trung Quốc, Trung Quốc
- [$ -404] – Trung Quốc, Đài Loan
Bạn có thể tìm thấy danh sách đầy đủ các mã địa phương trên blog này .
Ví dụ: đây là cách bạn thiết lập định dạng ngày tháng Excel tùy chỉnh cho miền địa phương Trung Quốc theo định dạng year-month-day (day of the week) time :
Hình dưới đây cho thấy một vài ví dụ của cùng một ngày được định dạng với các mã địa phương khác nhau theo cách truyền thống cho các ngôn ngữ tương ứng:
Định dạng ngày tháng của Excel không hoạt động – các bản sửa lỗi và giải pháp
Thông thường, Microsoft Excel hiểu ngày tốt và bạn không có khả năng đạt được bất kỳ rào cản nào khi làm việc với họ. Nếu bạn gặp vấn đề định dạng ngày tháng trong Excel, vui lòng kiểm tra các mẹo gỡ rối sau đây
Một ô không đủ rộng để vừa với một ngày
Nếu bạn thấy một số dấu hiệu pound (#####) thay vì ngày trong bảng tính Excel của bạn, nhiều khả năng các ô của bạn không đủ rộng để phù hợp với cả ngày tháng.
Giải pháp . Nhấp đúp vào biên phải của cột để thay đổi kích thước nó để tự động phù hợp với ngày tháng. Ngoài ra, bạn có thể kéo khung bên phải để đặt chiều rộng cột bạn muốn.
Số âm được định dạng là ngày tháng
Trong tất cả các phiên bản hiện đại của Excel 2013, 2010 và 2007, các dấu băm (#####) cũng được hiển thị khi một ô được định dạng như một ngày hoặc thời gian chứa một giá trị âm. Thông thường, đây là kết quả được trả về bởi một số công thức nhưng cũng có thể xảy ra khi bạn nhập giá trị âm vào một ô và sau đó Format Cells đó làm ngày tháng.
Nếu bạn muốn hiển thị số âm là ngày phủ định, bạn có thể chọn hai tùy chọn:
Giải pháp 1. Chuyển sang hệ thống ngày tháng năm 1904.
Đi tới File > Options > Advanced , cuộn xuống phần When calculating this workbook này, chọn hộp kiểm Use 1904 date system và nhấp vào OK .
Trong hệ thống này, 0 là ngày 1 tháng 1 năm 1904; 1 là 2 tháng 1 năm 1904; và -1 được hiển thị dưới dạng ngày âm -2-Jan-1904.
Tất nhiên, biểu hiện như vậy là rất bất thường và mất thời gian để làm quen, nhưng đây là cách đúng để đi nếu bạn muốn thực hiện tính toán với những ngày đầu.
Giải pháp 2. Sử dụng chức năng Excel TEXT.
Một cách có thể khác để hiển thị các số âm là ngày phủ định trong Excel đang sử dụng hàm TEXT. Ví dụ, nếu bạn đang trừ C1 từ B1 và một giá trị trong C1 lớn hơn trong B1, bạn có thể sử dụng công thức sau để xuất kết quả theo định dạng ngày:
=TEXT(ABS(B1-C1),"-d-mmm-yyyy")
Bạn có thể muốn thay đổi liên kết ô sang phải chính xác, và tự nhiên, bạn có thể sử dụng bất kỳ định dạng ngày tùy chỉnh khác trong công thức TEXT.
Ngày được nhập vào Excel dưới dạng các giá trị văn bản
Khi bạn nhập dữ liệu vào Excel từ tệp .csv hoặc một số cơ sở dữ liệu bên ngoài khác, ngày tháng thường được nhập dưới dạng các giá trị văn bản. Họ có thể giống như ngày bình thường với bạn, nhưng Excel nhận thức họ như là văn bản và xử lý theo đó.
Bạn có thể chuyển đổi “text dates” sang định dạng ngày sử dụng chức năng DATEVALUE của Excel hoặc Text to Columns.
Đây là cách bạn định dạng ngày trong Excel. Trong phần tiếp theo của hướng dẫn của chúng tôi, chúng tôi sẽ thảo luận nhiều cách khác nhau về cách bạn có thể chèn ngày tháng và thời gian vào bảng tính Excel của bạn. Cảm ơn bạn đã đọc và gặp bạn vào tuần tới!
