How to add random seconds in existing timestamp

[nomanalik]

Hi Folks
Currently i have a timestamps column in this format.... 2/10/2018 10:43 AM...
Now i want to add random seconds in this existing timestamp column like 2/10/2018 10:43:04 AM, 2/10/2018 10:43:09 AM, 2/10/2018 10:43:18 AM, 2/10/2018 10:43:28 AM and so on like this....

Please suggest me the formula

 

[nomanalik]

No, actually this data is coming from mobile sensors...And mobile sensors don't provide data like this (say with 1 second increment every time).

 

[Peter_SSs]

No, actually this data is coming from mobile sensors...And mobile sensors don't provide data like this (say with 1 second increment every time).

One of us is not understanding the other.
I am not suggesting that the sensors send the data with the 1 second increments. What I am saying is that if you get three rows of data with, say, 10:43:00 and you simply want to differentiate each row, I'm wondering what the difference is between adding random seconds in increasing order ..

10:43:09
10:43:27
10:43:32

.. and adding sequential seconds ..

10:43:01
10:43:02
10:43:03

.. as both allow you to differentiate the rows?


BTW, can you confirm that you will never have more than 60 original timestamps with the same value?

 

[Harsh]

Try this buddy!

=TIME(HOUR(D35),MINUTE(D35),SECOND(D35)+RAND()*9+1)

(Here D35 is the first cell where the time starts.)
Let me know if this worked.
Note: this will add random seconds between 1 to 10 if you want more difference here is the formula for RAND function
=Rand ()*Upper Value- lower Value+Lower value
In this case its =Rand ()*10-1+1

 

[Peter_SSs]

Try this buddy!

=TIME(HOUR(D35),MINUTE(D35),SECOND(D35)+RAND()*9+1)

Welcome to the MrExcel board!

That approach will not address this ..

the random seconds to change on each timestamp value every time the worksheet re-calculates

.. nor either of these

1) it is returning duplicates...
2) Seconds are not in ascending order...