Biến đổi thời gian thành số thập phân, giờ phút giây trong Excel

Bài viết dưới đây do Siêu Marketing cung cấp, sẽ chỉ đường dẫn cho các bạn các phương pháp đa dạng để biến thời gian thành số thập phân, quy đổi giờ thành phút trong excel cũng như cách làm ngược lại.

Biến đổi giá trị thời gian thành dạng số thập phân trong Excel:

Tóm lại, chúng ta có 3 phương pháp để quy đổi giá trị thời gian thành dạng số thập phân: sử dụng phép tính, hàm CONVERT, hoặc kết hợp 3 hàm liên quan đến thời gian với nhau.

Phương pháp đơn giản nhất để biến thời gian thành số thập phân trong Excel là thông qua việc nhân giá trị thời gian với số giờ, số phút, số giây có trong một ngày:

Thực hiện quy đổi thời gian sang giờ bằng cách nhân giá trị thời gian với 24 (tổng số giờ trong một ngày).

Để biến thời gian thành phút, hãy nhân giá trị thời gian đó với 1440 (tổng số phút trong một ngày là 24 nhân với 60).

Để quy đổi thời gian thành giây, hãy nhân giá trị thời gian đó với 86400 (tổng số giây trong một ngày là 24 nhân với 60 nhân với 60).

 Convert time to a decimal number in Excel

Biến đổi thời gian sang giờ trong Excel:

Trong phần này, chúng ta sẽ tìm hiểu 3 công thức khác nhau để biến đổi thời gian từ định dạng chuẩn (hh:mm:ss) sang dạng số thập phân.

Công thức 1: Sử dụng phép toán

Phương pháp nhanh nhất để biến đổi giá trị thời gian thành số giờ trong Excel là nhân với 24, tương đương với số giờ có trong một ngày:

=A2*24 (Trong đó A2 là giá trị thời gian)

Để có được số giờ đầy đủ, hãy dùng công thức hàm INT nhằm loại bỏ phần thập phân: =INT(A2*24)

 The formula and arithmetic calculation to convert time to hours in Excel

Công thức 2: Hàm CONVERT

Một cách thứ hai để chuyển thời gian thành giờ là qua việc sử dụng hàm CONVERT:

=CONVERT(A2, “day”, “hr”)

Công thức 3: Sử dụng hàm HOUR, MINUTE, SECOND

Công thức cuối cùng là sử dụng một cách phức tạp hơn, bằng cách lấy ra từng đơn vị thời gian thông qua các hàm HOUR, MINUTE, SECOND, sau đó chia số phút cho 60 và giây cho 3600 và tiến hành tính kết quả:

=HOUR(A2) + MINUTE(A2)/60 + SECOND(A2)/3600

Biến đổi thời gian sang phút trong Excel

Có ba kỹ thuật tương tự có thể áp dụng để chuyển đổi giá trị thời gian từ dạng chuẩn thành số thập phân khi muốn có phút.

Công thức 1: Sử dụng phép toán

Quy đổi thời gian thành phút, bạn chỉ cần nhân giá trị thời gian với 1440 – số phút trong một ngày.

=A2*1440 (A2 đại diện cho giá trị thời gian)

Để có phút đầy đủ, sử dụng công thức hàm INT giống như ví dụ được nêu trước đây: =INT(A2*1440). Kết quả sẽ hiện ra như hình sau đây:

 Converting time to minutes in Excel

Công thức 2: Hàm CONVERT

Dùng hàm CONVERT(number, from_unit, to_unit) để chuyển giờ thành phút, bạn chỉ cần nhập ‘day’ cho from_unit và ‘mn’ cho to_unit:

=CONVERT(A2, “day”, “mn”)

Công thức 3: Sử dụng hàm HOUR, MINUTE, SECOND

Phương pháp khác để tính số phút là nhân số giờ lấy được với 60 và chia số giây cho 60:

