Question:
Why does Excel interpret as absolute a value written in date formate by a DDE tag?
Solution:
If the Excel cell is formatted as Date/Time, several “###” characters will appear due to the difference in the time base and the “zero date” (initial date) in each system. In SCADA, this date is 01/01/1970; in Excel, it is 00/01/1900.
To fix this, just insert a new cell in Excel with the following expression:
=(A1/86400) + 25569
where
A1 is the cell receiving the DDE tag
86400 (24x60x60) is the result of turning the seconds base (SCADA) into a days base (Excel)
25569 (365*70+18+1) is the difference in days between 1970 and 1900, adding 1 day for each leap year, and Excel’s “00/1/1900” day.
