Converting Siemens DATE_AND_TIME to WinCC DateTime via VBA

Siemens S7 PLCs expose date and time as the legacy DATE_AND_TIME (DT) data type, an 8-byte BCD structure anchored to 01.01.1990. WinCC Comfort/Advanced and WinCC Professional internal HMI tags, however, only support the DateTime data type, which is a 64-bit floating-point value anchored to 30.12.1899 (the OLE Automation epoch). When you need to mirror a PLC DT value into an HMI internal tag without binding the tag to a PLC connection, the two epochs collide and the raw read looks decades off. The fix is a single arithmetic step in a WinCC VBA script: add 32874 days. This reference documents the byte layout, the epoch math, two production-ready VBA routines, and the verification steps to confirm the conversion is correct.

1. Problem Statement: DT vs. DateTime Mismatch in WinCC

When a WinCC tag is bound to a PLC connection pointing at an S7 DATE_AND_TIME tag, the WinCC driver transparently converts the 8-byte BCD value to an internal 64-bit DateTime and the screen shows the correct timestamp. As soon as the engineer attempts to use a WinCC internal tag (one without a PLC connection) of data type DateTime populated by a VBA script, the displayed value drifts by approximately 90 years. The cause is that the VBA path does not inherit the driver's automatic conversion and the engineer is now responsible for moving between two epochs manually.

Symptoms reported in the field include:

• Time stamp displays 01.01.1990 instead of the current date.
• Time stamp shows year 2079 or 2080 (off by exactly 89 years, 9 months from the BCD base).
• Time stamp shows year 1900 (off by exactly 90 years in the negative direction).
• Date portion is correct but the time of day is shifted by hours.
• Script crashes with type-mismatch error 13 because the raw DT bytes were assigned to a String.

2. Siemens DATE_AND_TIME (DT) Format Specification

Per the Siemens TIA Portal reference for the DT data type, DATE_AND_TIME occupies 8 bytes and stores the value in BCD. The BCD layout is fixed and the field engine cannot reorder or reformat the bytes.

The base year is 1990. Years 1990…1999 are encoded as 90…99 in the second byte; years 2000…2089 are encoded as 00…89. The day-of-week encoding follows ISO 8601 loosely: 1 = Sunday, 2 = Monday, …, 7 = Saturday. Reserve bit 7 of byte 8 (the high bit of the weekday nibble) is always 0 on S7-1500; some legacy S7-300 firmware writes a status flag there that the converter must mask off.

3. WinCC DateTime Internals and the OLE Epoch

WinCC internal tags of type DateTime store values as 64-bit IEEE-754 floating-point numbers representing days since 30.12.1899 00:00:00. The fractional part encodes the time of day: 0.5 = 12:00:00 noon, 0.25 = 06:00:00, and so on. This is the same epoch used by VBA's Date and DateSerial functions, by COM VT_DATE, and by the Win32 SYSTEMTIME conversions performed inside the WinCC runtime. The epoch was chosen by Microsoft to maintain backward compatibility with the 1900 date system in Lotus 1-2-3.

The Siemens DT base (01.01.1990) and the WinCC DateTime base (30.12.1899) are separated by exactly 32 874 calendar days. That integer is the single most important constant in this conversion; every working VBA routine eventually uses it as a literal or as a named constant.

4. Why +32874: The Day-Offset Calculation

Counting days from 30.12.1899 (WinCC epoch) to 01.01.1990 (Siemens DT epoch):

• 30.12.1899 → 30.12.1989 is exactly 32 873 full days.
• Add one more day to reach 31.12.1989.
• One more day lands on 01.01.1990.
• Total: 32 874 + 1 = 32 875 days if you count both endpoints inclusively, but the standard serial-date convention counts the difference, giving 32 874 days.

Any time the driver (or your script) represents a Siemens DT as a day count since 01.01.1990, you must add 32 874 to land on the WinCC epoch. If the driver or your parser already returns a day count since 30.12.1899, the offset is zero. Always identify which epoch your intermediate value uses before applying the constant.

5. Prerequisites

• WinCC Comfort/Advanced V16 or later, or WinCC Professional V16 or later (TIA Portal). Earlier versions (V13/V14/V15) use the same math but require the legacy HMIRuntime object model in some edge cases.
• Project with the VBA option enabled in the TIA Portal project properties (Runtime Settings → Scripts → Enable VBA).
• Internal HMI tag dt_Internal of data type DateTime.
• PLC tag "DB_HMI".dt_PLC of data type Date_And_Time exposed via a WinCC connection. If the PLC uses a CPU-local tag (e.g. %DB5.DBD0 as raw bytes), the conversion must start from the byte array, not from a numeric.
• Trigger event: a scheduled task, a value-change event on a discrete tag, or a button click that runs the script.

