[Office-excel] Subtract two times (2) – Subtract time with milliseconds

1. Use internal functions

1.1 Effect display

=TEXT(((RIGHT(TEXT(B2,"yyyy-mm-dd hh:mm:ss.000"),LEN(TEXT(B2,"yyyy-mm-dd hh:mm:ss.000" ))-FIND(".",TEXT(B2,"yyyy-mm-dd hh:mm:ss.000")))-RIGHT(TEXT(A2,"yyyy-mm-dd hh:mm:ss.000 "),LEN(TEXT(A2,"yyyy-mm-dd hh:mm:ss.000"))-FIND(".",TEXT(A2,"yyyy-mm-dd hh:mm:ss.000" )))) + (LEFT(TEXT(B2,"yyyy-mm-dd hh:mm:ss.000"),FIND(".",TEXT(B2,"yyyy-mm-dd hh:mm:ss. 000"))-1)-LEFT(TEXT(A2,"yyyy-mm-dd hh:mm:ss.000"),FIND(".",TEXT(A2,"yyyy-mm-dd hh:mm: ss.000"))-1))*86400000)/86400000,"[h]:m:S.000")
in:

B2 End time
A2 start time
Cell format: yyyy-mm-dd hh:mm:ss.000

1.2 Step breakdown

Steps	Start time	End time
	`[B2]`2023-09-10 10 :22:24.333	`[C2]`2023-09-11 10:22:23.222
1. Convert time to string	`[B3]`2023-09-10 10:22:24.333	`[C3]`2023-09-11 10:22:23.222
2. Get characters String length	`[B4]`23	`[C4]`23
3. Get the position number in milliseconds	`[B5]`20	`[C5]`20
4. Interception date + time	`[B6]`2023-09-10 10:22:24	`[C6]` 2023-09-11 10:22:23
5. Intercept milliseconds	`[B7 ]`333	`[C7]`222
6. Get the date and time difference (days)	`[B8]`0.999988426
7. Get millisecond difference	`[B9]`-111
8. Date and time difference (days) converted to date and time difference (milliseconds)	`[ B10]`86399000
9. Merge date and time difference (milliseconds) and millisecond difference	`[B11]`86398889
10. Date and time difference (milliseconds) converted to date and time difference (days)	`[B12]`0.999987141
11. Date and time difference (days) formatting	`[B13]`23:59:58.889

1. Convert the time format to a string — =TEXT(B2,"yyyy-mm-dd hh:mm:ss.000")|=TEXT(C2,"yyyy-mm-dd hh:mm:ss. 000")
2. Get String length — =LEN(B3)|=LEN(C3)
3. Get Millisecond separator (·) position number — =FIND(".",B3)|=FIND(".",C3)
4. InterceptionDate time — =LEFT(B3,B5-1)|=LEFT(C3,C5-1)
5. InterceptionMilliseconds — =RIGHT(B3,B4-B5)|=RIGHT(C3,C4-C5)
6. CalculationDate time difference (days) — =C6-B6
7. CalculationMillisecond difference — =C7-B7
8. Date and time difference (days) converted to Date and time difference (milliseconds) — =B8*86400000
9. AccumulationDate time difference (milliseconds) and millisecond difference — =B10 + B9
10. Date and time difference (milliseconds) converted to Date and time difference (days) — =B11/86400000
11. Date and time difference (days)Format– =TEXT(B12,"[h]:m:S.000")

2. Use custom functions

2.1 WPS uses js for function programming

2.1.1 Save file as macro-enabled workbook (*.xlsm)

2.1.2 Custom function DiffDate

Use the shortcut keys Alt + F11 to pop up the WPS Macro Editor and add a custom function

Code

/**
 *DiffDate Macro
 * @param {Date} start
 * @param {Date} end
 */
function DiffDate(start,end)
{<!-- -->
\t
start1= start.Text;
end1= end.Text;

var milliSecondsStart = 0;
if(start1.includes("."))
{<!-- -->
var startLst1 =start1.split(".");
var startdate1 = startLst1[0];
var startms1 = startLst1[1];
var dateObj1 = new Date(startdate1);
var t1 = parseInt(startms1);
milliSecondsStart = dateObj1.getTime() + t1;
\t\t
}
else
{<!-- -->
var dateObj1 = new Date(start1);
milliSecondsStart = dateObj1.getTime();
}
\t
var milliSecondsEnd = 0;
if(end1.includes("."))
{<!-- -->
var endLst1 = end1.split(".");
var enddate1 = endLst1[0];
var endms1 = endLst1[1];
var dateObj2 = new Date(enddate1);
var t2 = parseInt(endms1);
milliSecondsEnd = dateObj2.getTime() + t2;
\t\t
}
else
{<!-- -->
var dateObj2 = new Date(end1);
milliSecondsEnd = dateObj2.getTime();
}

var milliSeconds = milliSecondsEnd - milliSecondsStart;
\t
var pre = "";
if(milliSeconds<0)
{<!-- -->
milliSeconds = -milliSeconds;
pre="-"
}
\t
var hours = Math.floor(milliSeconds / 3600000);
var minutes = Math.floor((milliSeconds % 3600000) / 60000);
var seconds = Math.floor(((milliSeconds % 3600000) % 60000) / 1000);
var milliseconds = milliSeconds % 1000;
var timeStr =pre + hours.toString() + ":" + ("00" + minutes.toString()).slice(-2) + ":" + ("00" + seconds.toString()).slice (-2) + "." + ("000" + milliseconds.toString()).slice(-3);

return timeStr;
}

2.1.3 Using the DiffDate function in the workbook

=DiffDate(A2,B2)

2.2 Office uses VBA for function programming

Because Office is not installed, readers need to perform similar programming according to wps logic here

Extended reading

[Office-excel] Subtract two times