Làm sao chuyển đổi số giờ >24h thành tổng số ngày + số giờ

[nguoitinhmenyeu]

Hiện tại mình đang gặp chút rắc rối cần phải giải quyết bằng Excel, mong mọi người giúp đỡ. Cụ thể là: Muốn chuyển đổi số thời gian hh:mm:ss thành dạng ngày + giờ.
Ví dụ: 49h:25m:50S chuyển thành: 2 ngày : 1h: 25m :50s
Chỉ rõ bằng công thức excel nha mọi người.
Cảm ơn mọi người đã quan tâm tới topic.

 

[boyxin]

Hiện tại mình đang gặp chút rắc rối cần phải giải quyết bằng Excel, mong mọi người giúp đỡ. Cụ thể là: Muốn chuyển đổi số thời gian hh:mm:ss thành dạng ngày + giờ.
Ví dụ: 49h:25m:50S chuyển thành: 2 ngày : 1h: 25m :50s
Chỉ rõ bằng công thức excel nha mọi người.
Cảm ơn mọi người đã quan tâm tới topic.

Giả sử tại ô A1 = 49h:25m:50S
Bạn thử dùng công thức này xem sao

= DAY(A1)&" ngày "&HOUR(A1)&":"&MINUTE(A1)&":"&SECOND(A1)

 

[nguoitinhmenyeu]

hix công thức này đúng là vận dụng linh hoạt excel, còn minh lại dùng công thức sau:
1. Chia làm 5 ô: ngày giờ phút giây rồi dùng 2 hàm:
=INT(CONVERT(C6;"hr";"day")) và
=ROUND(CONVERT(MOD((CONVERT(C6;"hr";"day"));1);"day";"hr");0)
để hiển thị lên câu đơn giản như bạn là:
=(IF(AND(B7>0;C7=0;D7=0;E7=0);(B6+B7)&" ngày ";IF(AND(B6>0;B7>=0;C7=0;OR(D7>0;E7>0));(B6+B7)&" ngày : ";IF(AND(B6>0;B7=0;C7=0);B6&" ngày ";IF(AND(B6>0;B7>=0);(B6+B7)&" ngày : ";IF(AND((ISBLANK(B6)=TRUE);B7>0);B7&" ngày : ";" "))))))&IF(AND(C7>0;OR(B7>0;B6>0);OR(D7>0;E7>0));C7&" giờ : ";IF(AND(C7>0;OR(D7>0;E7>0));C7&" giờ : ";IF(AND(C7>0;OR(D7=0;E7=0));C7&" giờ ";" ")))&IF(AND(D7>0;E7=0);D7&" phút";IF(D7>0;D7&" phút : ";" "))&IF(E7>0;E7&" giây";" ")
thật là oải chè đậu mới ra kết quả như mong muốn, ấy vậy mà bạn dùng có cái công thức ngày giờ phút giây có sẳn là giải quyết xong vấn đề của mình. Đúng là kiến thức excel của mình thật là tệ quá đi.
Rất là cảm ơn sự giúp đỡ của bạn boyxin.
Cái hàm if dài dòng ấy mục đích là để cho nó hiển thị đúng ngày giờ, ví dụ:
Chỉ có 2 ngày và 1s thì nó phải hiện thị là: 2 ngày : 01 giây
còn nếu chỉ có 2 ngày thì nó phải hiện thị là: 2 ngày (không có dấu : đằng sau).
http://www.giaiphapexcel.com/forum/member.php?40707-boyxin

 

[huuthang_bd]

Công thức của Boyxin sẽ không cho kết quả đúng nếu số giờ lớn hơn 786 (tức 32 ngày). Tôi đề xuất công thức này:

=INT(A1)&" ngày "&TEXT(A1,"hh:mm:ss")

 

[nguoitinhmenyeu]

