Cách chuyển đổi thời gian thành số thập phân, giờ phút giây trong Excel

Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn nhiều cách khác nhau để chuyển đổi thời gian thành số thập phân, đổi giờ ra phút trong excel và ngược lại.

Cách chuyển đổi thời gian thành số thập phân trong Excel:

Nhìn chung, có 3 cách để chuyển giá trị thời gian thành số thập phân : phép toán, hàm CONVERT hoặc lồng ghép 3 hàm thời gian với nhau.

Cách dễ nhất để quy đổi thời hạn thành số thập phân trong Excel là nhân giá trị thời hạn gốc với số giờ, phút, giây trong ngày :

Để chuyển đổi thời gian sang giờ, nhân số thời gian với 24 (số giờ trong một ngày).

Để quy đổi thời hạn sang phút, nhân với thời hạn với 1440 ( số phút trong một ngày = 24 * 60 ) .
Để quy đổi thời hạn sang giây, nhân với thời hạn với 86400 ( số giây trong một ngày = 24 * 60 * 60 ) .

 

Cách chuyển đổi thời gian thành giờ trong Excel:

Phần này sẽ hướng dẫn 3 công thức khác nhau để quy đổi giờ từ định dạng thời hạn tiêu chuẩn ( hh : mm : ss ) thành 1 số ít thập phân .

Công thức 1: Phép toán

Bạn đã biết cách nhanh nhất để quy đổi giá trị thời hạn thành số giờ trong Excel – bằng cách nhân với 24, đơn cử là số giờ trong một ngày :

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

Để lấy Complete hours, nhập công thức hàm INT để bỏ phần lẻ: =INT(A2*24)

 

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

Một cách khác để thực hiện chuyển đổi thời gian > giờ là sử dụng công thức CONVERT:

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

Công thức 3: Hàm HOUR, MINUTE, SECOND

Cuối cùng, bạn có thể sử dụng công thức phức tạp hơn. Xuất các đơn vị thời gian bằng các hàm thời gian  HOUR, MINUTE, SECOND sau đó chia số phút cho 60 và chia số giây cho 3600 và tính toán kết quả:

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

Cách chuyển đổi thời gian thành phút trong Excel

Ba giải pháp tựa như hoàn toàn có thể sử dụng để quy đổi phút từ định dạng thời hạn tiêu chuẩn thành số thập phân .

Công thức 1: Phép toán

Để quy đổi thời hạn thành phút, bạn nhân thời hạn với 1440 – số phút trong một ngày ( 24 giờ * 60 phút = 1440 phút ) :

=A2*1440 (A2 là giá trị thời gian)

Để lấy Complete minutes, nhập công thức hàm INT như ví dụ trước: =INT(A2*1440). Kết quả như trong hình dưới đây:

 

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

Để chuyển đổi giờ > phút bằng hàm CONVERT(number, from_unit, to_unit), cung cấp ‘day’ là đơn vị được chuyển đổi và ‘mn’ là đơn vị chuyển đổi thành:

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

Công thức 3: Hàm HOUR, MINUTE, SECOND

Một cách khác để lấy số phút là nhân số giờ với 60, chia số giây cho 60 :

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

Cách chuyển đổi thời gian thành giây trong Excel

Công thức 1: Phép toán

Nhân thời hạn với 86400 – số giây trong một ngày ( 24 giờ * 60 phút * 60 giây = 86400 )

=A2*86400

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

Công thức tựa như ví dụ bên trên, chỉ khác ở việc thay đơn vị chức năng ‘ day ’ thành ‘ sec ’ :

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

Công thức 3: Hàm HOUR, MINUTE, SECOND

Sử dụng hàm HOUR, MINUTE, SECOND như hai ví dụ trước:

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

Chú ý.

1. Nếu có bất kỳ công thức nào cho kết quả là giá trị định dạng time, chỉ cần thay đổi định dạng thành General để hiển thị kết quả hiển thị số.
2. Để chuyển đổi thời gian thành số thập phân, định dạng General cho ô tính. Như vậy, 23:59:59 sẽ được chuyển đổi thành 0.99999, 06:00 AM thành 0.25, và 12:00 PM thành 0.5. Nếu phần nguyên của số chuyển đổi lớn hơn 0, điều đó có nghĩa là ô tính của bạn đang chứa giá trị của ngày tháng và thời gian.

Cách tách ngày tháng và giờ trong Excel

Như thường thấy, trang tính Excel hoàn toàn có thể chứa ngày tháng và thời hạn trong cùng một ô tính, trong khi bạn muốn tách chúng ra thành hai ô riêng không liên quan gì đến nhau .

Hãy nhớ rằng trong hệ thống Excel, giá trị ngày tháng được hiển thị ở phần nguyên và giá trị thời gian được hiển thị thành phần lẻ thập phân. Bạn có thể xuất giá trị ngày tháng bằng hàm INT – hàm này sẽ làm tròn ô tính theo giá trị nguyên gần nhất.

Giả sử ngày tháng và thời hạn gốc trong cột A, công thức sau sẽ triển khai xong việc tách :

