sozib

New Member
Joined
Mar 16, 2017
Messages
7
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)

Timestamp
20180429_135116
20180429_135116
20180429_135116
20180429_135117
20180429_135117
20180429_135117
20180429_135117
20180429_135117
20180429_135117
20180429_135117
20180429_135117
20180429_135117
20180429_135118
20180429_135118
20180429_135118
20180429_135118
20180429_135118
20180429_135118
20180429_135118
20180429_135118
20180429_135118
20180429_135119
20180429_135119
20180429_135119
20180429_135119
20180429_135119
20180429_135119
20180429_135119
20180429_135119


ANY HELP WILL BE APPRECIATED..
Thanks in advance



<colgroup><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,058
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Here is a VBA macro that will do that for you

Code:
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
 
Last edited:

sozib

New Member
Joined
Mar 16, 2017
Messages
7
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,058
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
shows ss.000 so i think 001 002 003
 

sozib

New Member
Joined
Mar 16, 2017
Messages
7
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
20180429_135117
20180429_135117
20180429_135117
20180429_135117
20180429_135117
20180429_135117
20180429_135117
20180429_135117
20180429_135117


<tbody>
</tbody>

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

 

Watch MrExcel Video

Forum statistics

Threads
1,122,262
Messages
5,595,154
Members
413,971
Latest member
User786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top