Kiểm tra lại ý kiến của bạn huuthang_bd về vấn đề tổng số thời gian có thể chuyển đổi thì theo các công thức của các bạn (boyxin và huuthang_bd) tuy đơn giản như với những số giờ lớn như 90.000h : 9 m : 9s khi chuyển đổi đều không được nhưng áp dụng cách của mình thì vẫn ok (3750 ngày : 9 phút : 9 giây). Xem ra vấn đề cũng phức tạp ra phết. Bạn nào có giải pháp tối ưu hơn không xin chỉ giáo. Với công thức của mình có một số hạn chế về hiển thị kết quả sao cho đúng khi công các dãy số thời gian ở nhiều hàng.

Cách chuyển đổi ngày giờ thì ok rồi, nhưng cách hiển thị khi tính tổng thời gian thì chưa chính xác. Mong mọi người hướng dẫn dùm mình.

Ví dụ:
25 giờ : 17 phút : 34 giây 25 giờ : 17 phút : 34 giây Ngày Giờ Phút Giây 19 376 94 25 17 34 Hàng trên cùng là dạng hiển thị: hh:mm:ss nên không có vấn đề gì.

Nhưng hàng thứ 2 nó phải đổi ra ngày: thì kết quả đúng đảng ra là 1 ngày : 1 giờ : 17 phút : 34 giây mới đúng.


Mình đính kèm tập tin đó theo cho dễ thấy vấn đề.

 

[huuthang_bd]

Kiểm tra lại ý kiến của bạn huuthang_bd về vấn đề tổng số thời gian có thể chuyển đổi thì theo các công thức của các bạn (boyxin và huuthang_bd) tuy đơn giản như với những số giờ lớn như 90.000h : 9 m : 9s khi chuyển đổi đều không được nhưng áp dụng cách của mình thì vẫn ok (3750 ngày : 9 phút : 9 giây). Xem ra vấn đề cũng phức tạp ra phết. Bạn nào có giải pháp tối ưu hơn không xin chỉ giáo. Với công thức của mình có một số hạn chế về hiển thị kết quả sao cho đúng khi công các dãy số thời gian ở nhiều hàng.
Ví dụ:
25 giờ : 17 phút : 34 giây 25 giờ : 17 phút : 34 giây Ngày Giờ Phút Giây 19 376 94 25 17 34 Hàng trên cùng là dạng hiển thị: hh:mm:ss nên không có vấn đề gì.
Nhưng hàng thứ 2 nó phải đổi ra ngày: thì kết quả đúng đảng ra là 1 ngày : 1 giờ : 17 phút : 34 giây mới đúng.

Bạn hãy gửi lên một file Excel trong đó có ví dụ mà công thức của tôi không cho kết quả đúng. Chú ý là dữ liệu đầu vào phải là dữ liệu thời gian. Đừng lấy dữ liệu kiểu Text đưa vào công thức, kết quả sai là điều đương nhiên.

 

[BNTT]

Cách chuyển đổi ngày giờ thì ok rồi, nhưng cách hiển thị khi tính tổng thời gian thì chưa chính xác. Mong mọi người hướng dẫn dùm mình.
Mình đính kèm tập tin đó theo cho dễ thấy vấn đề.

Quả thật lúc ban đầu, khi đọc câu hỏi của bạn, tôi chả biết làm như thế nào cho nó ra, bởi không biết cái dữ liệu đầu vào của bạn nó như thế nào hết.

Ngay cả cái chuyện bạn nói công thức của bạn đúng, của người kia chưa đúng, cũng khó mà chính xác, bởi những công thức mà tôi thấy ở trên đây, chỉ là công thức, mà chả biết định dạng của cái dữ liệu gốc nó ra làm sao. Phải biết cái định dạng (cái cách nhập vào) nó như thế nào thì mới có thể kết luận công thức đúng hay sai.

Tuy nhiên, nếu căn cứ theo cái file bạn gửi lên, thì tôi xin đề xuất phương án này, vừa sửa được lỗi tính tổng giờ mà bạn nói, vừa ngắn gọn hơn, chỉ xài mỗi hàm IF, thêm tí hàm LEFT và LEN để định dạng cái kết quả.

Trong file, tôi làm cho bạn ở Sheet2, để nguyên Sheet1 cho bạn so sánh.