=HOUR(A2)*60 + MINUTE(A2) + SECOND(A2)/60

Phương pháp chuyển đổi thời gian sang giây trong Excel

Công thức thứ nhất: Tính toán số học

Nhân đơn vị thời gian cho 86400 để quy đổi sang giây, do trong một ngày có tương ứng 86400 giây (24 giờ nhân 60 phút nhân 60 giây).

=A2*86400

Converting time to seconds in Excel

Công thức thứ hai: Sử dụng hàm CONVERT

Công thức này tương tự như minh họa trước, chỉ thay đổi ‘day’ thành ‘sec’ cho việc quy đổi:

=CONVERT(A2, “day”, “sec”)

Công thức thứ ba: Kết hợp hàm HOUR, MINUTE, SECOND

Áp dụng các hàm HOUR, MINUTE, SECOND giống như hai công thức đã nêu:

=HOUR(A2)*3600 + MINUTE(A2)*60 + SECOND(A2)

Lưu ý:

  1. Trong trường hợp công thức nào cho ra kết quả có định dạng time, bạn chỉ cần đổi sang định dạng General để kết quả hiển thị dưới dạng số.
  2. Để biến thời gian thành số thập phân, hãy đặt định dạng General cho ô đó. Ví dụ, 23:59:59 sẽ quy đổi thành 0.99999, 06:00 AM thành 0.25 và 12:00 PM thành 0.5. Nếu số sau khi quy đổi có phần nguyên lớn hơn 0, điều đó biểu thị ô chứa thông tin ngày tháng cụ thể ngoài thời gian.

Phương pháp phân tách ngày và giờ trong Excel

Thường thấy trong Excel, cả ngày và giờ có thể được lưu trong một ô duy nhất, nhưng đôi khi cần chia chúng thành hai ô khác nhau.

Xin nhắc là trong Excel, ngày được biểu diễn bằng phần nguyên và thời gian được mô tả bởi phần thập phân của giá trị. Dùng hàm INT để trích xuất giá trị ngày – hàm này làm tròn xuống số nguyên gần nhất từ giá trị ô tính.

Để thực hiện việc phân tách khi ngày tháng và thời gian ban đầu nằm ở cột A, sử dụng công thức sau đây:

=INT(A2)

Use the INT function to extract the date values

Để lấy phần thời gian, bạn trừ giá trị này từ giá trị ngày tháng và thời gian ban đầu:

=A2-B2

Cột A chứa ngày tháng và thời gian gốc, trong khi cột B chứa giá trị ngày đã được tính toán qua hàm INT.

Nếu muốn thời gian không gắn với ngày (ví dụ bạn muốn xóa cột ngày sau này), dùng hàm MOD với công thức sau:

=MOD(A2,1)

Splitting date and time in Excel

Lưu ý: Nếu bạn muốn các giá trị của ngày và thời gian hiện thị chính xác, hãy thay đổi định dạng của các cột mới thành DateTime tương ứng.

Biểu diễn thời gian bằng văn bản trong Excel

Đôi khi bạn muốn biến thời gian thành dạng chữ như “# ngày, # giờ, # phút và # giây”. Khi ấy, các bước để tạo công thức như sau:

  • Dùng hàm INT để trích xuất số ngày
  • Áp dụng các hàm HOUR, MINUTESECOND để lấy các đơn vị thời gian cụ thể.
  • Ghép tất cả các phần này vào một công thức duy nhất

Nếu giả sử bạn có thời gian bắt đầu sự kiện trong cột B từ ô B4, và giá trị thời gian hiện tại trong ô B1 có thể lấy từ hàm =NOW().