6. Conversion Method A: Direct Numeric Offset via VBA

Use this method when the WinCC driver already exposes the PLC DATE_AND_TIME as a numeric Long / Double representing days since 01.01.1990 (this is the default for tags connected through the S7-1500 driver in TIA Portal V17+ when the HMI tag is typed as Long in an intermediate calculation step). The script reads the numeric, adds 32 874, and writes the result to the internal DateTime tag.

1. Open the WinCC project in TIA Portal.
2. Add an internal tag dt_Internal of type DateTime.
3. Open the project tree → VBA macros → create a new module modDateConvert.
4. Paste the following routine and wire it to the trigger event of your choice (typically the same trigger that currently updates the screen):

' modDateConvert - Convert PLC DT (days since 1990-01-01) to WinCC DateTime
' Constants
Public Const SIEMENS_DT_EPOCH_OFFSET As Long = 32874   ' days from 30.12.1899 to 01.01.1990

Public Sub ConvertPlcDT_ToInternalDateTime()
    Dim vRaw As Variant
    Dim dDays As Double
    Dim dWinCC As Double
    Dim oTag As HMITag
    
    ' 1. Read the PLC DT value (numeric: days since 01.01.1990)
    vRaw = SmartTags("DB_HMI_dummy")
    If IsEmpty(vRaw) Or IsNull(vRaw) Then Exit Sub
    
    ' 2. Coerce to Double; the driver may hand back Integer, Long or Double
    dDays = CDbl(vRaw)
    
    ' 3. Add the offset to land on the OLE 30.12.1899 epoch
    dWinCC = dDays + SIEMENS_DT_EPOCH_OFFSET
    
    ' 4. Write to the internal DateTime tag
    SmartTags("dt_Internal") = dWinCC
End Sub

To run the routine when the PLC updates the DT, schedule it on the same trigger. In WinCC Comfort/Advanced, right-click the internal tag in the HMI tags editor and select Properties → Events → Value change → and bind it to the ConvertPlcDT_ToInternalDateTime macro. For WinCC Professional, attach the same macro to the PLC tag's OnValueChanged event in the Graphics Designer.

7. Conversion Method B: Full BCD Parse to Date

Use this method when the PLC tag is a raw byte array (e.g. an S7-300 DATE_AND_TIME of type DT read through an Area Pointer or a non-integrated connection) or when you want maximum portability across firmware versions. The script walks the 8-byte BCD structure, decodes each nibble, and constructs a WinCC DateTime with DateSerial / TimeSerial.

