Ghép ngày và giờ trong excel

Nếu bạn thường xuyên sử dụng Excel, tôi chắc chắn rằng bạn đã từng bắt gặp ngày và giờ trong các ô của bạn. Dữ liệu thường có một bản ghi khi nó được tạo ra hoặc cập nhật, vì vậy, biết cách làm thế nào để làm việc với dữ liệu này là điều cần thiết.

Dưới đây là ba kỹ năng quan trọng mà bạn sẽ được học trong hướng dẫn này:

• Cách định dạng ngày trong Excel để chúng xuất hiện theo định dạng ưa thích của bạn
• Các công thức để tính toán số ngày, tháng, và năm giữa hai thời điểm
• Một công thức date trong Excel để xuất ra thời điểm của ngày hôm nay, và một phím tắt để thêm thời điểm hiện tại

Microsoft Excel cơ bản có thể làm bất cứ điều gì với dữ liệu, miễn là bạn chỉ cần biết cách làm. Hướng dẫn này là một bước quan trọng khác để thêm những kỹ năng vào bộ công cụ Excel của bạn. Hãy bắt đầu nào.

Các hàm ngày và giờ trong Excel (Video hướng dẫn)

Video hướng dẫn này sẽ hướng dẫn bạn đi qua cách để làm việc với ngày tháng và giờ trong Excel. Tôi khái quát định dạng ngày tháng với các kiểu khác nhau, cũng như các công thức ngày trong Excel để tính toán và làm việc với ngày. Hãy chắc chắn tải về cuốn bài tập bảng tính Excel miễn phí với các bài tập mà tôi đã đính kèm với hướng dẫn này.

Hãy đọc tiếp tài liệu tham khảo hướng dẫn về cách định dạng ngày và giờ trong Excel, và làm việc với chúng trong các công thức của bạn. Tôi còn sẽ chia sẻ một vài lời khuyên mà không được khái quát ở trong video hướng dẫn.

Nhập ngày tháng và giờ trong Excel

Đối với phần này của hướng dẫn, sử dụng tab có tiêu đề "Typing Dates & Times" trong bài tập ví dụ.

Một trong những chìa khóa để làm việc với ngày tháng và thời gian trong Excel là chụp lấy các dữ liệu một cách chính xác. Dưới đây là cách làm thế nào để nhập ngày tháng và giờ trong bảng tính Excel của bạn:

1. Cách nhập ngày

Tôi khuyên bạn nên nhập ngày theo cùng một định dạng được sử dụng trong hệ thống của bạn. Đối với độc giả người Mỹ của chúng tôi, một ngày đầy đủ sẽ trong định dạng "ngày/tháng/năm". Kiểu châu Âu là "tháng/ngày/năm."

Khi tôi nhập ngày, tôi luôn luôn nhập vào ngày đầy đủ cùng với tháng, ngày và năm. Nếu tôi chỉ muốn hiển thị tháng và năm, tôi sẽ chỉ cần định dạng nó theo cách đó (Bạn sẽ rõ trong chốc lát nữa).

2. Cách nhập thời gian

Rất dễ dàng để nhập thời gian trong Excel. Chúng ta có thể chỉ định bất cứ điều gì từ chỉ là một giờ trong ngày, đến chính xác từng giây mà một thứ gì đã diễn ra.

Nếu tôi muốn nhập thời gian là 4PM, tôi gõ "4 pm" vào một ô trong Excel và sau đó nhấn Enter:

Lưu ý sau khi chúng ta nhấn Enter, Excel chuyển đổi những gì chúng tôi đã nhập vào thành một định dạng dữ liệu giờ:phút:giây.

Đây là cách để nhập một thời gian cụ thể trong bảng tính của bạn:

Điều quan trọng là sử dụng các dấu hai chấm để phân chia phần dữ liệu thời gian, và sau đó thêm một khoảng trắng cộng với "AM" hoặc "PM."

3. Cách nhập ngày và giờ kết hợp

Bạn cũng có thể nhập kết hợp ngày và giờ trong Excel cho các timestamp cụ thể.

Để nhập một sự kết hợp ngày giờ, chỉ cần sử dụng những gì chúng ta đã học được về cách nhập ngày và cách nhập giờ.

Chú ý rằng Excel chuyển đổi giờ sang một định dạng 24 giờ khi nó được sử dụng kết hợp với một ngày, theo mặc định. Nếu bạn muốn thay đổi kiểu của ngày này, hãy đọc tiếp.

Bonus: Phím tắt Excel cho thời gian hiện tại

Một trong những phím tắt yêu thích của tôi Excel để chèn vào thời gian hiện tại vào một bảng tính. Tôi thường sử dụng công thức này, khi tôi để ý thấy khi tôi đã thực hiện một thay đổi dữ liệu của tôi. Hãy thử nó:

Control + Shift + ;

Định dạng ngày tháng trong Excel

Phần này của hướng dẫn, sử dụng tab có tiêu đề "Formatting Dates & Times" trong bài tập ví dụ.

