Công thức chung
=IF(end>start, end-start, 1-start+end)
Tóm lược
Để tính toán số giờ giữa hai thời điểm, bạn có thể sử dụng công thức trừ thời gian bắt đầu cho thời gian kết thúc. Nếu thời gian bắt đầu và kết thúc kéo dài giữa đêm, bạn sẽ cần điều chỉnh công thức như giải thích bên dưới. Trong ví dụ được hiển thị, công thức trong D5 là:
=IF(C5>B5,C5-B5,1-B5+C5)
Giải trình
Để tính số giờ giữa các thời điểm, khi cả hai thời điểm đều trong cùng một ngày theo lịch, bạn chỉ cần trừ thời gian bắt đầu cho thời gian kết thúc:
=end-start
Và áp dụng định dạng số để hiển thị giờ.
Tuy nhiên, khi thời gian vượt qua ranh giới trong ngày (nửa đêm), mọi thứ có thể trở nên phức tạp. Đọc bên dưới để xem một số cách để quản lý thách thức này.
Cách Excel theo dõi thời gian
Trong Excel, một ngày bằng 1, đại diện cho 24 giờ. Điều này có nghĩa là thời gian và giờ là các giá trị phân số của 1, như được hiển thị trong bảng dưới đây:
| Giờ | Thời gian | Phân số | Giá trị |
|---|---|---|---|
| 3 | 03:00 | 24/3 | 0,125 |
| 6 | 06:00 | 24/6 | 0,25 |
| 4 | 4:00 sáng | 24/4 | 0,167 |
| số 8 | 08:00 | 24/8 | 0,333 |
| 12 | 12 giờ trưa | 24/12 | 0,5 |
| 18 | 6:00 chiều | 18/24 | 0,75 |
| 21 | 9:00 TỐI | 21/24 | 0,875 |
Tính toán thời lượng đơn giản
Khi thời gian bắt đầu và thời gian kết thúc trong cùng một ngày, việc tính thời lượng theo giờ rất đơn giản. Ví dụ: với thời gian bắt đầu là 9:00 sáng và thời gian kết thúc là 5:00 chiều, bạn có thể chỉ cần sử dụng công thức này:
=end-start =5:00PM-8:00AM =0.375-0.708=.333 // 8hours
Khi thời gian qua nửa đêm
Việc tính toán thời gian đã trôi qua sẽ khó khăn hơn nếu thời gian vượt qua ranh giới trong ngày (nửa đêm). Ví dụ: nếu thời gian bắt đầu là 10:00 giờ tối một ngày và thời gian kết thúc là 5:00 sáng ngày hôm sau, thì thời gian kết thúc thực sự ít hơn thời gian bắt đầu và công thức trên sẽ trả về giá trị âm và Excel twill hiển thị một chuỗi ký tự băm (########).
Để khắc phục sự cố này, bạn có thể sử dụng công thức này cho các thời gian vượt qua ranh giới trong ngày:
=1-start+end
Bằng cách trừ thời gian bắt đầu cho 1, bạn sẽ có được lượng thời gian trong ngày đầu tiên, bạn có thể chỉ cần thêm vào lượng thời gian trong ngày thứ 2, bằng với thời gian kết thúc .
Công thức này sẽ không hoạt động cho các thời điểm trong cùng một ngày, vì vậy chúng tôi có thể tổng quát hóa và kết hợp cả hai công thức bên trong một câu lệnh IF như sau:
=IF(end>start, end-start, 1-start+end)
Bây giờ khi cả hai thời điểm trong cùng một ngày, kết thúc lớn hơn thời gian bắt đầu , vì vậy công thức đơn giản được sử dụng. Nhưng khi ranh giới thời gian trong ngày, công thức thứ hai được sử dụng.
Thay thế chức năng MOD
Bằng cách sử dụng hàm MOD với số chia là 1, chúng ta có thể đơn giản hóa công thức ở trên thành:
=MOD(end-start,1)
Ở đây hàm MOD xử lý vấn đề tiêu cực bằng cách sử dụng hàm MOD để "lật" các giá trị âm sang giá trị dương cần thiết. Phiên bản này của công thức sẽ xử lý cả hai trường hợp, vì vậy chúng ta có thể loại bỏ câu lệnh IF có điều kiện.
Lưu ý: không có công thức nào ở trên sẽ xử lý thời lượng lớn hơn 24 giờ. Nếu bạn cần điều này, hãy xem tùy chọn ngày + giờ bên dưới.
Để biết thêm về modulo, đây là một liên kết tốt trên Học viện Khan.
Khoảng thời gian định dạng
Theo mặc định, Excel có thể hiển thị thời gian, thậm chí cả thời gian biểu thị một khoảng thời gian, bằng cách sử dụng AM / PM. Ví dụ: nếu bạn có thời gian được tính là 6 giờ, Excel có thể hiển thị thời gian này là 6 giờ sáng. Để xóa AM / PM, hãy áp dụng định dạng số tùy chỉnh như h: mm.
Trong trường hợp thời gian tính toán vượt quá 24 giờ, bạn có thể muốn sử dụng định dạng tùy chỉnh như (h): mm. Cú pháp dấu ngoặc vuông (h) cho Excel biết hiển thị thời lượng giờ lớn hơn 24 giờ. Nếu bạn không sử dụng dấu ngoặc, Excel sẽ chỉ đơn giản là "cuộn qua" khi thời lượng đạt 24 giờ (giống như đồng hồ).
Đơn giản hóa vấn đề với ngày + giờ
Bạn có thể chỉ đơn giản là vấn đề tính toán thời gian đã trôi qua bằng cách làm việc với các giá trị chứa cả ngày và giờ. Để nhập ngày và giờ cùng nhau, hãy sử dụng một khoảng trắng duy nhất giữa thời gian và ngày: 9/1/2016 10:00 SA
Sau đó, bạn có thể sử dụng công thức cơ bản để tính thời gian đã trôi qua:
=end-start
Trong ví dụ dưới đây, giá trị bắt đầu và giá trị kết thúc chứa cả ngày và giờ:
Công thức là:
=C5-B5
Được định dạng với định dạng số tùy chỉnh (h): mm, để hiển thị giờ đã trôi qua.