Công thức để tính toán sự chênh lệch thời gian“`html

Tính năng này vô cùng đơn giản với công thức = B4 – $B$1. Bạn cũng có thể thực hiện việc trừ trực tiếp số liệu thời gian hiện tại qua cách sử dụng = B4 – NOW().

Ta sẽ thiết lập một bộ đếm ngược, thể hiện số ngày, giờ, phút và giây đếm ngược cho đến khi từng sự kiện diễn ra.

Setting up a countdown timer in Excel

Công thức cho ô D4 sẽ là:

=INT(C4) & ” ngày, ” & HOUR(C4) & ” giờ, ” & MINUTE(C4) & ” phút và ” & SECOND(C4) & ” giây”

Đối với các trường hợp giá trị bằng 0, như thấy ở ô D6, D7, bạn có thể áp dụng hàm IF như sau:

=IF(INT(C4)>0, INT(C4)&” ngày, “,””) & IF(HOUR(C4)>0, HOUR(C4) & ” giờ, “,””) & IF(MINUTE(C4)>0, MINUTE(C4) & ” phút và “,””) & IF(SECOND(C4)>0, SECOND(C4) & ” giây”,””)

Như vậy, ta đã loại bỏ những giá trị bằng 0.

Lưu ý: Khi số liệu đưa ra từ công thức trên là số âm, lỗi #NUM! sẽ được hiển thị. Điều này diễn ra do việc trừ một số nhỏ hơn từ một số lớn hơn.

Đổi định dạng từ text sang time trong Excel

Nếu bạn gặp lỗi với công thức thời gian do định dạng text, hãy sử dụng hàm TIMEVALUE.

=TIMEVALUE(time_text)

Trong đó, time_text là chuỗi text mà Excel có thể hiểu được, chẳng hạn:

=TIMEVALUE(“6:20 PM”)

=TIMEVALUE(“6-Jan-2015 6:20 PM”)

=TIMEVALUE(A2), khi ô A2 chứa chuỗi text đó.

Convert text to time using the Excel TIMEVALUE function

Bạn có thể thấy rằng công thức chứa tham chiếu đến ô với chuỗi text cho kết quả tương tự nhau. Thêm vào đó, cần chú ý rằng chuỗi text thời gian được căn lề trái trước khi chuyển đổi và căn lề phải sau khi chuyển đến định dạng time trong cột D.

Đổi định dạng time sang text trong Excel

Nếu bạn có dữ liệu Excel với định dạng time như “8:30:00 AM” và muốn chuyển nó sang text, thay đổi định dạng ô sang text không khả thi vì điều này sẽ biến giá trị thời gian thành dạng số, chẳng hạn 8:30:00 AM sẽ trở thành 0.354166666666667 dưới dạng số thập phân.

Hãy áp dụng hàm TEXT để chuyển đổi giá trị số thành text, cụ thể là:

=TEXT($A2,”h:mm:ss”)

Hình dưới đây minh họa một số định dạng khác:

Converting time to text in Excel

Đổi định dạng number sang time trong Excel

Khi bạn có một danh sách số như 1, 2, 3.5 và bạn muốn định dạng chúng thành giờ, ví dụ 1:00:00, 2:00:00 hoặc 3:30 AM, làm theo các bước sau:

  1. Chia những số đó cho 24 (bởi có 24 giờ trong một ngày). Công thức là =A2/24.
  2. Chọn ô có kết quả công thức, chuột phải và chọn Format Cells hoặc bấm Ctrl + 1. Trong hộp thoại Format Cells, chọn Time từ phần Category ở phía bên trái và định dạng mong muốn từ phần Type ở phía bên phải.

Converting numbers to the time format in Excel

Để mở rộng kiến thức về Excel, bạn có thể tham gia khóa học SM90 – Excel từ cơ bản đến nâng cao dành cho người đi làm của Siêu Marketing, nơi cung cấp những kiến thức hoàn chỉnh và có hệ thống về các hàm, công cụ trong Excel và cách ứng dụng chúng vào công việc. Hiện tại, có nhiều ưu đãi dành cho người đăng ký khóa học này. Xem thông tin chi tiết tại:

“`

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop