Cách làm việc với Ngày và Giờ trong Excel

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

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

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

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

Một trong những điều quan trọng để làm việc với ngày và giờ trong Excel là thu thập dữ liệu một cách chính xác. Dưới đây là cách nhập ngày và giờ trong bảng tính Excel của bạn:

1. Cách nhập ngày tháng

Tôi khuyên bạn nên nhập ngày ở cùng định dạng mà hệ thống của bạn sử dụng. Một ngày đầy đủ sẽ có định dạng trong “ngày tháng năm” (day/month/year). Ngày phong cách châu Âu là “tháng ngày năm” (month/day/year).

Khi tôi nhập ngày tháng, tôi luôn nhập đầy đủ ngày 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ỉ định dạng nó theo cách đó (thêm về điều đó trong một phút nữa.)

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

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

Nếu tôi muốn ghi thời gian là 4 giờ chiều, tôi sẽ nhập “4 giờ chiều” vào một ô trong Excel rồi nhấn enter:

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

Đây là cách ghi thời gian cụ thể hơn vào bảng tính của bạn:

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

3. Cách nhập ngày-giờ cùng nhau

Bạn cũng có thể nhập kết hợp ngày và giờ trong Excel để có dấu thời gian rất cụ thể.

Để nhập tổ hợp ngày-giờ, chỉ cần sử dụng những gì chúng ta đã học về cách nhập ngày và thời gian nhập.

Lưu ý rằng Excel đã chuyển đổi thời gian thành định dạng 24 giờ khi định dạng này được sử dụng cùng với ngày theo mặc định. Nếu bạn muốn thay đổi phong cách của ngày này, hãy tiếp tục đọc.

Phím tắt Excel cho Thời gian hiện tại

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

Control + Shift + ;

Định dạng ngày trong Excel

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

Tất cả các ô này chứa chính xác cùng một dữ liệu, chúng chỉ được định dạng theo những cách khác nhau.Trong ảnh chụp màn hình ở trên, điều có thể làm bạn ngạc nhiên là tất cả sáu ô đó đều chứa chính xác cùng một dữ liệu – “22/1/2017”. Điều khác biệt là cách chúng được định dạng trong Excel. Dữ liệu gốc 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 để sửa đổi phong cách của ngày của chúng tôi. Chúng tôi không cần phải sửa đổi dữ liệu – chỉ cần thay đổi cách nó được trình bày.

Định dạng ô Excel

Để thay đổi hình thức của dữ liệu ngày và giờ của chúng tôi, hãy đảm bảo rằng bạn đang làm việc trên tab Home của Excel. trên Ribbon (menu ở đầu Excel), tìm phần có nhãn Con số.

Có một mũi tên nhỏ ở góc dưới bên phải của phần này. Nhấn vào nó để mở Menu định dạng ô (Format Cells menu).

Format Cells menu có nhiều tùy chọn để tạo kiểu cho ngày và giờ của bạn. Bạn có thể biến “22/1/2017” thành “Chủ nhật, ngày 22 tháng 1” chỉ với định dạng. Sau đó, bạn có thể lấy trình vẽ định dạng và thay đổi tất cả các kiểu ô của mình.

Hãy dành thời gian khám phá menu này và thử các kiểu khác nhau cho ngày và giờ trong Excel của bạn.

Nhận dữ liệu từ ngày và giờ

Giả sử rằng chúng ta có một danh sách dữ liệu có ngày và giờ rất cụ thể và chúng ta muốn nhận các phiên bản đơn giản hơn của các công thức đó. Có thể chúng tôi có danh sách ngày giao dịch chính xác, nhưng chúng tôi muốn làm việc với chúng ở cấp độ 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 bằng công thức Excel này:

=YEAR(CELL)

Để chỉ lấy tháng từ một ô ngày, hã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ờ

Trong khi các định dạng được sử dụng để thay đổi cách trình bày ngày và giờ, 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 và giờ theo chương trình.

Công thức DATEDIF rất mạnh để tính toán sự khác biệt giữa các ngày. Cung cấp cho công thức hai ngày và nó sẽ trả về số ngày, tháng và năm giữa hai ngày. Hãy xem cách sử dụng nó.

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

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

=DATEDIF(A1,B1,"d")

Công thức lấy hai ô, được phân tách bằng dấu phẩy, sau đó sử dụng “d” để tính chênh lệch theo 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 ngày Excel này để tạo lợi thế cho mình:

• Tính toán sự khác biệt giữa ngày hôm nay và ngày sinh nhật của bạn để bắt đầu đếm ngược ngày sinh nhật
• Sử dụng DATEDIF để tính chênh lệch giữa hai ngày và chia mức tăng trưởng của danh mục đầu tư chứng khoán của bạn cho số ngày để tính mức tăng (hoặc lỗ!) mỗi 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 này trong Excel rất giống nhau, nhưng thay thế “m” cho “d” để tính chênh lệch theo tháng:

=DATEDIF(A1,B1,"m")

Tuy nhiên, có một điểm bất thường trong cách Excel áp dụng DATEDIF: nó tính toán cả tháng giữa các ngày. Xem ảnh chụp màn hình bên dưới.

Đối với tôi, có ba tháng từ ngày 1 tháng 1 đến ngày 31 tháng 3 (cả tháng 1, cả tháng 2 và gần như cả tháng 3). Tuy nhiên, vì Excel sử dụng cả tháng nên nó chỉ coi tháng 1 và tháng 2 là cả tháng, vì vậy kết quả là “2.”

Đây là cách ưa thích của tôi để tính số tháng giữa hai ngày. Chúng ta sẽ tìm chênh lệch ngày theo ngày, rồi chia cho số ngày trung bình trong một tháng – 30,42 .

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

Hãy áp dụng DATEDIF đã sửa đổi của chúng ta cho hai ngày:

Tốt hơn nhiều. Đầu ra là 2,99 rất gần với 3 tháng đầy đủ và điều này sẽ hữu ích hơn nhiều trong các công thức trong tương lai.

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

3. Số 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 thức để tính năm giữa các ngày là theo 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 tôi, nhưng chúng tôi chỉ đơn giản thay phần cuối của công thức bằng “y” để tính số năm giữa hai ngày. Hãy xem nó hoạt động:

Lưu ý rằng điều này hoạt động giống như DATEDIF trong nhiều tháng: nó được tính chỉ có trọn vẹn năm tháng đã trôi qua. Tôi cũng muốn bao gồm một phần năm trôi qua. Đây là một DATEDIF tốt hơn trong nhiều năm:

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

Về cơ bản, chúng ta chỉ lấy chênh lệch ngày theo ngày, rồi chia nó cho 365 để tính là một năm. Đây là kết quả:

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

Số Ngày làm việc giữa các ngày

Các công thức ngày trong Excel được trình bày ở trên tập trung vào số ngày làm việc giữa các ngày. Tuy nhiên, đôi khi sẽ hữu ích khi chỉ tính 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 tôi sẽ sử dụng =NETWORKDAYS để tính số ngày làm việc giữa hai ngày.

=NETWORKDAYS(A1,B1)

Trong ảnh chụp màn hình bên dưới, tôi đưa ra một ví dụ về việc sử dụng NETWORKDAYS. Bạn có thể xem lịch hiển thị cách công thức tính kết quả là “4”.

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