Applies To:
  • CitectSCADA x.x

Summary:
To convert between Citect time and Access/Excel time use the following calculations. 

Solution:
For 32 bit enviroment (Citect 4.x or later)
CitectTime = 86400 * (ExcelTime - (25569 + (TimeZone / 24)))

where TimeZone is Local Time Serial minus GMT (System) Time Serial in hours.

or...

ExcelTime = 25569 + ((CitectTime + GMTBias()) / 86400.0);
(See the GMTBias() Cicode function below).

CitectSCADA v6.00 has a new Cicode function to do this conversion for you. Use the following command:

ExcelTime = TimeToOleDate(CitectTime, 1);
 

For 16 bit enviroment (3.x or earlier) without TZ Environment Variable Set
CitectTime = 86400 * (ExcelTime - 25568.66667)

If the TZ Enviroment Variable is in a 16 bit system, the Time Zone should be taken into account.

The following is some sample VBA for Excel Code:-

Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)

Type SYSTEMTIME
   wYear As Integer
   wMonth As Integer
   wDayOfWeek As Integer
   wDay As Integer
   wHour As Integer
   wMinute As Integer
   wSecond As Integer
   wMilliseconds As Integer
End Type

Dim trSystem As SYSTEMTIME
Dim trLocal As SYSTEMTIME

Function SetCitectTime(Environment32Bit As Boolean)
   Dim ExcelTime As Double
   Dim CitectTime As Double
   Dim lTimeZone As Long
   Dim tzSystem As Double
   Dim tzLocal As Double

   ExcelTime = Now()

   If Environment32Bit Then

      Call GetSystemTime(trSystem)
      With trSystem
         tzSystem = DateSerial(.wYear, .wMonth, .wDay) + TimeSerial(.wHour, .wMinute, .wSecond)
      End With

      Call GetLocalTime(trLocal)
      With trLocal
         tzLocal = DateSerial(.wYear, .wMonth, .wDay) + TimeSerial(.wHour, .wMinute, .wSecond)
      End With

      lTimeZone = Application.Round(24 * (tzLocal - tzSystem), 0)
      CitectTime = 86400 * (ExcelTime - (25569 + (lTimeZone / 24)))
   Else
      CitectTime = 86400 * (ExcelTime - 25568.66667)
   End If
   SetCitectTime = CitectTime
End Function

 

The following is sample Cicode:

// Returns the current time zone's offset from Greenwich Mean Time in seconds.
// Takes daylight savings time into effect
INT
FUNCTION
GMTBias()

     // Constants
     INT DAY_SECS = 86400;
     INT GMT_1_1_1980 = 315532800;     // Citect time/date variable for 1/1/80

     // Variables
     INT iBiasStandardTime;     // Current time zone's bias from GMT when in standard time
     INT iBiasCurrentTime;     // Current time zone's bias from GMT. May or may not be daylight savings time
     INT iLocal_1_1_1980;               // Citect time/date variable for 1/1/80 in local time zone
    
     iLocal_1_1_1980 = BuildDate(1,1,1980);
    
     iBiasStandardTime = GMT_1_1_1980 - iLocal_1_1_1980;
     iBiasCurrentTime = TimeMidNight(TimeCurrent()) MOD DAY_SECS;
    
     IF (iBiasStandardTime >= 0) AND (iBiasCurrentTime <> 0) THEN
          iBiasCurrentTime = DAY_SECS - iBiasCurrentTime;
     END
    
     IF iBiasStandardTime < 0 THEN
          iBiasCurrentTime = iBiasCurrentTime * (-1);
     END
    
     RETURN iBiasCurrentTime;

END


// Build a Citect time/date value from separate day, month, and year values.
// Takes into account local month/date/year order and separator character
// Supporting function for GMTBias()
PRIVATE
INT
FUNCTION
BuildDate(INT iDay, INT iMonth, INT iYear)

     // Constants
     INT          DATE_ORDER = 1;
     STRING     MMDDYY_ORDER = "0";
     STRING     DDMMYY_ORDER = "1";
     STRING     YYMMDD_ORDER = "2";
     INT          DATE_DELIMITER = 2;
    
     // Variables
     STRING sDelimiter;
     STRING sDate;

     sDelimiter = DateInfo(DATE_DELIMITER, 0);
    
     SELECT CASE DateInfo(DATE_ORDER, 0)
          CASE MMDDYY_ORDER
               sDate = iMonth:# + sDelimiter + iDay:# + sDelimiter + iYear:#;
               RETURN StrToDate(sDate);
          CASE DDMMYY_ORDER
               sDate = iDay:# + sDelimiter + iMonth:# + sDelimiter + iYear:#;
               RETURN StrToDate(sDate);
          CASE YYMMDD_ORDER
               sDate = iYear:# + sDelimiter + iMonth:# + sDelimiter + iDay:#;
               RETURN StrToDate(sDate);
          CASE ELSE
               RETURN 0;
     END SELECT
END

 

 

Keywords:
 

Attachments