Date Functions

Introduction

The return value of the Date&Time field is a timestamp, and the unit of the timestamp is milliseconds. In this article, the Date&Time field can be directly set as the argument of the Date functions. For example, YEAR(Date&Time).

The following is an introduction to the two basic concepts of date functions:

Timestamp: Timestamp is a format in which computers record time. A timestamp is a sequence of characters that identifies a single moment in time. In Jodoo forms, the value of the Date&Time field is saved in the timestamp sequence.

Date object: The date object can be taken as year, month, day, hour, minute, and second. If the format of the date object is Sat Aug 01 2015 00:00:00, this means 00:00:00 on August 1, 2015. The date object can perform various operations, such as date additions(+), date subtractions(-), and so on.

DATE

DATE(timestamp)

Converts a timestamp to a date object.

DATE(year,month,day)

DATE(year,month,day,hour,minute,second)

Converts the year, month, and day (hour, minute, and second) to a date object.

For example, the date object for January 1, 2023 could be represented as follows:

DATE(Date&Time)

DATE(2023,1,1)

DATE(2023,1,1,0,0,0)

These three all mean "Sun Jan 01 2023 00:00:00 GMT+0800".

DAY

DAY(timestamp)

Returns the day of a date. The day is given as an integer ranging from 1 to 31.

For example, to calculate the number of days corresponding to the sign-in date, you can set the Formula as DAY(Sign-in Date).

DAYS

DAYS(end_timestamp, start_timestamp)

Returns the number of days between two dates.

There are two arguments in the DAYS function:

For example, in the leave application form, enter Start Date and End Date, set the Formula as DAYS(End Date,Start Date)+1, and you can calculate the number of days of leave.

DAYS360

DAYS360(end_timestamp, start_timestamp, method)

The DAYS360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. In some accounting calculations, use the DAYS360 function to help compute payments if your accounting system is based on twelve 30-day months.

There are three arguments in the DAYS360 function:

For example, in accounting calculations, the accounting system generally calculates based on a 360-day year. Therefore, the DAYS360 function can help calculate payment amounts.

DATEDIF

DATEDIF(start_timestamp, end_timestamp, “unit”)

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

There are three arguments in the DATEDIF function:

Note:

1. Be case-sensitive. Uppercase M stands for month and lowercase m stands for minute.

2. If the start date is greater than the end date, the DATEDIF function cannot finish the calculation.

For example, you can calculate how many days, months, or years a project or an event lasts.

Note:

For detailed usage of the DATEDIF function, see Using DATEDIF to Calculate the Difference Between Two Dates.

DATEDELTA

DATEDELTA(timestamp, deltadays)

A specified number of days will be added to or subtracted from the specified date.

There are two arguments in the DATEDELTA function:

For example, in the case of production management, you can enter the production date and shelf life, then you can calculate the expiration date of the product with the DATEDELTA function.

Note:

The DATEDELTA function must be used in the Date&Time field.

HOUR

HOUR(timestamp)

Returns the hour of a time value.

For example, to calculate the number of hours corresponding to the sign-in date, you can set the Formula as HOUR(Sign-in Date).

ISOWEEKNUM

ISOWEEKNUM(timestamp)

Returns the number of the ISO week number of the year for a given date.

The ISO week number is calculated according to the rule of ISO 8601. For detailed calculations:

For example, the ISOWEEKNUM function can be used to calculate the finical year of the government and business to maintain time series.

MINUTE

MINUTE(timestamp)

Returns the minutes of a time value.

For example, to calculate the number of minutes corresponding to a sign-in date, you can set the Formula as MINUTE (Sign-in Date).

MONTH

MONTH(timestamp)

Returns the month from a specified date. The month is given as an integer, ranging from 1 to 12.

For example, to calculate the month corresponding to a sign-in date, you can set the Formula as MONTH(Sign-in Date).

NETWORKDAYS

NETWORKDAYS(start_timestamp, end_timestamp, [holidays])

Returns the number of whole working days between start_timestamp and end_timestamp. Working days exclude weekends and any dates identified in holidays. Use the NETWORKDAYS function to calculate employee benefits that accrue based on the number of days worked during a specific term.

There are three arguments in the NETWORKDAYS function:

Note:

[holidays] can be input in two ways:

1. Using the DATE function or as a result of other formulas. For example, use DATE(2012,5,23) for May 23, 2012. Problems can occur if dates are entered as text.

2. Directly calling the Date&Time field in a subform. However, main form fields cannot be called directly.

For example, when you are calculating an employee's salary for a week and you want to set June 22nd and June 23rd as holidays, you can set the Formula as follows:

NETWORKDAYS(Start Date,End Date,[DATE(2023,6,22),DATE(2023,6,23)]).

NOW

NOW()

Returns the current date and time. Same as the TODAY function.

For example, you can set the Sign-out Date field to the NOW function. Then members can fill in the sign-out form without entering the time manually.

Note:

When using the NOW function in a Date&Time field, you can set the time format in Field Properties.

SYSTIME

SYSTIME()

Returns the current server time.

Even if the client time is manually adjusted, the SYSTIME() function will not be affected and the obtained time is still the server time. In the scenarios of sign-in record and so on, use the SYSTIME() function to prevent users from cheating by adjusting the time.

For example, the SYSTIME function can be used to record sign-in or sign-out date, thus avoiding cheating.

Note:

When using the NOW function in a Date&Time field, you can set the time format in Field Properties.

SECOND

SECOND(timestamp)

Returns the seconds of a date value.

For example, to calculate the number of seconds corresponding to a sign-in date, you can set the Formula as SECOND(Sign-in Date).

TIMESTAMP

TIMESTAMP(date)

Converts a date object to a timestamp.

For example, when customizing time in a Date&Time field, the TIMESTAMP function can be used to convert to a date object to a timestamp format.

TIME

TIME(hour, minute, second)

Returns the decimal number for a particular time. Time values are a portion of a date value and are represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

There are three arguments in the TIME function:

For example, you can use the TIME function to calculate the standby time of a product.

TODAY

TODAY()

Returns the current server date, accurate to the second.

Examples:

1. The TODAY function can be directly used in the Date&Time field. Go to Field Properties > Type to set the precision of the displayed date.

When the field type of the Date&Time field is set as yyyy-MM-dd hh:mm:ss, the returned value of the TODAY function is like 2023-06-30 18:45:05.

2. Use the TODAY function with the TEXT function to convert the obtained date to text format. For example, if you want to get the year, month, and day values, use TEXT(TODAY(),“yyyyMMddhhmmss”).

Note:

For conversion between date and text formats, see Converting Between Text and Date Formats.

WEEKDAY

WEEKDAY(Date&Time)

Returns the day of the week as a number for a given date, with the return value ranging from 0 to 6, where 0 represents Sunday, 1 represents Monday, and so on.

For example, you can calculate the day of the week based on the Sign-in Date field.

WEEKNUM

WEEKNUM(timestamp, return_type)

Returns the week number of a specific date in a year.

Sometimes you may want to know the week number of a specific future date in a year, but it is inefficient to search on the calendar. Use the WEEKNUM function instead.

There are two arguments in the WEEKNUM function:

Different return_type represents different days on which the week begins, the WEEKNUM function also returns different results of week numbers with different return_type. If the return_type is out of the range specified in the table above, the default day on which the week begins is set as Sunday for calculations.

For example, for January 2, 2023, if the week begins on Sunday, WEEKNUM(Date&Time,1) returns 1. If the week begins on Monday, WEEKNUM(Date&Time,2) returns 2.

WORKDAY

WORKDAY(start_timestamp, days, [holidays])

Returns a number that represents a date that is the indicated number of working days before or after a date (the start date). Working days exclude weekends and any dates identified as holidays.

There are three arguments in the WORKDAY function:

Use the WORKDAY function to exclude weekends or holidays when calculating invoice due dates and expected delivery times.

Note:

[holidays] can be input in two ways:

1. Using the DATE function or as a result of other formulas. For example, use DATE(2012,5,23) for May 23, 2012. Problems can occur if dates are entered as text.

2. Directly calling the Date&Time field in a subform. However, main form fields cannot be called directly.

For example, when you are calculating the delivery time and want to set April 5th, 2023 as a holiday, you can set the Formula to WORKDAY(Order Date,Processing Time,[DATE(2023,4,5)]) to deduct weekends and holidays.

YEAR

YEAR(timestamp)

Returns the year corresponding to a date.

For example, to calculate the year corresponding to a production date, you can set the Formula to YEAR(Production Date).