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
|