how to get zulu time difference seconds/milliseconds in excel-CodePudding

The excel column contains Zulu Timzezones How to calculate the difference between two dates in seconds.milliseconds

From Time 2022-04-25T04:16:57.823121842Z

To Time

2022-04-25T04:16:58.173194593Z
2022-04-25T04:16:58.089133751Z
2022-04-25T04:16:58.462278784Z
2022-04-25T04:16:57.829376293Z
2022-04-25T04:16:57.961790312Z
2022-04-25T04:16:58.445884586Z
2022-04-25T04:16:57.830806273Z
2022-04-25T04:16:58.067723338Z
2022-04-25T04:16:58.470913276Z
2022-04-25T04:16:57.838787068Z

When I Try to Do something like =B13-B14

Error Function MINUS parameter 1 expects number values. But '2022-04-25T04:35:59.092943356Z' is a text and cannot be coerced to a number.

Converted to Number format

CodePudding user response：

REVISED: I forgot to convert the milliseconds

You can convert the date strings into time values by breaking them into parts:

=DATEVALUE(LEFT(A2,10))   TIMEVALUE( MID(A2,12,8) ) --MID(A2,20,10)/24/60/60

Where A2 is the date string.

This assumes that they have the exact structure that you have shown and fully padded with zeros. If that is not the case, for example the milliseconds could be .095Z, then you can mod this to:

=DATEVALUE(LEFT(A2,10))   TIMEVALUE( MID(A2,12,8) ) --MID(SUBSTITUTE(A2,"Z",""),20,999)/24/60/60

to be safe.