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)

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.

Example -

Lets 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 -

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

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

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