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

 

[Peter_SSs]

Welcome to the MrExcel board!

1. Are those timestamp entries TEXT or are NUMERICAL values formatted as Date/Time?

2. Unless you want the random seconds to change on each timestamp value every time the worksheet re-calculates, you will have to use vba, not a formula. Is that acceptable?

 

[nomanalik]

its in Custom format...

 

[nomanalik]

ALright what will be the vba function to do this ?

 

[Peter_SSs]

Edit: I hadn't seen post 4. I'll be back in a while.

 

[Peter_SSs]

See if this does what you want. If not, more details/examples please.
You'll need to adjust the code to work on the appropriate column and starting row in your worksheet. Currently the code is processing column D starting at row 2.

Test in a copy of your workbook.

Sub Add_Seconds()
  Dim a As Variant
  Dim i As Long
  
  Randomize
  With Range("D2", Range("D" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      If Not IsEmpty(a(i, 1)) Then a(i, 1) = DateAdd("s", Rnd() * 60, a(i, 1))
    Next i
    .NumberFormat = "d/mm/yyyy h:mm:ss AM/PM"
    .Value = a
  End With
End Sub

 

[nomanalik]

Thanks but there are two issues in this code
1) it is returning duplicates...
2) Seconds are not in ascending order...

 

[Peter_SSs]

Thanks but there are two issues in this code
1) it is returning duplicates...
2) Seconds are not in ascending order...

You said that you wanted to add "random seconds". If the numbers are random,
- there is nothing to stop the same random number occurring more than once
- it is extremely unlikely they would be in ascending order.



Perhaps you should start again and try to explain more clearly and give a representative set of sample data (say 10-15 rows) and also give an example of what the results might look like for that sample data, and why.

My signature block below has a 'Look here' link to some good ways to provide your sample data directly in your post.

 

[nomanalik]

Ok i am unable to attach screenshot...I have multiple rows with same hh:mm:ss in excel...
e.g:
1) 10:43:00
2) 10:43:00
3) 10:43:00
4) 10:43:00
5) 10:43:00

6) 10:45:00
7) 10:45:00
8) 10:45:00
9) 10:45:00

10) 10:49:00
11) 10:49:00
12) 10:49:00
13) 10:49:00

What i want is to replace the 00 seconds in above entries with random number(seconds) between (0 to 59) with no duplicates (in a single minute) and in ascending order

i.e.)

1) 10:43:05
2) 10:43:12
3) 10:43:18
4) 10:43:24
5) 10:43:30

6) 10:45:27
7) 10:45:30
8) 10:45:56
9) 10:45:59

10) 10:49:03
11) 10:49:05
12) 10:49:30
13) 10:49:56

No as you can see that there are no duplicate of seconds in a single minute and it is in ascending order...

This is what is want
I want this to differentiate each row for a data mining task...

Hope you understand now...

 

[Peter_SSs]

I want this to differentiate each row for a data mining task...

In that case, do we really need anything random? Couldn't we just add 1 second each time in each group?

i.e.

1) 10:43:01
2) 10:43:02
3) 10:43:03
4) 10:43:04
5) 10:43:05

6) 10:45:01
7) 10:45:02
8) 10:45:03
9) 10:45:04

10) 10:49:01
11) 10:49:02
12) 10:49:03
13) 10:49:04