how to get zulu time difference seconds/milliseconds in excel

user_911576 0
Viewed 98

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

excel-formula excel
1 Answers

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.

user_46171210
Related