Preface | Workplace Examples
In daily workplace office scenarios, we often encounter time data in the composite format of "date + time point". We need to calculate the two data in this format to obtain the time difference, and finally display the time difference in the "X hours and X minutes" format.
I explained this problem in an article before. Since the previous methods are bound by certain situations, they can only solve specific models and cannot perfectly solve all situations. So today the editor has summarized an upgraded method, which can perfectly solve the problem of time data calculating time difference in the compound format of "date + time point".
Formula | Solution
We can directly enter function formula in C2 cell:
=LEFT(TEXT(TEXT(B2-A2,"[M]")/60,"0.00"),2)&"hours"&ROUND(RIGHT(TEXT(TEXT(B2-A2,"[M]")/60,"0.00"),2)/100*60,0)&"minutes"
0 pull-down to get all the results. As shown in the figure below:
Detailed explanation | Long formula disassembly and understand
We see that the above formula is very long and nests multiple functions, such as LEFT function, RIGHT function, TEXT function, ROUND function, etc. Each function is very basic, but the whole formula is easy to understand. Let's break down the formula and understand it.
① Get the minute time difference
C2 cell input formula:
=TEXT(B2-A2,"[M]")
②Divide the minute time difference by 60 to get the hour time difference
D2 cell input formula:
=C2/60
③Batch convert hour time difference into two-digit decimal format
E2 cell input formula:
=TEXT(D2,"0.00")
④ Extract the "hour" data part of the hour time difference
Enter the formula in cell F2:
=LEFT(E2,2)&"hour"
Use the LEFT function to extract 2 bits from left to right.
⑤Extract the "minute" data part of the conversion hour time difference
Input function in G2 cell:
=ROUND(RIGHT(E2,2)/100*60,0)&"part"
Use the RIGHT function to extract 2 bits from right to left, then divide by 100 and multiply by 60, and finally round the ROUND function to retain the integer to get the minute part.
⑥Merge "hours" and "minutes"
Enter the formula in cell H2:
=F2&G2
