Support > Repository > Function > Date Time Function
ja | en

We introduce 33 functions related to date and time processing.

BETWEENTIME |  BOMONTH |  CLEARHMS |  DATE |  DATEDIF |  DATESTRING |  DATETIMEVALUE |  DATEVALUE |  DAY |  EDATE |  EOMONTH |  FISCALYEAR |  HOUR |  MINUTE |  MONTH |  MOVEDAY |  MOVEHOUR |  MOVEMINUTE |  MOVEMONTH |  MOVESECOND |  MOVEYEAR |  NETWORKDAYS |  NOW |  SECOND |  TIME |  TIMEVALUE |  TODATE |  TODAY |  TOTIME |  WEEKDAY |  WEEKNUM |  WORKDAY |  YEAR

BETWEENTIME (date type, date type)

Take the time difference between two date type items.Return type is time type.In addition, minutes exceeding 24 hours will be truncated.

BETWEENTIME(${date1}, ${date2})

BETWEENTIME (time type, time type)

Take the time difference between two time type items.Return type is time type.In addition, minutes exceeding 24 hours will be truncated.

BETWEENTIME(${time1}, ${time2})

BETWEENTIME (date/time type, date/time type)

Take the time difference between two date/time type items.Return type is time type.In addition, minutes exceeding 24 hours will be truncated.

BETWEENTIME(${datetime1}, ${datetime2})

BETWEENTIME (character type, character type)

Take the time difference between two date type items written as a string.Return type is time type.In addition, minutes exceeding 24 hours will be truncated.

BETWEENTIME(${datestr1}, ${datestr2})

BOMONTH (date)

Returns the first date of the month.The return type is a date type.

BOMONTH(${date1})

BOMONTH (date, number of months)

