Display and operation of Excel cross-day time difference

Use the common functions HOUR (), MINUTE () and SECOND () to extract hours and minutes. If it exceeds 24 hours (across days), an error will occur when extracting hours (less than hours).

Excel function TEXT(value, famat) displays the time difference in a specified format.

It can not only display the number of hours across days, but also calculate the time difference four times.

prerequisite

Excel date column, right-click to set the format-the custom format is: yyyy/mm/dd h:mm:ss.

formula

=TEXT(C2-B2，“h”:mm:ss”)

pay attention to

C2-B2: indicates the time difference (days) "end time-start time"

[h]: indicates hours. Parentheses [] can display more than 24 digits ([mm] displays more than 60 digits).

"[h]:mm:ss": means to display in the format of time: minute: second. Which company you want to display can be set flexibly.

formula

=TEXT((C2-B2)*24*3600，" 0.00")/E2

pay attention to

(C2-B2)*24*3600: indicates the time difference (seconds)

"0.00": it means to display in the format of two decimal places (which can be set flexibly). Because the value of the time difference will be used for later calculation, it is displayed in decimal format instead of in hours, minutes and seconds.