I. Description
When processing cell reports, it is inevitable that there will be a lot of calculations between cells. We can usually use the DATESUBDATE function to calculate the time difference between two dates. However, in some special cases, we need to be accurate to xx hours, xx minutes and xx seconds. We can use the following time type addition and subtraction formula.
The results are as follows：
II. Formulas
1. Simple time calculation
First, create a new workbook. Enter 13:00:00 in cell A2 and 13:26:00 in cell B2
C2 cell input hour difference: HOUR(B2) - HOUR (A2)
D2 cell input minute differenece: MINUTE(B2) - MINUTE(A2)
Because the hour difference may be greater than 0, enter the actual minute difference in cell E2: C2 * 60 + D2;
Enter the final time difference in cell F2: TRUNC (E2 / 60) + "h" + mod (E2, 60) + "m";
The template style is as follows:
2. Special format processing
1) If the time format is not the standard hh:mm:ss format, the following formula can be used:
Enter 13:50 in cell A4 and 14:20 in cell B4
Cell C4: Hour differenceHOUR(FORMAT(TIME(INDEXOFARRAY(SPLIT(B4, ":"), 1), INDEXOFARRAY(SPLIT(B4, ":"), 2), 00), "HH:mm:ss")) - HOUR(FORMAT(TIME(INDEXOFARRAY(SPLIT(A4, ":"), 1), INDEXOFARRAY(SPLIT(A4, ":"), 2), 00), "HH:mm:ss"))
Cell D4: Minute difference:
MINUTE(FORMAT(TIME(INDEXOFARRAY(SPLIT(B4, ":"), 1), INDEXOFARRAY(SPLIT(B4, ":"), 2), 00), "hh:mm:ss")) - MINUTE(FORMAT(TIME(INDEXOFARRAY(SPLIT(A4, ":"), 1), INDEXOFARRAY(SPLIT(A4, ":"), 2), 00), "hh:mm:ss"))
Because the hour difference may be greater than 0, enter the actual minute difference in E4 cell: C4 * 60 + D4;
Enter the final time difference in cell F4: TRUNC (E4 / 60) + "h" + mod (E4, 60) + "m";
The template style is as follows:
For special cases, the first step is to format and then process the time type, the most important formula is the basic time formula HOUR(),MINUTE();
2) In addition, if the time format is yyyy-mm-dd hh:mm:ss, you need to process the days and convert them into hours:
Enter 2017-01-20 13:30:30 in cell A6, and enter 2017-01-22 11:19:20 in cell B6
Cell C6: Hour difference:
TRUNC((DATETONUMBER(B6) - DATETONUMBER(A6)) / 1000 / 60 / 60) + "H"
Cell D6: Minute difference:
TRUNC ((datetonumber (B6) - datetonumber (A6)) / 1000 / 60) - TRUNC ((datetonumber (B6) - datetonumber (A6)) / 1000 / 60 / 60) * 60 + "m"
E6 cell: seconds difference:
if (right (b6,2) - right (a6,2) > 0, right (b6,2) - right (a6,2), right (b6,2) - right (a6,2) + 60) + "s"
Enter the final time difference in F6 cell: C6 + D6 + E6;
The template style is as follows:
At the same time, we can also use some other formulas to obtain the time value, such as:
Hour difference: DATESUBDATE(B6, A6, "h") + "H"
Minute difference: DATESUBDATE(B6, A6, "m") - DATESUBDATE(B6, A6, "h") * 60 + "M"
Second difference: DATESUBDATE(B6, A6, "s") - (DATESUBDATE(B6, A6, "m") - DATESUBDATE(B6, A6, "h") * 60) * 60 - DATESUBDATE(B6, A6, "h") * 60 * 60 + "S"
3. Custom functions
If you feel that the formula nesting is too cumbersome, you can directly customize the function to calculate the time difference, that is, pass two time periods to add and subtract directly.
For the source code of DateDiff function, see:
1) Compiling custom functions
compile DateDiff.java generate DateDiff.class Copy file to report application directory %FR_HOME%\webapps\webroot\WEB-INF\classes\com\fr\function.
2) Register custom functions
After compiling, select server > function manager and add the DateDiff function, as shown in the figure below:
Create a new template and add the formula
DateDiff(TODATE("2017-01-20 13:30:30"),TODATE("2017-01-22 11:19:20"),"h{2}H m{2}M s{2}S")
to the cell. The usage method is as follows:
