How to calculate time difference of text like YYYYMMDDHHMMSSXXX format including millisecond-CodePudding

Want to calculate time difference in milliseconds for below format YYYYMMDDHHMMSSXXX. XXX refers milliseconds.

20230101232324548 - 20230101232324500 =48

Im trying expr a-b in linux but its not giving proper results for few records. Any way we can calculate difference either in linux or excel. Any possible way would be helpful.

CodePudding user response：

This is not elegant but in Excel you could work in days like this:

=DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2)) MID(A2,9,2)/24 MID(A2,11,2)/1440 MID(A2,13,2)/86400 RIGHT(A2,3)/86400000

or in milliseconds like this:

=DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))*86400000 MID(A2,9,2)*3600000 MID(A2,11,2)*60000 MID(A2,13,2)*1000 RIGHT(A2,3)

then subtract the results.

Working in milliseconds appears the more successful option.

(even with the second method you are starting to approach the limit on Excel's precision, so if you wanted to apply this a long way into the future you might want to subtract the two dates (assuming they are fairly close to each other) before carrying out the rest of the calculation).

CodePudding user response：

If stored as text and the result returned will be text, you could use:

=LET(a,  A1:A3,
     b,  B1:B3,

     dif,LEFT(a,LEN(a)-3)
         -LEFT(b,LEN(b)-3),
     ms, RIGHT(a,3)
         -RIGHT(b,3),

IF(dif=0, TEXT(ms,"@"), dif&ms))

It divides the string into portions excel can handle and subtract these. DateTime excluding milliseconds at first and milliseconds afterwards. If the DateTime difference equals 0 it's omitted.

CodePudding user response：

> bash --version | head -1
GNU bash, version 5.2.15(1)-release (x86_64-redhat-linux-gnu)
> expr 20230101232324548 - 20230101232324500
48