Display and operation of Excel cross-day time difference
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.