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 X

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