Bạn có thể làm gì khi ngày tháng của bạn theo kiểu châu Âu? Có nghĩa là, chúng đang ở trong một định dạng ngày-tháng-năm, và bạn cần phải chuyển đổi chúng sang các định dạng quen thuộc hơn tháng-ngày-năm?

Trong ảnh chụp màn hình ở trên, những thứ làm bạn ngạc nhiên là tất cả sáu ô đều có chứa chính xác cùng một dữ liệu - "1/22/2017." Những sự khác nhau là cách chúng đang được định dạng trong Excel. Các dữ liệu ban đầu là giống hệt nhau, nhưng nó có thể được định dạng để hiển thị theo nhiều cách khác nhau.

Trong hầu hết các trường hợp, tốt hơn là sử dụng định dạng để thay đổi kiểu hiển thị ngày tháng của chúng ta. Chúng ta không cần phải sửa đổi bản thân các dữ liệu - chỉ cần thay đổi cách trình bày.

Định dạng các ô Excel

Để thay đổi định dạng của dữ liệu ngày tháng và giờ của chúng ta, hãy chắc chắn rằng bạn đang làm việc trên tab Home của Excel. Trên Ribbon (trình đơn ở bên trên của Excel), tìm phần có nhãn Number.

Có một mũi tên nhỏ ở góc dưới bên phải của phần đó. Nhấp vào nó để mở menu Format Cells.

Menu Format Cells có các tuỳ chọn khác nhau cho các kiểu ngày và giờ. Bạn có thể biến "1/22/2017" thành "Sunday, January 22nd" chỉ với định dạng. Sau đó, bạn có thể lấy format painter và thay đổi tất cả các định dạng trên các ô của bạn.

Dành chút thời gian để khám phá menu này và thử các kiểu khác nhau cho ngày và giờ Excel của bạn.

Lấy dữ liệu từ ngày và giờ

Giả sử rằng chúng ta có một danh sách các dữ liệu có ngày và giờ rất cụ thể, và chúng ta muốn để có được phiên bản đơn giản của những công thức đó. Có thể chúng ta có một danh sách các ngày giao dịch chính xác, nhưng chúng ta muốn làm việc với chúng ở một mức độ cao hơn, nhóm chúng theo năm hoặc tháng.

Bạn có thể lấy năm từ một ngày với công thức Excel này:

=YEAR(CELL)

Chỉ lấy tháng từ một ô ngày, sử dụng công thức Excel sau:

=MON(CELL)

Tìm sự khác biệt giữa ngày và giờ

Đối với phần này của hướng dẫn, sử dụng tab có tiêu đề "DATEDIF" trong bài tập ví dụ.

Trong khi các định dạng được sử dụng để thay đổi cách ngày và giờ được trình bày, thì công thức trong Excel được sử dụng để sửa đổi, tính toán, hoặc làm việc với ngày tháng và thời gian bằng cách lập trình.

Công thức DATEDIF là công thức mạnh mẽ để tính toán khoảng cách giữa các ngày. Nhập vào công thức hai ngày và và nó sẽ trả về số ngày, tháng, và năm giữa hai ngày này. Hãy xem cách làm thế nào để sử dụng nó.

1. Số ngày giữa hai ngày

Công thức ngày này trong Excel sẽ tính toán số ngày giữa hai ngày:

=DATEDIF(A1,B1,"d")

Công thức lấy hai ô, ngăn cách bởi dấu phẩy, và sau đó sử dụng một "d" để tính toán khoảng cách giữa hai ngày.

Dưới đây là một số ý tưởng về cách bạn có thể sử dụng công thức Excel ngày này để bạn có thể tận dụng nó:

• Tính toán sự khác nhau giữa hôm nay và ngày sinh của bạn để bắt đầu đếm ngược ngày sinh nhật
• Sử dụng một DATEDIF để tính toán sự khác nhau giữa hai ngày và phân chia sự tăng giá danh mục cổ phiếu của bạn theo số ngày để tính toán sự tăng giá (hoặc giảm!) từng ngày

2. Số tháng giữa hai ngày

DATEDIF cũng tính toán số tháng giữa hai ngày. Công thức ngày trong này trong Excel là rất giống, nhưng thay thế "d" bằng một "m" để tính toán sự khác biệt bằng số tháng:

=DATEDIF(A1,B1,"m")

Tuy nhiên, còn một vấn đề trong cách Excel áp dụng DATEDIF: nó sẽ tính toán những tháng đủ giữa hai ngày. Hãy xem ảnh chụp màn hình dưới đây.

Với tôi, có ba tháng giữa ngày 1 tháng 1 và ngày 31 tháng 3 (Tất cả các ngày của tháng một, tháng hai, và tháng ba.) Tuy nhiên, vì Excel sử dụng các tháng đủ, nó chỉ sẽ xem tháng một và tháng hai là tháng đầy đủ, toàn bộ tháng, do đó, kết quả là "2."

