timestamp sorting

[sozib]

hi,

I am new to this group. I need to sort out my timestamp which is in human-readable format.

The issue which needs to be sorted is following

1. firstly, I need to make this time format in DD:MM:YYYY hh:mm:ss
2.as you can see it has similar timestamp few of them, based on the total number of timestamp its need to make as unique for each of them, like DD:MM:YYYY hh:mm:ss.000
3. I need to convert this timestamp in Unix timestamp format(Here Linux timestamp is like BST(GMT +01)

[TABLE="width: 149"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Timestamp[/TD]
[/TR]
[TR]
[TD]20180429_135116[/TD]
[/TR]
[TR]
[TD]20180429_135116[/TD]
[/TR]
[TR]
[TD]20180429_135116[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135118[/TD]
[/TR]
[TR]
[TD]20180429_135118[/TD]
[/TR]
[TR]
[TD]20180429_135118[/TD]
[/TR]
[TR]
[TD]20180429_135118[/TD]
[/TR]
[TR]
[TD]20180429_135118[/TD]
[/TR]
[TR]
[TD]20180429_135118[/TD]
[/TR]
[TR]
[TD]20180429_135118[/TD]
[/TR]
[TR]
[TD]20180429_135118[/TD]
[/TR]
[TR]
[TD]20180429_135118[/TD]
[/TR]
[TR]
[TD]20180429_135119[/TD]
[/TR]
[TR]
[TD]20180429_135119[/TD]
[/TR]
[TR]
[TD]20180429_135119[/TD]
[/TR]
[TR]
[TD]20180429_135119[/TD]
[/TR]
[TR]
[TD]20180429_135119[/TD]
[/TR]
[TR]
[TD]20180429_135119[/TD]
[/TR]
[TR]
[TD]20180429_135119[/TD]
[/TR]
[TR]
[TD]20180429_135119


ANY HELP WILL BE APPRECIATED..
Thanks in advance


[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

do you want to convert or keep them and have a usable time format also. I think you want a VBA solution, as you are after an increment also, you can make visual formats with this
=MID(A1,7,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)

 

[alansidman]

Here is a VBA macro that will do that for you

Sub formatDate()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        Range("B" & i) = DateSerial(Left(Range("A" & i), 4), Mid(Range("A" & i), 5, 2), _
                                    Mid(Range("A" & i), 7, 2))  & TimeSerial(Mid(Range("A" & i), 10, 2), Mid(Range("A" & i), 12, 2), _
                                                                                  Mid(Range("A" & i), 14, 2))
    Next i
End Sub

 

[sozib]

Hi,
Thanks this works to sort out the solution about the hh:mm:ss. But if I want to increment, is it possible to do without the VBA codethanks

 

[alansidman]

Using the example in your first post, show us what you mean by increment. For the first 6 entries in your example, what are the expected results. I am unsure what you are asking for.

 

[mole999]

shows ss.000 so i think 001 002 003

 

[sozib]

taking this as example, as you can see from here that all of them has same timestamp, so now it has 9 of them same timestamp, so they gonna convert 1000/9 for each of them
[TABLE="class: cms_table, width: 149"]
<tbody>[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117[/TD]
[/TR]
[TR]
[TD]20180429_135117

[/TD]
[/TR]
</tbody>[/TABLE]

the expected result will be 2018/04/29 13:51:17.111
2018/04/29 13:51:17.222
2018/04/29 13:51:17.333
2018/04/29 13:51:17.444
2018/04/29 13:51:17.555