Date and Time Serial Numbers - j5 - 28.0 - Customization & Programming - Hexagon PPM

j5 IndustraForm Designer Help

English (United States)
Search by Category
Customization & Programming
j5 Version

As in other spreadsheet applications, Date and Date & Time values are processed inside the spreadsheet engine as sequential serial numbers. This allows them to be used in calculations with numeric functions. For example, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39447 days after January 1, 1900.

The time portion of a Date & Time value is a fraction of a 24 hour day. So, for example, the serial number for January 1, 2016 at 6PM is 42370.75.

This means that any numeric functions can also work with date & time values. So, for example you can want to have an IndustraForm that has readings for Process Data at specific times during the day, you can use a formula like the one in the image below. The TRUNC(A9) formula removes the time portion of a Date & Time input element in cell A9, and then adding TIME(18,0,0) puts that specific time of day back in.

Example Date & Time Formula

Although Date & Time values are processed as serial numbers in the spreadsheet engine, they are stored in the j5 database using the ISO 8601 standard UTC Date & Time format. This ensures that all of the information about the timestamp is recorded unambiguously.