Convert date and time to unix timestamps

Alfie101

New Member
Joined
Jan 29, 2016
Messages
19
Hi,
I maybe overlooking clues I can see on the internet but I am still having trouble converting date and time (day-month-year-mins, sample below) into unix timestamps. Would be most grateful for help please.
Alfie101
[TABLE="width: 112"]
<colgroup><col></colgroup><tbody>[TR]
[TD]20/4/2017 30[/TD]
[/TR]
[TR]
[TD]20/4/2017 32[/TD]
[/TR]
[TR]
[TD]20/4/2017 39[/TD]
[/TR]
[TR]
[TD]20/4/2017 40[/TD]
[/TR]
[TR]
[TD]20/4/2017 44[/TD]
[/TR]
[TR]
[TD]20/4/2017 51[/TD]
[/TR]
[TR]
[TD]20/4/2017 59[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Do the minutes increment up after 59 to 60 (60 being 1:00 AM) and so on?

If that's the case I believe this UDF will do it:

Code:
Function UnixTime(r As String) As Long
Dim d, dv
r = Replace(r, " ", "/")
d = Split(r, "/")
dv = DateValue(d(1) & " " & d(0) & " " & d(2)) + TimeSerial(0, d(3), 0)
UnixTime = (dv - DateSerial(1970, 1, 1)) * 86400
End Function


Excel 2010
AB
120/4/2017 301492648200
220/4/2017 321492648320
320/4/2017 391492648740
420/4/2017 401492648800
520/4/2017 441492649040
620/4/2017 511492649460
720/4/2017 591492649940
Sheet1
Cell Formulas
RangeFormula
B1=unixtime(A1)
B2=unixtime(A2)
B3=unixtime(A3)
B4=unixtime(A4)
B5=unixtime(A5)
B6=unixtime(A6)
B7=unixtime(A7)
 
Last edited:
Upvote 0
Do the minutes increment up after 59 to 60 (60 being 1:00 AM) and so on?

If that's the case I believe this UDF will do it:

Code:
Function UnixTime(r As String) As Long
Dim d, dv
r = Replace(r, " ", "/")
d = Split(r, "/")
dv = DateValue(d(1) & " " & d(0) & " " & d(2)) + TimeSerial(0, d(3), 0)
UnixTime = (dv - DateSerial(1970, 1, 1)) * 86400
End Function

Excel 2010
AB
20/4/2017 30
20/4/2017 32
20/4/2017 39
20/4/2017 40
20/4/2017 44
20/4/2017 51
20/4/2017 59

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1492648200[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1492648320[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1492648740[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1492648800[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1492649040[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1492649460[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1492649940[/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=unixtime(A1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=unixtime(A2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=unixtime(A3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=unixtime(A4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=unixtime(A5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]=unixtime(A6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B7[/TH]
[TD="align: left"]=unixtime(A7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Scott,
Thank you lots. I shall try it in the next days and let you know.
Again thanks, regards,
Alfie101
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top