Công thức Excel: Chuyển đổi thời gian thành múi giờ -

Mục lục

Công thức chung

=MOD(time+(hours/24),1)

Tóm lược

Để chuyển đổi thời gian từ múi giờ này sang múi giờ khác, bạn có thể sử dụng công thức chuyển đổi giờ đã nhập dưới dạng số nguyên thành giá trị thập phân mà Excel nhận dạng là thời gian. Trong ví dụ được hiển thị, công thức trong F5 là:

=MOD(C5+(E5/24),1)

Công thức này trả về một số mà Excel nhận ra là 2:00 sáng.

Giải trình

Thời gian trong Excel là các giá trị phân số của số 1. Vì vậy, 12 giờ đêm là 12/24 = 0,5, 6 giờ sáng là 6/24 = 0,25, v.v. Vì vậy, để chuyển đổi thời gian cho một số nhất định, bạn cần chia số giờ cho 24 để nhận được giá trị thập phân cần thiết:

E5/24 // convert adjustment to Excel time

Chúng tôi thêm kết quả vào thời gian bắt đầu:

C5+(E5/24)

Để đảm bảo chúng ta có giá trị thời gian thực, chúng ta cần đảm bảo rằng chúng ta chỉ có một giá trị thập phân. Nói cách khác, nếu chúng ta thêm 12 giờ (0,5) đến 6 giờ chiều (0,75), chúng ta sẽ nhận được 1,25, nhưng chúng ta thực sự chỉ muốn 0,25.

Để đảm bảo chúng ta chỉ nhận được giá trị thập phân, chúng ta sử dụng hàm MOD với số chia là 1, như một cách thông minh để giữ cho công thức đơn giản.

MOD trả về phần còn lại sau khi chia, vì vậy trả về giá trị thập phân trong trường hợp kết quả lớn hơn 1 (tức là lớn hơn 24 giờ).

Thậm chí tốt hơn, nếu chúng ta kết thúc bằng một giá trị phân số âm, MOD trả về nghịch đảo. Vì vậy, nếu chúng ta kết thúc bằng -25, MOD trả về 0,75 (tương đương với 6 giờ chiều).

Điều này rất quan trọng vì Excel sẽ không hiển thị các giá trị thời gian âm.

Ngày giờ

Một số giá trị ngày bao gồm cả ngày và giờ, và đôi khi được gọi là "datetimes". Các giá trị này bao gồm cả số sê-ri để biểu thị ngày, cộng với giá trị phân số để biểu thị thời gian. Bảng dưới đây cho thấy một số ví dụ:

Ngày giờ	Giá trị thô
3/6/18 6:00 sáng	43165,25
1 tháng 1 năm 1999 21:00	36161.875
4/1/2020 0:00	43922
Ngày 3 tháng 6 năm 1980 12:00 CH	29375,5

Khi làm việc với các ngày bao gồm cả ngày và giờ (datetimes), bạn không cần sử dụng MOD, vì không cần phải làm bất cứ điều gì thông minh khi thời gian qua nửa đêm. Thao tác này trở thành phép cộng đơn giản vì ngày được bao gồm và bạn có thể sử dụng công thức như sau: