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 - Lets assume, you have two date controls on the screen. Write below mentioned formula in another control say number or text.
DATEDIF( datepicker , datepicker1 , "D")

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")
Did this answer your question?