Đây là cách ưa thích của tôi để tính toán số tháng giữa hai ngày. Chúng ta sẽ tìm thấy sự khác biệt bằng ngày, và sau đó chia nó bằng số ngày trung bình trong một tháng - 30.42.

=(DATEDIF(A1,B1,"d")/30.42)

Hãy áp dụng công thức DATEDIF tuỳ biến cho hai ngày:

Tốt hơn rồi. Kết quả 2,99 là rất gần 3 tháng đầy đủ, và điều này sẽ hữu ích hơn nhiều trong các công thức sau này.

Tài liệu chính thức của Excel có một giữa các ngày, nhưng đây là một cách gần giống nhưng đơn giản và dễ dàng. Viết một công thức Excel tốt là việc tìm kiếm điểm chính xác và đơn giản, và công thức này đã làm được cả hai.

3. Các năm giữa hai ngày

Cuối cùng, hãy tính số năm giữa hai ngày. Cách chính chủ để tính toán năm giữa các ngày là với công thức sau:

=DATEDIF(A1,B1,"y")

Lưu ý rằng điều này giống như các công thức DATEDIF trước đây của chúng ta, nhưng chúng ta đơn giản chỉ cần thay thế phần cuối cùng của công thức với "y" để tính toán số năm giữa hai ngày. Hãy xem nó bằng ví dụ sau:

Lưu ý rằng công thức này hoạt động như DATEDIF cho các tháng: nó chỉ đếm năm đủ đã trôi qua. Tôi thích bao gồm năm đã đi qua một phần. Đây là công thức DATEDIF tốt hơn cho năm:

=(DATEDIF(A1,B2,"d")/365)

Về cơ bản, chúng ta chỉ lấy ngày khác nhau trong các ngày, và sau đó chia cho 365 để tính toán nó như là một năm. Đây là kết quả:

DATEDIF cực kỳ mạnh mẽ, nhưng đề phòng cách nó hoạt động: nó sẽ chỉ tính toán các tháng đủ hoặc năm đủ theo mặc định. Sử dụng các phiên bản sửa đổi của tôi để các kết quả chính xác hơn .

Bonus: Số ngày làm việc giữa hai ngày

Các công thức Excel ngày đã khái quát ở trên tập trung vào số ngày giữa hai ngày. Tuy nhiên, đôi khi rất hữu ích chỉ cần tính toán số ngày làm việc (về cơ bản là các ngày trong tuần) giữa hai ngày.

Trong trường hợp này, chúng ta sẽ sử dụng =NETWORKDAYS để tính toán số lượng các ngày làm việc giữa hai ngày.

=NETWORKDAYS(A1,B1)

Trong ảnh chụp màn hình dưới đây, tôi chỉ ra một ví dụ về cách sử dụng NETWORKDAYS. Bạn có thể xem lịch hiển thị cách công thức tính toán một kết quả của "4."

Nếu bạn đã biết những ngày lễ mà bạn muốn loại trừ, hãy xem tài liệu chính thức của NETWORKDAYS.

Tóm tắt và tìm hiểu thêm

Ngày và giờ phổ biến đối bảng tính. Các công thức ngày trong Excel và các tùy chọn định dạng là hữu ích. Các kỹ thuật trong hướng dẫn này có thể nâng cao các kỹ năng Excel lên cấp độ cao hơn do đó bạn có thể kết hợp dữ liệu ngày liên tục vào trong các bảng tính của bạn.

Bạn đã thêm một kỹ năng vào hộp công cụ Excel của bạn - tại sao phải ngừng ở đây? Hãy kết nối các công thức và các kỹ năng Excel để tạo ra bảng tính mạnh mẽ. Để tiếp tục tìm hiểu thêm về làm việc với bảng tính Excel, hãy kiểm tra các tài nguyên khác:

• Câu lệnh IF là câu lệnh logic được tích hợp trong bảng tính của bạn hiển thị kết quả dựa trên điều kiện. Bạn có thể kết hợp một câu lệnh IF với một phạm vi ngày để hiển thị dữ liệu dựa trên thời gian hoặc ngày. Hãy xem hướng dẫn này để tìm hiểu chúng.
• Bạn có thể kết hợp hướng dẫn VLOOKUP với các công thức ngày và giờ để chọn các giá trị dựa trên ngày hoặc giờ.
• Tìm và loại bỏ trùng lặp là một chức năng Excel được sử dụng trong việc kết hợp với dữ liệu ngày và giờ, vì nó thường là một trong các mảng dữ liệu tốt nhất để kiểm tra các trùng lắp.

Bạn làm việc với dữ liệu lịch và thời gian thế nào trong bảng tính Excel của bạn? Có bất kỳ công thức ngày hoặc giờ nào trong Excel mà tôi quên trong hướng dẫn này mà bạn sử dụng thường xuyên? Nếu vậy, hãy cho tôi biết trong phần bình luận nhé.