Trong bài viết này, Blog Học Excel Onnline sẽ hướng dẫn các đặc tính của định dạng thời gian trong Excel cũng như cách thêm dấu mốc thời gian bằng các phím tắt hoặc việc sử dụng hàm NOW để chèn thời gian (mà được cập nhật tự động). Bạn cũng sẽ học cách áp dụng các hàm thời gian trong Excel để cho ra kết quả là giờ, phút hoặc giây so với dấu mốc thời gian đã định.
Microsoft Excel có một số tính năng thời gian hữu ích và việc bạn biết một chút về chúng, đôi khi có thể giúp bạn tiết kiệm được rất nhiều thời gian. Khi dùng các hàm đặc biệt, thì bạn có thể: chèn ngày và giờ hiện tại vào bất kỳ nơi nào trong một bảng tính, chuyển đổi thời gian thành số thập phân, tính tổng các đơn vị thời gian khác nhau hoặc tính thời gian trôi qua.
Định dạng thời gian trong Excel:
Nếu bạn đã làm theo hướng dẫn về Hướng dẫn về Ngày trong Excel của chúng tôi, chắc hẳn bạn sẽ biết rằng Microsoft Excel lưu trữ các ngày như các số thứ tự bắt đầu từ ngày 1 tháng 1 năm 1900, dưới dạng số 1. Bởi vì Excel xử lý thời gian như một phần của một ngày, đồng thời thì thời gian được lưu trữ dưới dạng phân số thập phân.
Trong hệ thống nội bộ của Excel:
- 00:00:00 được lưu trữ dưới dạng 0.0
- 23:59:59 được lưu trữ dưới dạng 0.99999
- 06:00 sáng là 0,25
- 12:00 tối là 0.5
Khi cả ngày và thời gian đều được nhập vào ô, thì chúng được lưu trữ dưới dạng số thập phân bao gồm phần 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ụ: ngày 1 tháng 6 năm 2015 9:30:00 sáng được lưu trữ là 42156.3958333333.
Xem thêm: Hàm SUMIFS - tính tổng theo tuần, tháng, năm
Làm thế nào để có được một số thập phân đại diện trong Excel:
Một cách nhanh chóng để chọn một số thập phân đại diện cho một thời gian cụ thể là sử dụng hộp thoại Format Cells.
Mọi thứ khá đơn giản đấy. Bạn chỉ cần chọn một ô có chứa thời gian và nhấn Ctrl + 1 để mở hộp thoại Format Cells. Trên tab Number, chọn General trong mục Category, và khi đó bạn sẽ thấy thập phân trong hộp Samples.
Bây giờ, bạn có thể ghi lại con số đó và nhấp vào Cancel để đóng cửa sổ. Hoặc, bạn có thể nhấp vào nút OK và có thời gian được thay thế bằng số thập phân tương ứng trong ô. Trên thực tế, bạn có thể nghĩ đến nó như là cách nhanh nhất, dễ dàng nhất và không có công thức để chuyển đổi thời gian thành thập phân trong Excel. Tuy nhiên, chúng tôi sẽ giới thiệu trong phần tiếp theo của bài viết này, và khi đó chúng ta sẽ cùng xem xét kỹ hơn các hàm và phép tính thời gian đặc biệt có khả năng chuyển đổi thời gian sang giờ, phút hoặc giây.
Cách áp dụng hoặc thay đổi định dạng thời gian trong Excel:
Microsoft Excel là đủ thông minh để nhận ra một thời gian khi bạn nhập vào và định dạng các ô một cách phù hợp. Ví dụ: nếu bạn nhập 20:30, hoặc 8:30 chiều hoặc thậm chí 8:30 trong một ô, Excel sẽ giải thích đây là một khoảng thời gian và hiển thị vào 20:30 hoặc 8:30, tùy thuộc vào thời gian mặc định mà bạn đã định dạng.
Nếu bạn muốn định dạng một vài số liệu dưới dạng thời gian hoặc áp dụng định dạng thời gian khác với giá trị thời gian hiện tại, thì bạn có thể thực hiện việc này bằng cách sử dụng hộp thoại Format Cells, như dưới đây.
- Trong một trang Excel, chọn một/những ô mà bạn muốn áp dụng hay thay đổi định dạng thời gian.
- Mở hộp thoại Format Cells bằng cách nhấn Ctrl + 1 hoặc nhấp vào biểu tượng Dialog Box Launcher bế bên Number trong nhóm Number, trên tab Home.
- Trên tab Number, chọn Time từ mục Category và chọn định dạng thời gian mong muốn từ mục Type.
- Nhấn OK để áp dụng định dạng thời gian đã chọn và đóng hộp thoại.
Tạo một định dạng thời gian tùy chỉnh trong Excel:
Mặc dù Microsoft Excel cung cấp khá ít các định dạng thời gian khác nhau, nhưng vấn đề là đôi khi bạn muốn tạo một định dạng của riêng mình phù hợp nhất cho một trang tính cụ thể. Để làm điều này, bạn hãy mở hộp thoại Format Cells lên, chọn Custom từ danh mục Category và gõ định dạng thời gian mà bạn muốn áp dụng trong hộp Type.
Định dạng thời gian tùy chỉnh bạn đã tạo sẽ nằm trong mục Type và hiển thị từ sau lần tiếp theo bạn sử dụng.
Xem thêm: Bài tập Excel có lời giải (dùng hàm Vlookup, hàm IF) Phần 2
Mách nhỏ: Cách dễ nhất để tạo định dạng thời gian tùy chỉnh trong Excel là việc sử dụng một trong các định dạng hiện tại để xuất phát. Tương ứng với việc, bạn phải nhấp vào Time trong mục Category và chọn một trong các định dạng được xác định trước trong mục Type. Sau đó chuyển sang Custom và thực hiện các thay đổi cho định dạng hiển thị trong hộp Type.
Khi tạo định dạng thời gian tùy chỉnh trong Excel, bạn có thể sử dụng các mã sau đây.
Mã Mô tả Để hiện thị h Giờ mà không có số 0 phía trước 0-23 hh Giờ có 2 chữ số 00-23 m Số phút mà không có số 0 phía trước 0-59 mm Số phút có 2 chữ số 00-59 s Số giây mà không có số 0 phía trước 0-59 ss Số giây có 2 chữ số 00-59 AM/PM Khoảng thời gian trong ngày (nếu không được định dạng thì sẽ mặc định sử dụng định dạng 24 giờ) AM or PMMách nhỏ: Để tạo định dạng tùy chỉnh cho ngày tháng và thời gian, hãy sử dụng và kết hợp thông minh các mã thời gian và mã ngày.
Bảng sau đây cung cấp một vài ví dụ về cách các định dạng thời gian trong Excel của bạn:
Định dạng Để hiển thị h:mm:ss AM/PM 1:30:00 PM h:mm 13:30 dddd, m/d/yy h:mm AM/PM Tuesday, 1/13/15 1:30 PM ddd, mmmm dd, yyyy hh:mm:ss Tue, January 13, 2015 13:30:00Định dạng tùy chỉnh trong khoảng thời gian trên 24 giờ:
Khi bạn tính toán thời gian trong Excel, một ô có chứa tổng lượng thời gian thì có thể vượt quá 24 giờ. Để Microsoft Excel hiển thị thời gian vượt quá 24 giờ một cách chính xác, hãy áp dụng một trong các định dạng thời gian tùy chỉnh sau đây.
Định dạng Hiển thị Giải thích [h]:mm 41:30 41 giờ và 30 phút [h]:mm:ss 41:30:10 41 giờ và 30 phút và 10 giây [h] “hours”, mm “minutes”, ss “seconds” 40 giờ, 30 phút, 10 giây d h:mm:ss 1 17:30:10 1 ngày, 17 giờ, 30 phút and 10 giây d “day” h:mm:ss 1 ngày 17:30:10 d “day,” h “hours,” m “minutes and” s “seconds” 1 ngày, 17 giờ, 30 phút và 10 giâyĐịnh dạng thời gian tùy chỉnh cho những giá trị thời gian âm:
Các định dạng thời gian tùy chỉnh được thảo luận ở trên chỉ làm việc với các giá trị mang dấu dương. Nên nếu kết quả tính toán của bạn là giá trị âm (được định dạng dưới dạng thời gian) (ví dụ: khi bạn trừ một số tiền lớn hơn từ một số nhỏ hơn), kết quả sẽ được hiển thị là #####. Còn nếu bạn muốn định dạng các giá trị thời gian âm khác nhau, thì bạn có thể chọn các tùy chọn sau:
- Hiển thị một ô trống cho thời gian âm. Nhập dấu chấm phẩy vào cuối định dạng thời gian, ví dụ [h]: mm;
- Hiển thị một thông báo lỗi. Nhập dấu chấm phẩy vào cuối định dạng thời gian, sau đó gõ một thông báo bằng dấu nháy kép, ví dụ: [H]: mm, “Thời gian âm”
Nói chung, dấu chấm phẩy đóng vai trò là dấu phân tách để phân biệt định dạng các giá trị mang dấu dương và định dạng các giá trị mang dấu âm.
Nếu bạn muốn hiển thị giá trị thời gian mang giá trị âm, ví dụ: -10: 30, thì cách đơn giản nhất là thay đổi hệ thống ngày tháng của Excel sang hệ thống ngày 1904. Để thực hiện việc này, hãy nhấp vào File>Options>Advanced, kéo xuống When calculating this workbook section (Khi tính toán phần này trong bảng tính) và kiểm tra hộp Use 1904 date system (sử dụng hệ thống ngày năm 1904)
Định dạng thời gian mặc định trong Excel:
Khi thay đổi định dạng thời gian trong hộp thoại Format Cells, bạn có thể nhận thấy rằng có một trong các định dạng này bắt đầu với dấu hoa thị (*). Đây là định dạng thời gian mặc định trong Excel của bạn.
Để nhanh chóng áp dụng định dạng thời gian mặc định của Excel vào ô hoặc một dải ô đã chọn, hãy nhấp vào mũi tên thả xuống trong nhóm Number, trên tab Home và chọn mục Time.
Để thay đổi định dạng thời gian mặc định, bạn vào Control Panel và nhấp Region and Language. Nếu trong bảng điều khiển của bạn mở ở chế độ xem Category, hãy nhấp vào Clock, Language, and Region> Region and Language> Change the date, time, or number format.
Lưu ý: Khi bạn muốn tạo một định dạng thời gian mới của Excel hoặc muốn sửa đổi định dạng hiện tại, hãy nhớ rằng bất kể bạn đã chọn cách nào để hiển thị thời gian trong một ô, thì Excel luôn luôn lưu trữ nội bộ các thời gian với cùng một cách - bằng các chữ số thập phân.
Làm thế nào để chèn thời gian và một mốc thời gian trong Excel:
Có nhiều để chèn thời gian vào Excel, nhưng việc bạn sử dụng cái nào thì còn tùy thuộc vào việc bạn muốn có một mốc thời gian cố định hay một giá trị thay đổi (mà được cập nhật tự động để cho thấy thời gian hiện tại).
Thêm vào mốc thời gian bằng các phím tắt:
Nếu bạn đang tìm kiếm một cách để chèn mốc thời gian vào ô Excel, tức là một giá trị tĩnh (không tự động cập nhật dù cho bảng tính được tính lại), hãy sử dụng một trong các phím tắt sau:
- Chèn thời gian hiện tại, nhấn Ctrl + Shift +;
- Chèn thời gian hiện tại: Nhập ngày và giờ hiện tại, nhấn Ctrl +; để chèn một ngày, sau đó bấm phím Space (phím cách), rồi nhấn Ctrl + Shift +;
Thêm ngày tháng hiện tại và thời gian hiện tại bằng hàm NOW
Nếu mục đích của bạn là chèn ngày và giờ hiện tại để làm một giá trị động (tự động được cập nhật), hãy sử dụng hàm NOW trong Excel.
Công thức đơn giản như nó có thể được ghi như sau: (không có đối số bắt buộc)
= NOW ()
Khi sử dụng hàm NOW trong Excel, có một số điều cần lưu ý:
- Hàm NOW truy xuất thời gian từ đồng hồ hệ thống của máy tính.
- Hàm NOW là một trong những hàm dễ thay đổi trong Excel, nghĩa là các ô có công thức này đều sẽ được tính mỗi khi bảng tính được mở lại hoặc tính lại.
- Để buộc hàm NOW cập nhật giá trị đã được trả về, nhấn Shift + F9 để tính toán lại bảng tính hiện tại hoặc F9 để tính toán lại tất cả các bảng tính đang mở.
- Để có được hàm NOW tự động cập nhật trong khoảng thời gian nhất định, hãy thêm một macro VBA vào bảng tính của bạn, có thể tham khảo một vài ví dụ có sẵn ở đây.
Chèn thời gian hiện tại làm một giá trị động
Nếu bạn chỉ muốn chèn thời gian hiện tại vào ô mà không chèn ngày, thì dưới đây cócác lựa chọn để bạn tham khảo:
- Sử dụng công thức = NOW (), và sau đó áp dụng các định dạng thời gian trong 1 hay nhiều ô.
Hãy nhớ, điều này sẽ chỉ thay đổi định dạng hiển thị, giá trị thực tế được lưu trữ trong một ô vẫn sẽ là một số thập phân gồm phần nguyên đại diện cho ngày và phần thập phân thể hiện thời gian.
- Sử dụng công thức sau: = NOW () - INT (NOW ())
Hàm INT được sử dụng để làm tròn số thập phân được trả về bởi NOW () xuống số nguyên gần nó nhất. Và sau đó, bạn trừ đi phần nguyên đại diện cho ngày hôm nay để chỉ ra phần thập phân đại diện cho thời gian hiện tại.
Vì công thức trả về là dạng thập phân, nên bạn sẽ cần phải áp dụng định dạng thời gian vào các ô khiến giá trị hiển thị dưới dạng thời gian.
Ảnh chụp màn hình sau minh họa cả hai công thức trên. Chú ý rằng dù các giá trị định dạng thời gian giống nhau (cột C), thì các giá trị thực tế được lưu trữ trong các ô (cột D) vẫn khác nhau - D4 chỉ chứa phần thập phân:
Chèn thời gian bằng hàm TIME:
Hàm TIME trong Excel được sử dụng để chuyển đổi một chuỗi văn bản hiển thị thời gian thành một số thập phân đại diện cho thời gian.
Cú pháp của hàm Excel TIME rất đơn giản:
= TIME (hour,minute,second)
Các đối số hour (giờ), minute (phút) và second (giây) có thể được nhập vào dưới dạng số từ 0 đến 3276.
Nếu hour lớn hơn 23, thì nó sẽ chia cho 24 và phần còn lại sẽ được lấy làm giá trị giờ.
Ví dụ, TIME (30, 0, 0) tương đương với TIME(6,0,0), đều là 0,25 hoặc 6 giờ sáng
Nếu minute lớn hơn 59, nó được chuyển thành giờ và phút. Và nếu second lớn hơn 59, thì chuyển thành giờ, phút và giây.
Ví dụ, TIME (0, 930, 0) được chuyển thành TIME (15, 30, 0), đều là 0,645833333 hoặc 15:30.
Thực sự thì hàm TIME rất hữu ích khi kết hợp các giá trị riêng lẻ vào một giá trị thời gian đơn, ví dụ giá trị trong các ô khác hoặc trả về bởi các hàm Excel khác.
Làm thế nào để có được giờ, phút và giây từ mốc thời gian:
Để lấy ra được từng đơn vị thời gian trong mốc thời gian, bạn có thể sử dụng hàm công thức thời gian như dưới đây:
HOUR(serial_number) - trả về giá trị giờ của thời gian đó, đây là một số nguyên chạy từ 0 (12:00 sáng) đến 23 (11:00 tối)
MINUTE(serial_number) - trả về số phút trong giá trị thời gian đó, có dạng là 1 số nguyên có giá trị trong khoảng từ 0 đến 59.
SECOND(serial_number) - trả về số giây trong giá trị thời gian đó, là một số nguyên có giá trị trong khoảng từ 0 đến 59.
Xem thêm: Hàm thời gian thông dụng trong Excel
Trong cả 3 công thức trên, bạn có thể nhập dữ liệu thời gian ở dạng chuỗi và đặt nó trong dấu nhát kép (ví dụ, “6:00 sáng”), hoặc dưới dạng phân số (ví dụ, 0.25 có nghĩa là 6:00 sáng), hay là kết quả trả về của các hàm Excel khác. Bạn có thể xem thêm 1 vài công thức ví dụ ở dưới đây:
- =HOUR(A2) - trả về số giờ trong mốc thời gian ở ô A1
- =MINUTE(A2) - trả về số phút trong mốc thời gian ở ô A1
- =SECOND(A2) - trả về số giây trong mốc thời gian ở ô A1
- =HOUR(NOW()) - trả về giờ hiện tại
Bây giờ, sau khi bạn đã hiểu về các định dạng thời gian và các hàm thời gian trong Excel, thì nó hẳn là dễ dàng hơn nhiều cho bạn để thao tác ngày tháng và thời gian trong bảng tính. Trong phần tiếp theo của hướng dẫn về thời gian trong Excel của chúng tôi, chúng ta sẽ cùng thảo luận các cách khác nhau để chuyển đổi thời gian thành số. Cảm ơn bạn đã đọc và hy vọng được gặp bạn trên blog của chúng tôi vào tuần tới!
———
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table… Khi kết hợp tốt các hàm và các công cụ với nhau thì bạn sẽ thấy hiệu quả công việc trên Excel sẽ được cải thiện rất nhiều.
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học
