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) Application.ActiveCell.Value = NewDate ActiveCell.NumberFormat = "general" 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#) Application.ActiveCell.Value = NewDate ActiveCell.NumberFormat = "dd.mm.yyyy" ActiveCell.Offset(1, 0).Activate Wend
EndSub
|