We’ve developed some resources to help you work effectively from home during COVID-19 Click here to learn more

SuperOffice Date Format

In this article

    Date format in a SuperOffice database

    From SuperOffice CRM 7 most datefields in the SuperOffice database was changed to DateTime.

    There are however some fields in the Userdefined tables (udXXsmall and udXXlarge) which still use the old style a 4-byte value containing seconds elapsed since 1st of January 1970 00:00 (midnight). This will restrict a date to within 1st January 1970 to 1st January 2038 (not precise).

    This date value is easily produced using a C function called mktime.

    Any field containing only a date (not time) is set to midnight that day.

    1st September 1995 midnight = 809913600

    One day is 60 * 60 * 24 = 86400

    6th September 1995 = 809913600 + (86400 * 5) =  810345600

     

    Example Visual Basic: 

    MsgBox DateDiff("s", #1/1/1970#, Now)

     

    Visual Basic for Applications - Excel

    Sub Conv_to_SO_date()
        Dim myDate
        Dim NewDate, OldDate
        Dim col, i
        Dim response

        response = MsgBox("Is this the first cell in the column that contains data?", vbYesNo)

        If Response = vbNo ThenExitSub

        While ActiveCell.Text <> ""
            OldDate = ActiveCell.Value
            NewDate = DateDiff("s", #1/1/1970#, OldDate) ' To SO date
            Application.ActiveCell.Value = NewDate
            ActiveCell.NumberFormat = "general"' To SO date
            ActiveCell.Offset(1, 0).Activate
        Wend

     EndSub



    Sub Conv_from_SO_date()
        Dim myDate
        Dim NewDate, OldDate
        Dim col, i
        Dim response

        response = MsgBox("Is this the first cell in the column that contains data?", vbYesNo)

        If Response = vbNo ThenExitSub

        While ActiveCell.Text <> ""
            OldDate = ActiveCell.Value
            NewDate = DateAdd("s", CLng(OldDate), #1/1/1970#) ' From So date
            Application.ActiveCell.Value = NewDate
            ActiveCell.NumberFormat = "dd.mm.yyyy"' From So date
            ActiveCell.Offset(1, 0).Activate
        Wend

     EndSub