Date & Time Functions

Supported functions related to Date and Time

DronaHQ Studio supports the following Date and Time-related functions

DATESTR()

This is a custom function that converts date (Unix format) to readable date format, say, 29/Apr/2019.

Syntax - DATESTR(date,format,isLocalTime)

where

  • date is a UNIX date

  • format can be any of the following keywords -

    "dddd" will return Monday
    "ddd" will return Mon
    "dd" will return Mo
    "d" will return 1
    "DD" will return 29
    “MM”
    will return 04
    "M" will return 4
    "MMM" will return Apr
    "MMMM" will return April
    "YY" will return 19
    "YYYY" will return 2019
    “MM/DD/YYYY”
    will return 04/29/2019
    "HH" will return hour in 24hr format - 17
    "hh" will return hour in 12hr format - 5
    "hh:mm:ss" will return Hour:Minutes:Seconds in 12hr format
    "hh:mm:ss a" will return Hour:Minutes:Seconds am/pm in 12hr format. You can also put " A " instead of " a " to show AM/PM instead of am/pm respectively.

  • isLocalTime: is enabled or disabled to specify the output to be formatted to local time. Default output time is UTC.

Example -

Let’s say, we input date from a Date Picker control (unique name - datepicker) and use the DATESTR() in another control, say Text, then the formula will be -

DATESTR(datepicker,"MM/DD/YYYY")

The output will be 04/29/2019 , when date selected in Date Picker is 29th April 2019.

Let’s say, we input time from a DateTime Picker control (unique name - datetimepicker) and use the DATESTR() in another control, say Text, then the formula will be written to get time for the local time zone as:

DATESTR(datetimepicker,“DD-MM-YYYY HH:mm” , 1)

timeloca

The same without the local time zone would be written as

DATESTR(datetimepicker,“DD-MM-YYYY HH:mm” , 0)

timelocal no

Here as the time zone is disabled the output is in the UTC.

DATEDIF()

Calculates the number of days, months, or years between two dates.

Syntax - DATEDIF ( start_date , end_date , unit )

Where, unit is “D” which returns the number of days in the period.

Example -

  • DATEDIF(TODAY(), "09/09/2020", "D").
    Assuming TODAY() is 23- July-2020) the output will be 47 days.

  • Let’s assume, you have two date controls (datepicker and datepicker1) on the screen. If you try the below formula in another control for e.g numeric or text control, the output function is defined as follows.
    DATEDIF( datepicker , datepicker1 , "D") which would then return the difference in days between the dates.

YEAR()

Returns the year corresponding to a date.

Syntax - YEAR(serial_number)
Example -

YEAR(DATESTR(datepicker,"MM/DD/YYYY"))

YEAR(DATE("2019","05","24"))

DAY()

Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.

Syntax - DAY(serial_number)
Example -

DAY(DATESTR(datepicker,"MM/DD/YYYY"))

DAY(DATE("2019","05","24"))

DAYS()

Returns the number of days between two dates.

Syntax - DAYS(end_date, start_date)
Example -

DAYS(DATESTR(datepicker1,"MM/DD/YYYY"),DATESTR(datepicker,"MM/DD/YYYY"))

DAYS(DATE("2019","05","24"),DATE("2019","05","20"))

DAYS("2019/05/24","2019/05/22")

DAYS("05/24/2019","05/22/2019")

DAYS360()

This function returns the number of days between two dates based on a 360-day year (twelve 30-day months).

Syntax - DAYS360(start_date,end_date,[method])
Example

DAYS360(DATE("2019","05","20"),DATE("2019","05","24"),1)

DAYS360(DATESTR(datepicker,"MM/DD/YYYY"),DATESTR(datepicker1,"MM/DD/YYYY"),0)

DAYS360("2019/05/22","2019/05/24",0)

DAYS360("05/22/2019","05/24/2019",0)

DATEVALUE()

This function converts a date that is stored as text to a serial number. It accepts date in a text format and will return a serial number with respect to 1st Jan 1900.

Syntax : DATEVALUE(date_text)
Example :

DATEVALUE(DATESTR(datepicker,"YYYY/MM/DD"))

DATEVALUE(DATESTR(datepicker,"MM/DD/YYYY"))

DATEVALUE("2019/05/24")

DATEVALUE("05/24/2019")