Handling Dates, Times, and Calculating Durations in Omniscope

Modified on Tue, 26 Aug at 6:46 PM

Working with dates and times in Omniscope goes beyond simple display. You may need to convert formats, extract components such as day or month, calculate differences between two date-time values, aggregate durations, count working days within a period, or convert text into dates (and vice versa).

The Basics

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

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

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 in the Field Organiser block 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 event start and finish date-time values stored in separate fields, and we want to calculate the duration between them.

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 aggregation and 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")