Returns the first date of the month.Move back and forth by the specified number of months.(You can move it forward by specifying a negative number for the second argument.The return type is a date type.

BOMONTH(${date1}, 1)

CLEARHMS (date)

Clear the hour, minute, and second of the date to zero.

CLEARHMS(${dateitem})
CLEARHMS(TODAY())

DATE (date)

DATE (year, month, day)

Converts the date expressed in year, month, day to serial value.Here, the serial value is the number of days elapsed since January 1, 1900 as the starting point (1).The return value type is integer type.

Example: Finding the serial value of a date type item

DATE(${date1})

An example Behavior
DATE(1900,1,1) 1
DATE(2000,1,1) 36526

Excel is a specification that regards 1900 as a leap year.This function also conforms to that specification.

You can apply this function to find the difference between two dates.Specifically, it is described as follows.
DATE(${date1})-DATE(${date2})

DATEDIF (date 1, date 2, unit)

Check the difference in the number of days between the start date and the end date and display it in the specified unit.The unit is "Y" (number of years), "M" (number of months), "D" (number of days), "YM" (number of months of fraction in year display), "YD" , "MD" (fractional number of days in month display).The return value type is integer type.

DATEDIF(${date1}, ${date2}, "Y")

An example Behavior
DATEDIF(${birth}, TODAY() ,"Y") 37 (Birth date is 1970/12/9, TODAY () is 2008/2/2 2008)
DATEDIF(${birth}, TODAY() ,"YM") 2 (if your birthday is December 9, 1970 and TODAY () is February 25, 2008)

With this function, you can make automatic calculations "years, months and months".Specifically, it becomes the following expression.
DATEDIF ($ {birth}, TODAY (), Y) + "Year" + DATEDIF ($ {birth}, TODAY (), "YM") + "months"
When the unit is "D", "YD", "MD", it may return different value from Excel.Please note that this is not exactly the same as it is known to be defect on Excel side.

DATESTRING (date)

Converts a date type value to a Japanese calendar.The format of Japanese calendar is "(era) NN M MON D DAY."The return type is a string type.

DATESTRING(${date1})

DATETIMEVALUE (a string representing the date and time)

Converts a date string representing a date and time to a date/time type."Yyyy - MM - dd HH: mm: ss" and "yyyy/MM/dd HH: mm: ss" notation can be used.The return type is datetime type.

DATETIMEVALUE(${timestr})

DATEVALUE (character string representing date)

Converts a date string to a date type.Notation such as "yyyy - MM - dd" or "yyyy/MM/dd" can be used.The return type is a date type.

DATEVALUE(${datestr})

DAY (date)

Find the day from the date type value of the argument.(Month ranges from 1 to 31.The return type is an integer type.

DAY(${date1})

EDATE (date, number of months)

Returns the date that is around the specified number of months.The return type is a date type.

EDATE(${date1}, 3)

EOMONTH (date)

Returns the date at the end of the month.It can be used for setting payment due date for billing date item, etc.The return type is a date type.

EOMONTH(${date1})

EOMONTH (date, number of months)

Returns the date at the end of the month.Move back and forth by the specified number of months.(You can move it forward by specifying a negative number for the second argument.The return type is a date type.

EOMONTH(${date1}, 1)

FISCALYEAR (date)

Returns the "year" for the input date (date type).The return value type is integer type.

FISCALYEAR(${date1})

An example Behavior
FISCALYEAR(DATEVALUE("2009-1-1")) 2008
FISCALYEAR(DATEVALUE("2009-3-31")) 2008
FISCALYEAR(DATEVALUE("2009-4-1")) 2009

HOUR (date)

Find the time from the date type value of the argument.

HOUR(${date1})

MINUTE (date)

Calculate minutes from the date type value of the argument.

MINUTE(${date1})

MONTH (date)

Find the month from the date type value of the argument.(Month ranges from 1 to 12.The return type is an integer type.

MONTH(${date1})

MOVEDAY (date, number of days)

Returns the date that is around the specified number of days.(Giving back a negative number will return to the previous.The return type is a date type.

MOVEDAY(${dateitem}, 1)

MOVEHOUR (date time, hour)

Returns the date and time of the specified time (hour).(Giving back a negative number will return to the previous.The return type is datetime type.

MOVEHOUR(${datetimeitem}, 1)

MOVEMINUTE (date time, minutes)

Returns the date and time of the specified time (minutes).(Giving back a negative number will return to the previous.The return type is datetime type.

MOVEMINUTE(${datetimeitem}, 1)

MOVEMONTH (date, number of months)

Returns the date that is around the specified number of months.(Giving back a negative number will return to the previous.The return type is a date type.
this function is EDATE It has the same behavior.

MOVEMONTH(${dateitem}, 1)

MOVESECOND (date time, seconds)

Returns the date and time of the specified time (seconds).(Giving back a negative number will return to the previous.The return type is datetime type.

MOVESECOND(${datetimeitem}, 1)

MOVEYEAR (date, number of years)

Returns the date that is around the specified number of years.(Giving back a negative number will return to the previous.The return type is a date type.

MOVEYEAR(${dateitem}, 1)

NETWORKDAYS (start date, end date)

Returns the number of days in the working days (business days excluding Saturdays, Sundays, and holidays) included in the period of the specified two dates.

We use the holiday model (jholiday) which is shipped standard to Wagby on holidays.

NOW()

Returns the date and time of the day.The return value is datetime type.

When NOW () is applied to multiple items in the same model, it is guaranteed that the return values ​​of all NOW () will be the same.(Depending on the timing of drawing, NOW () of one item will not be 00 seconds, NOW () of another item will not be 01 seconds.)

NOW()

SECOND (date)

Calculate seconds from the date type value of the argument.

SECOND(${date1})

TIME (hour, minute, second)

Returns the time serial value (decimal) corresponding to the specified time.This is a decimal value that considers "1" as 24 hours.The return type is 8 byte floating point type.
For time designation exceeding 23, the surplus gradually decremented by 24 is used.
The designation of more than 59 minutes is converted into hour and minute.
The second specification exceeding 59 is converted to hour, minute, second.

TIME(${hour}, ${minute}, ${second})

TIME (time type)

Converts the value of the time type item to the time serial value (decimal number).The return type is 8 byte floating point type.

TIME(${timevalue})

TIMEVALUE (character string representing time)

Converts a string representing time to a time type.Notation such as "HH: mm" or "HH: mm: ss" can be used.Return type is time type.

TIMEVALUE(${timestr})

TODATE (date time type)

Converts a date/time type to a date type.

TODATE(${datetime})

TODAY()

I will return today's date.The return value is "date type".

If TODAY () is applied to multiple items in the same model, it is guaranteed that the return values ​​of all TODAY () will be the same.(Depending on the timing of drawing, TODAY () of one item will not be January 1, TODAY () of another item will not be January 2.)

TODAY()

TOTIME (date and time type)

Converts a date/time type to a time type.

TOTIME(${datetime})

WEEKDAY (date)

Converts the date to a number corresponding to the day of the week.Sunday is 1, Saturday is 7.The return value type is integer type.

WEEKDAY(${date1})

WEEKDAY (date, type)

Converts the date to a number corresponding to the day of the week.The return value type is integer type.
If you specify 1 as the type, Sunday is 1 and Saturday is 7.
If you specify 2 as the type, Monday is 1 and Sunday is 7.
If you specify 3 as the type, Monday is 0 and Sunday is 6.

If the date of the argument is null, it returns -1.

WEEKDAY(${date1}, 1)

An example Behavior
WEEKDAY(d,1) 4
WEEKDAY(d,2) 3
WEEKDAY(d,3) 2

WEEKNUM (date)

Returns the week number of the year in which the date falls.The beginning of the week is Sunday.The return value type is integer type.

WEEKNUM(${date1})

WORKDAY (start date, number of days)

Returns a date that is around the specified number of days from a date.Saturdays, Sundays and holidays are excluded and calculated on business days only.The return type is a date type.

We use the holiday model (jholiday) which is shipped standard to Wagby on holidays.

YEAR (date)

Find the year from the date type value of the argument.The return value type is integer type.

YEAR(${date1})