Time a Date Conversions and Duration Calculations in Omniscope

Modified on Fri, 22 Aug at 4:50 PM

Many datasets contain time or date fields, and analysis often requires calculations or conversions involving this type of data.

The Basics

Omniscope provides simple tools for managing date and time formatting within the Field Organiser block:

a) If a field is recognised as a date but its display format is not suitable, click the three-dot menu next to the field name to adjust it. You can either select a predefined format from the drop-down list or enter a custom format manually, then press Enter to apply it.

b) If a field is imported as text but represents a date, you can convert and format it:

Change the field’s data type from the drop-down menu (the values may briefly disappear while conversion takes place).

Open the three-dot menu and enter the input date pattern exactly as it appears in the dataset.

Then, choose the desired output format.

The input pattern defines how Omniscope interprets the data, while the output format controls how it is displayed. These do not need to match.

Time and Date Calculations

Once fields are correctly formatted as dates or times, you can perform calculations on them. To do this, create a new field and click the Formula button to use one of the available date and duration functions. Functions can also be combined to build more complex formulas.

Date functions in Omniscope:

Function	Description
`DATE(year, month, day, hour, minute, second, millisecond, timezone)`	Creates a date/time value from numeric components and an optional timezone.
`DATEADD(date, number, unit)`	Adds a specified number of units (e.g. days, months, years) to a date.
`DATEDIFF(date1, date2, unit, approximate)`	Returns the difference between two dates in the specified unit; can use approximate mode for months/years.
`DATETOTEXT(date_value, custom_format, time_zone)`	Converts a date to text using a custom format and optional timezone.
`DATEUNIT(date, unit)`	Extracts a specific unit (e.g. year, month, day, hour) from a date.
`DATEVALUE(text)`	Converts a text value in recognised formats into a date.
`FORMATDURATION(number, unit)`	Formats a numeric duration in the specified unit as a human-readable string.
`LASTDAYOFMONTH(date_value)`	Returns the last day of the month for the given date.
`NETWORKDAYS(start_date, end_date)`	Returns the number of working days (Mon–Fri) between two dates.
`NOW()`	Returns the current date and time.
`TEXTTODATE(text_value, custom_format, lenient, time_zone)`	Converts a text value to a date using a custom format and optional timezone; can be lenient with mismatches.
`TODAY()`	Returns the current date (no time component).
`TZCORRECT(date, input_timezone, output_timezone)`	Converts a date between timezones.
`TZDEFAULT()`	Returns the system default timezone.
`TZOFFSET(time_zone, date)`	Returns the offset, in milliseconds, for a given timezone at a specified date.
`UNIX_MILLISECONDS_TO_DATE(unixtime)`	Converts a Unix timestamp in milliseconds into a date.
`UNIX_SECONDS_TO_DATE(unixtime)`	Converts a Unix timestamp in seconds into a date.

Click on the interactive formula guide for the full explanation of what the functions do and where each one can be applied.

Calculating time duration

a) We have start and finish time

Let's say we have an event start and finish time and date and we wish to establish the duration of time that has passed between them (see the screenshot with data shown below).

Time difference between the two date fields measured in seconds:

DATEDIFF([Field1],[Field2],"second")

Now convert resulting [ Duration In Seconds] (numeric field) to HH:mm:ss format:

RIGHT("00" + INTFLOOR([Duration In Seconds] / 3600), 2) + ":" +

RIGHT("00" + INTFLOOR(MOD([Duration In Seconds], 3600) / 60), 2) + ":" +

RIGHT("00" + MOD([Duration In Seconds], 60), 2)

b) Calculations on HH:mm:ss Format Durations

When a duration is stored in HH:mm:ss format, one approach is to use a formula to convert it into hours, minutes, seconds, or milliseconds before performing calculations. This makes it possible to sum values (for total time spent) or compute averages (such as average call duration, job time, etc.).

Here’s an example formula that converts a duration value from HH:mm:ss to minutes:

DATEDIFF(

TEXTTODATE("00:00:00", "HH:mm:ss"),

TEXTTODATE([Duration text], "HH:mm:ss"),

“minute")