Đầu tiên, tôi lấy số giờ + (số phút / 60) + (số giây / 3600) để tính tổng số giờ, sau đó chia hết cho 24 để Excel nó hiểu đây là một giá trị thời gian, bao gồm đủ ngày, giờ, phút, giây:

Time = (C6+(D6/60)+(E6/3600))/24

(Lưu ý: Khi tạo Name này, bạn phải đang chọn ô B4, đứng ở ô khác, sai ráng chịu)

Tiếp theo tôi dùng IF để định dạng nó. Phải làm chuyện này, vì yêu cầu của bạn "cao" quá: Nếu các chỉ số ngày, giờ, phút, giây mà bằng 0 thì không hiển thị nó, rồi lại thêm cái dấu hai chấm ở giữa các chỉ số nữa... Nhưng không sao, chừng 1 phút thôi:

EditTime = IF(DAY(Time)>0, DAY(Time) & " ngày : ", "") & IF(HOUR(Time)>0, HOUR(Time) & " giờ : ", "") & IF(MINUTE(Time)>0, MINUTE(Time) & " phút : ", "") & IF(SECOND(Time)>0, SECOND(Time) & " giây", "")

Cuối cùng, vì cái chuyện nếu như số giây = 0, ở kết quả nó có cái dấu hai chấm vô duyên quá, nên phải thêm một tí định dạng nữa:

MyTime = IF(SECOND(Time)<=0, LEFT(EditTime,LEN(EditTime)-2), EditTime)

Xong. Ra ngoài ô B4, tôi gõ công thức =MyTime, rồi copy ra những ô tương tự.


------------------------------------------------------------------------------------------------
P/S: Nếu bạn không thích đặt Name thì cũng chả sao hết, nhưng nó... sẽ rối tinh lên, dòm mấy cái công thức dài loằng ngoằng oải lắm.

Thêm chuyện nữa: Lần sau mà bạn có hỏi cái gì, bạn nên gửi luôn file chứa vấn đề bạn cần hỏi ngay từ bài đầu tiên, để mọi người khỏi đoán mò, vừa mất thời gian của người ta, mà bạn lại không nhận được câu trả như mong muốn.

 

[huuthang_bd]

Quả thật lúc ban đầu, khi đọc câu hỏi của bạn, tôi chả biết làm như thế nào cho nó ra, bởi không biết cái dữ liệu đầu vào của bạn nó như thế nào hết.

Ngay cả cái chuyện bạn nói công thức của bạn đúng, của người kia chưa đúng, cũng khó mà chính xác, bởi những công thức mà tôi thấy ở trên đây, chỉ là công thức, mà chả biết định dạng của cái dữ liệu gốc nó ra làm sao. Phải biết cái định dạng (cái cách nhập vào) nó như thế nào thì mới có thể kết luận công thức đúng hay sai.

Tuy nhiên, nếu căn cứ theo cái file bạn gửi lên, thì tôi xin đề xuất phương án này, vừa sửa được lỗi tính tổng giờ mà bạn nói, vừa ngắn gọn hơn, chỉ xài mỗi hàm IF, thêm tí hàm LEFT và LEN để định dạng cái kết quả.

Trong file, tôi làm cho bạn ở Sheet2, để nguyên Sheet1 cho bạn so sánh.

Đầu tiên, tôi lấy số giờ + (số phút / 60) + (số giây / 3600) để tính tổng số giờ, sau đó chia hết cho 24 để Excel nó hiểu đây là một giá trị thời gian, bao gồm đủ ngày, giờ, phút, giây:


(Lưu ý: Khi tạo Name này, bạn phải đang chọn ô B4, đứng ở ô khác, sai ráng chịu)

Tiếp theo tôi dùng IF để định dạng nó. Phải làm chuyện này, vì yêu cầu của bạn "cao" quá: Nếu các chỉ số ngày, giờ, phút, giây mà bằng 0 thì không hiển thị nó, rồi lại thêm cái dấu hai chấm ở giữa các chỉ số nữa... Nhưng không sao, chừng 1 phút thôi:


Cuối cùng, vì cái chuyện nếu như số giây = 0, ở kết quả nó có cái dấu hai chấm vô duyên quá, nên phải thêm một tí định dạng nữa:


Xong. Ra ngoài ô B4, tôi gõ công thức =MyTime, rồi copy ra những ô tương tự.


------------------------------------------------------------------------------------------------
P/S: Nếu bạn không thích đặt Name thì cũng chả sao hết, nhưng nó... sẽ rối tinh lên, dòm mấy cái công thức dài loằng ngoằng oải lắm.

Thêm chuyện nữa: Lần sau mà bạn có hỏi cái gì, bạn nên gửi luôn file chứa vấn đề bạn cần hỏi ngay từ bài đầu tiên, để mọi người khỏi đoán mò, vừa mất thời gian của người ta, mà bạn lại không nhận được câu trả như mong muốn.

Sao bạn không dùng hàm TEXT() thay cho IF(). Theo tôi, sau khi có Time thì làm thế này cho gọn.

=SUBSTITUTE(TEXT(DAY(Time),"#"" ngày : "";;")&TEXT(HOUR(Time),"#"" giờ : "";;")&TEXT(MINUTE(Time),"#"" phút : "";;")&TEXT(SECOND(Time),"#"" giây : "";;")&"_"," : _","")

 

[nguoitinhmenyeu]

Các bạn thử hàm đó với số thời gian là: 90.000 giờ :1 phút: 1 giây xem thử có hiểu quả không nhé. Mình thấy với số lớn hàm trên chạy không còn chính xác nữa.
Có một vấn đề mình muốn các bạn giúp mình đó là:
làm thế nào để tìm 1 giá trị mà nó nằm ở ô cộng gộp trong file excel mình đính kèm có:
Ví dụ một tìm giá trị cấp độ: 3 nếu có giá trị cấp độ 3 thì trả lại kết quả giá trị ngày giờ tương ứng với cấp độ 3 đó.
Mình dùng vloop nhưng nó tham chiếu ngay ở hàng đầu tiên của ô cấp độ 3 tức là ô mình không cần, mình đang cần giá trị ở ô giá trị hàng 4, tức là ô ngày, ô giờ, ô phút, ô giây.
Mong các bạn giúp đỡ.

 

[huuthang_bd]

Các bạn thử hàm đó với số thời gian là: 90.000 giờ :1 phút: 1 giây xem thử có hiểu quả không nhé. Mình thấy với số lớn hàm trên chạy không còn chính xác nữa.
Có một vấn đề mình muốn các bạn giúp mình đó là:
làm thế nào để tìm 1 giá trị mà nó nằm ở ô cộng gộp trong file excel mình đính kèm có:
Ví dụ một tìm giá trị cấp độ: 3 nếu có giá trị cấp độ 3 thì trả lại kết quả giá trị ngày giờ tương ứng với cấp độ 3 đó.
Mình dùng vloop nhưng nó tham chiếu ngay ở hàng đầu tiên của ô cấp độ 3 tức là ô mình không cần, mình đang cần giá trị ở ô giá trị hàng 4, tức là ô ngày, ô giờ, ô phút, ô giây.
Mong các bạn giúp đỡ.

Người ta giúp bạn mà chẳng thấy bạn cảm ơn lấy một tiếng. Cũng chẳng hồi âm gì. Khi nào bí lại quay lại hỏi...

 

[nguoitinhmenyeu]

Không phải là không cảm ơn. Mọi sự giúp đỡ của các bạn mình rất chân thành cảm ơn, dù có trả lời hay không nhưng quan tâm đến vấn đề này. huuthang_bd đừng bùn vì điều đó. Việc hồi âm chậm trể là do mình bận nên thời gian online để xem hồi âm và trả lời chậm. Mấy bạn thông cảm
Nhờ có các bạn mà mình giải quyết vấn đề của mình nhanh chóng hơn.
Cho mình gửi lời chào thân ái đến các bạn.