' modDateConvert - Decode 8-byte BCD DATE_AND_TIME into WinCC DateTime
Public Sub DecodePlcDTBytes_ToInternalDateTime()
    Dim vRaw As Variant
    Dim b() As Byte
    Dim iYear As Integer, iMonth As Integer, iDay As Integer
    Dim iHour As Integer, iMin As Integer, iSec As Integer
    Dim iMs As Integer, iDow As Integer
    Dim dtResult As Date
    
    ' 1. Read the raw byte array exposed by the driver
    vRaw = SmartTags("DB_HMI_dummy")
    If Not IsArray(vRaw) Then
        SmartTags("dt_Internal") = 0          ' driver returned a scalar; abort safely
        Exit Sub
    End If
    b = vRaw
    
    ' 2. Decode year (BCD bytes 0 and 1, big-endian)
    '    b(0) = hundreds+tens nibble, b(1) = thousands+units nibble
    iYear = 2000 + (BCD(b(0)) * 100) + (BCD(b(1)) Mod 100)
    If iYear > 2089 Then iYear = iYear - 100   ' wrap for the rare >2089 case
    
    ' 3. Decode month and day (BCD bytes 2 and 3)
    iMonth = BCD(b(2))
    iDay = BCD(b(3))
    
    ' 4. Decode time of day (BCD bytes 4, 5, 6)
    iHour = BCD(b(4))
    iMin  = BCD(b(5))
    iSec  = BCD(b(6))
    
    ' 5. Decode milliseconds and day-of-week (BCD bytes 7 and 8)
    '    b(7) high nibble = ms*100, low nibble = ms*10
    '    b(8) high nibble = ms*1, low nibble = weekday (1..7)
    iMs = (BCD_HiNibble(b(7)) * 100) + (BCD_LoNibble(b(7)) * 10) + BCD_HiNibble(b(8))
    iDow = BCD_LoNibble(b(8)) And &H07       ' mask off any reserved high bit
    
    ' 6. Build a VBA Date and write to internal tag (no offset needed;
    '    DateSerial already returns a 30.12.1899-anchored serial number).
    dtResult = DateSerial(iYear, iMonth, iDay) + _
               TimeSerial(iHour, iMin, iSec) + _
               (iMs / 86400000#)
    
    SmartTags("dt_Internal") = dtResult
    SmartTags("iWeekday")   = iDow            ' optional internal tag, Integer
End Sub

Private Function BCD(ByVal b As Byte) As Integer
    BCD = (((b And &H F0) \ & H10) * 10) + (b And &H0F)
End Function

Private Function BCD_HiNibble(ByVal b As Byte) As Integer
    BCD_HiNibble = (b And &HF0) \ &H10
End Function

Private Function BCD_LoNibble(ByVal b As Byte) As Integer
    BCD_LoNibble = b And &H0F
End Function

Method B has the advantage of being self-documenting: every byte of the BCD structure is visible in the code, so a maintainer can verify the conversion against the Siemens TIA Portal DATE_AND_TIME reference without external context.

8. Alternative: Skip VBA Entirely by Using DTL

On S7-1500 (firmware V2.0 or later) and TIA Portal V14 SP1 or later, the DTL data type is supported. DTL stores date and time in a structure with individual INT and DINT fields (year, month, day, hour, minute, second, nanosecond, weekday) and is mapped natively to WinCC DateTime through the integrated S7-1500 connection. The complete conversion collapses to a single tag-pointing exercise; no VBA is required. Reserve this approach for greenfield projects or for PLC programs you can modify without affecting other consumers of the legacy DT field.

Conversion in the PLC from DT to DTL is done with the standard library function DT_TO_DTL (defined in the Siemens IEC Standard Functions extended library). Once the variable is DTL, bind it to a WinCC tag of type DateTime; the driver handles the rest.

9. Verification Procedure

After deploying either method, verify the conversion is correct using the following steps. Each step has a binary pass/fail criterion so commissioning engineers can sign off unambiguously.

1. Static check (offline): set the PLC clock to a known value, e.g. 15.03.2024 09:30:00. Trigger the VBA routine manually from the WinCC runtime by clicking a button bound to ConvertPlcDT_ToInternalDateTime. The internal tag dt_Internal must display 15.03.2024 09:30:00 in the WinCC tag simulator.
2. Epoch boundary check: force the PLC to write 01.01.1990 00:00:00.482 to the DT field. The internal tag must display 01.01.1990 00:00:00 with the milliseconds in the source reflected in the lower three digits (482).
3. Leap-year check: force the PLC to 29.02.2024 23:59:59. The internal tag must display 29.02.2024 23:59:59 (non-leap-year 29.02 must be rejected by the sanity filter).
4. Round-trip check: subtract the offset (32 874) from the internal tag value with the formula Round((dt_Internal - 32874) * 86400); the result in seconds since 01.01.1990 must equal the PLC DT converted to seconds since 01.01.1990. Mismatches > 1 second indicate a BCD parse error.
5. Daylight-saving check: if the PLC uses local time without DST adjustment, confirm that the displayed time matches the PLC's local-time view, not the UTC view, to avoid confusion during DST transitions.
6. Performance check: trigger the routine 1 000 times in a tight loop and confirm the runtime of the conversion is < 50 ms on the target panel (Comfort Panels typically < 5 ms; WinCC Runtime Professional on a PC typically < 2 ms).

10. Troubleshooting Matrix

11. Field-Proven Caveats

• Locale side effect: the DateSerial/CDate path is sensitive to the HMI panel's regional settings. On a panel configured for German (de-DE), CDate("03/04/2024") interprets the string as 3 April 2024; on English (en-US) the same string is 4 March 2024. Method B avoids this risk by using numeric DateSerial(y, m, d) exclusively.
• Variant caching: VBA caches the value of SmartTags(...) within a single Sub only if the underlying tag changes during execution. The cache is invalidated at the next Sub boundary, so calling SmartTags("dt_Internal") in a loop always re-reads.
• 32-bit overflow: the offset 32 874 plus today's serial (~45 600 for mid-2024) totals ~78 474, which fits comfortably in a 32-bit Long (max 2 147 483 647). If you ever compute seconds-since-epoch instead of days, the result is ~6.78 billion and overflows a 32-bit Long on TIA Portal VBA; coerce to Double immediately.
• Trigger latency: on Comfort Panels, a value-change event fires within 100–250 ms of the underlying update. For high-resolution timestamps (1 ms granularity required), prefer a cyclic trigger at 100 ms or 250 ms rather than a value-change event.
• Watchdog on the PLC side: the S7-1500 RD_SYS_T and S7-300 READ_CLK instructions write the entire DT atomically; the WinCC driver, however, reads the 8 bytes non-atomically. Under heavy CPU load, a value-change event can fire between byte 3 and byte 4 of the read, producing an inconsistent timestamp. Read the value twice and keep the result only if both reads match.

12. Quick-Reference Constants