=INT(A2)

 

Để xuất phần thời hạn, trừ hiệu quả mới nhận được cho giá trị ngày tháng và thời hạn gốc :

=A2-B2

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

Nếu bạn không muốn giá trị thời gian liên kết với phần ngày tháng (ví dụ, bạn có thể muốn xóa cột ngày tháng sau này), bạn có thể sử dụng hàm MOD, công thức như sau:

=MOD(A2,1)

 

Chú ý. Nếu phần ngày tháng và thời gian không hiển thị theo ý bạn, thay đổi định dạng của cột mới lần lượt thành  Date và Time.

Cách viết thời gian bằng chữ trong Excel

Đôi khi, bạn cần quy đổi thời hạn thành định dạng “ # ngày, # giờ, # phút và # giây ”. Như vậy, bạn đã biết các thành phần của công thức :

• Sử dụng hàm INT để xuất ngày
• Sử dụng hàm HOUR, MINUTE và SECOND để xuất các đơn vị tương ứng.
• Kết nối tất cả thành phần trong công thức

Giả sử bạn có thời điểm tổ chức sự kiện trong cột B bắt đầu từ ô B4, và ngày tháng thời gian hiện tại trong ô B1 là kết quả của hàm =NOW().

Công thức để tính chênh lệch thời gian rất đơn giản= B4- $B$1. Tất nhiên, không có gì ngăn cản bạn trừ thời điểm hiện tại trực tiếp với = B4-NOW().

Bây giờ, hãy tạo một bộ đếm hiển thị số ngày, giờ, phút và giây còn lại đến khi mỗi sự kiện xảy ra .

Công thức trong ô D4 như sau :

=INT(C4) & ” days, ” & HOUR(C4) & ” hours, ” & MINUTE(C4) & ” minutes and ” & SECOND(C4) & ” seconds”

Nếu bạn bỏ giá trị 0 như trong ô D6, D7 ở trên, hãy thêm hàm IF vào như sau:

=IF(INT(C4)>0, INT(C4)&” days, “,””) & IF(HOUR(C4)>0, HOUR(C4) & ” hours, “,””) & IF(MINUTE(C4)>0, MINUTE(C4) & ” minutes and “,””) & IF(SECOND(C4)>0, SECOND(C4) & ” seconds”,””)

Tất cả giá trị 0 đã được vô hiệu .

Chú ý. Khi một trong các công thức trên ra số âm, lỗi #NUM! sẽ xuất hiện. Điều này xảy ra khi bạn lấy giá trị nhỏ hơn trừ cho thời gian lớn hơn.

Chuyển đổi định dạng text thành time trong Excel

Nếu công thức thời gian và các phép tính không áp dụng được, lỗi định dạng text cho giá trị thời gian có thể là nguyên nhân. Cách nhanh nhất để chữa lỗi là sử dụng hàm TIMEVALUE.

=TIMEVALUE(time_text)

Time_text là chuỗi text mà Excel có thể nhận biết được trong bất kỳ định dạng thời gian nào, ví dụ:

=TIMEVALUE(“6:20 PM”)

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

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

Như bạn thấy, công thức chứa ô tham chiếu chuỗi text tương ứng cung ứng tác dụng giống hệt nhau. Ngoài ra, hãy quan tâm đến chuỗi thời hạn ( giá trị text ) được căn lề trái trong ô A2 và A6, được căn lề phải sau khi quy đổi thành định dạng time trong cột D .

Chuyển đổi định dạng time thành text trong Excel

Giả sử, tệp Excel của bạn được định dạng time như “ 8 : 30 : 00 AM ” và bạn muốn chuyển chúng thành định dạng text. Việc đổi khác định dạng ô sang text không hề vận dụng được do tại điều này sẽ đổi khác giá trị thời hạn thành dạng số. Ví dụ : 8 : 30 : 00 AM sẽ được quy đổi thành số thập phân 0.354166666666667

Sử dụng hàm TEXT để chuyển đổi giá trị số thành text, ví dụ:

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

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

Chuyển đổi định dạng number thành time trong Excel

Nếu bạn có danh sách các số như 1, 2, 3.5 và bạn muốn chuyển đổi chúng thành định dạng giờ, ví dụ 1:00:00, 2:00:00 or 3:30 AM, thực hiện theo các bước sau:

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

Ngoài ra để nâng cao hơn kỹ năng và kiến thức về Excel, các bạn hoàn toàn có thể tham gia khóa học EX101 – Excel từ cơ bản tới nâng cao dành cho người đi làm của mạng lưới hệ thống Học Excel Online. Trong khóa học này các bạn sẽ được tìm hiểu và khám phá những kiến thức và kỹ năng khá đầy đủ, có mạng lưới hệ thống về các hàm, các công cụ của Excel, việc ứng dụng các hàm, công cụ đó vào trong việc làm. Hiện nay mạng lưới hệ thống có rất nhiều tặng thêm cho bạn khi ĐK khóa học này. Thông tin chi tiết cụ thể xem tại :