Is there a way to get the date from unix server?-CodePudding

I am pulling some data using VBA and the range where I need the date and time is like a huge number and it is not able to format as yyyy/mm/dd, it is like that: 1651680385. I tried formula or change the format in Excel but, it doesn't work.

Below is the code which I am still not able to understand where is the issue. I appreciate any advice.

Function theTimeinYard(unixTime As String) As String
     Dim tiy As Double
     Dim tiyd As Date
     Dim days As Double
     Dim hours As Double
     Dim minutes As Double
     Dim hou As Double
                
     tiy = CDbl(unixTime) ' / 60# / 60# / 24#) '- DATE(1970,1,1)
     tiyd = ModUtilities.fromUnix(tiy)
     
     days = 0
     minutes = 0
     hours = 0
     If ((DateDiff("n", tiyd, Now) / 60) >= 24) Then
        days = DateDiff("d", tiyd, Now) 'how many days
        tiyd = DateAdd("d", days, tiyd) 'recalculate date
     End If
     
     If (days < 4) Then ' 4 or more days just mention days
        'how many minutes
        minutes = DateDiff("n", tiyd, Now)
        
        hou = (minutos / 60) ' calcs
        hours = Int(hou)
        
        minutes = Int((hou - Int(hou)) * 60)
     End If
     
     theTimeinYard = IIf(days > 0#, days & " days", "") & IIf(hours > 0#, " " & hours & " hours", "") & IIf(minutes > 0, " " & minutes & " min", "")
                            
End Function

CodePudding user response：

Please, try the next simple function:

Public Function fromUnix(ts) As Date
    fromUnix = DateAdd("s", CDbl(ts), "1/1/1970")
End Function

It returns a Date in the standard format. Like any Date you may change the format as you want.

I am not its 'father', I found it on the net with some year before...

You can test it using the next sub and your UNIX number (from the question):

Sub testFromUnix()
   Debug.Print fromUnix("1651680385")
   Debug.Print Format(fromUnix("1651680385"), "yyyy/mm/dd hh:nn:ss")
End Sub

CodePudding user response：

FaneDuru's single line is OK for your purpose.

If you prefer extended code, it should add some extra. This function, for example, handles milliseconds as well:

' Day offset from Visual Basic numerical zero date value (1899-12-30 00:00:00.000).
    ' Julian Date.
    Private Const JdOffset          As Double = 2415018.5
' <snip>
    ' Unix Time.
    Private Const UtOffset          As Long = -25569


' Returns the date of a specified Unix Time with a resolution of 1 ms.
' UnixDate can be any value that will return a valid VBA Date value.
'
' Minimum value:  -59011459200
'   ->  100-01-01 00:00:00.000
' Maximum value:  253402300799.999
'   -> 9999-12-31 23:59:59.999
'
' 2016-02-08. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateUnix( _
    ByVal UnixDate As Variant) _
    As Date
    
    Dim Timespan    As Variant
    Dim ResultDate  As Date
    
    Timespan = (CDec(UnixDate) / SecondsPerDay) - CDec(UtOffset)
    ResultDate = DateFromTimespan(Timespan)
    
    DateUnix = ResultDate
    
End Function

As it is taken from the larger library of mine, VBA.Date , it takes advantage of some supporting functions:

' Converts a timespan value to a date value.
' Useful only for result date values prior to 1899-12-30 as
' these have a negative numeric value.
'
' 2015-12-15. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateFromTimespan( _
    ByVal Value As Date) _
    As Date
  
    ConvTimespanToDate Value
  
    DateFromTimespan = Value
  
End Function


' Converts a linear timespan value by reference to a date value.
' Example:
'
'   Date     Time  Timespan      Date
'   19000101 0000  2             2
'
'   18991231 1800  1,75          1,75
'   18991231 1200  1,5           1,5
'   18991231 0600  1,25          1,25
'   18991231 0000  1             1
'
'   18991230 1800  0,75          0,75
'   18991230 1200  0,5           0,5
'   18991230 0600  0,25          0,25
'   18991230 0000  0             0
'
'   18991229 1800 -0,25         -1,75
'   18991229 1200 -0,5          -1,5
'   18991229 0600 -0,75         -1,25
'   18991229 0000 -1            -1
'
'   18991228 1800 -1,25         -2,75
'   18991228 1200 -1,5          -2,5
'   18991228 0600 -1,75         -2,25
'   18991228 0000 -2            -2
'
' 2015-12-15. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub ConvTimespanToDate( _
    ByRef Value As Date)
   
    Dim DatePart    As Double
    Dim TimePart    As Double
  
    If Value < 0 Then
        ' Get date (integer) part of TimeSpan shifted one day
        ' if a time part is present as Int() rounds down.
        DatePart = Int(CDbl(Value))
        ' Retrieve and reverse time (decimal) part.
        TimePart = DatePart - Value
        ' Assemble the date and time parts to return a date value.
        Value = CDate(DatePart   TimePart)
    Else
        ' Positive timespan values are identical to date values by design.
    End If
  
End Sub