Date aFunctions

DATEOFFSET

DATEOFFSET([String inputDate, Integer monthOffset, Integer dayOffset, Integer yearOffset, String outputFormat="yyyyMMdd", String inputDateFormat="yyyyMMdd"])

This function calculates a date based on the given input date and offset.

Arguments:

• inputDate (java.lang.String) – (Optional) The input date. The default date is current date.
• monthOffset (java.lang.Integer) – (Optional) Number of months to offset the input date. Can be positive or negative.
• dayOffset (java.lang.Integer) – (Optional) Number of days to offset the input date. Can be positive or negative.
• yearOffset (java.lang.Integer) – (Optional) Number of years to offset the input date. Can be positive or negative.
• outputFormat (java.lang.String) – (Optional) The output date format. The default format is yyyyMMdd.
• inputDateFormat (java.lang.String) – (Optional) The input date format. The default format is yyyyMMdd.

Return-type :

java.lang.String

Returns:

Date after applying offset and format.

For details on all supported format/patterns refer to the following link.

Example:

=DATEOFFSET(NOW(), -3) - Current date minus 3 months.
=DATEOFFSET(NOW(), 3) - Current date plus 3 months.
=DATEOFFSET(NOW(), -1, 2, 3) - Current date minus 1 month, plus 2 days and 3 years.
=DATEOFFSET("15/03/2007", 0, 0, -1, "yyyyMM", "dd/MM/yyyy") -
   Input date 15/03/2007 minus 1 year, result is "200603".

EXCEL_DATE_TO_STRING

EXCEL_DATE_TO_STRING(Double excel_numeric_date[, String format_string, String constantString="1904"])

Converts an excel number date representation to human readable String format.

Arguments:

• excel_numeric_date (java.lang.Double) – The excel numeric date value.
• format_string (java.lang.String) – (Optional) The output date format. The default format is “yyyyMMdd”.
• constantString “1904” (java.lang.String) – (Optional) A constant String “1904” if present then start year is considered as 1904 else by default considered as 1900.

Return-type :

java.lang.String

Returns:

String representation of Date.

For details on all supported format/patterns refer to the following link.

Example:

=EXCEL_DATE_TO_STRING(39980) - Output: 20090616
=EXCEL_DATE_TO_STRING(39980, "yyyy/MM/dd") - Output: 2009/06/16

NOW

NOW([String outputDateFormat="yyyyMMdd"])

Returns the current date in string format.

Arguments:

• outputFormat (java.lang.String) – (Optional) The output date format. The default format is yyyyMMdd.

Return-type :

java.lang.String

Returns:

The current date in string format.

For details on all supported format/patterns refer to the following link.

Example:

=NOW("dd-MM-yyyy")