Common Date Formulas

Getting the current time

Returns the current date (year-month-day).

TODAY()

If the system date is November 11, 2011, the formula returns 2011-11-11.

Returns the current time (hour:minute:second)

NOW()

Returns 2012-06-20 14:55:57 if the cell format is general and the system time is 14:55:57, June   20, 2012.

Getting the Year, Month, or Day of a Date

Returns the year of a date.

YEAR("2011-11-11")

Returns 2011.

Returns the month of a date.

MONTH("2011-11-11")

Returns 11.

Returns the day of a date.

DAY("2011-11-11")

Returns 11.

Getting a Specified Day of a Year, Quarter, Month, or Week

Returns the date of yesterday.

NOW()-1

Returns 2021-11-08 11:43:53 if the system time is 11:43:53, November 9, 2021.

Returns the first day of a year.

DATE(YEAR(NOW()),01,01)

Returns 2011-01-01 if the system date is 2011-11-11.

Returns the last day of a month.

DATE(YEAR(TODAY()),MONTH(TODAY()),DAYSOFMONTH(TODAY()))

Returns 2011-11-30 if the system date is 2011-11-11.

Returns the number of days of the current month.

DAYSOFMONTH(NOW())

Returns 30 if the system date is 2021-11-11.

Returns the number of days from the current date to a specified date.

DATEDIF(DATE(YEAR(Date),MONTH(Date),DAY(Date)),TODAY(),"D")

Returns 29 if the specified date is 2021-10-10 and the system date is 2021-11-09.

Returns the week number of the current date of the year.

WEEK(TODAY())

Returns 41 if the system date is 2021-10-10.

Getting a Specified Time Relative to the Current Month

Returns the current   month.

MONTH(TODAY())

Returns 11 if the system date is November 11, 2011.

Returns the same   day last year.

YEARDELTA(TODAY(),-1)

Returns 2010-11-11 if the system date is November 11, 2011.

Returns the month   before last.

DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY()))

Returns 2011-09-11 if the system date is November 11, 2011.

Returns the last   month.

DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

Returns 2011-10-11 if the system date is November 11, 2011.

Returns the date   one year and one month before the current date.

DATE(YEAR(TODAY())-1,MONTH(TODAY())-1,DAY(TODAY()))

Returns 2010-10-11 if the system date is 2011-11-11.

Returns the end of   the current month.

DATEDELTA(DATE(YEAR(MONTHDELTA(Date,1)),MONTH(MONTHDELTA(Date,1)),1),-1)

or DATE(YEAR(Date),MONTH(Date)+1,1-1)

Returns 2011-11-30 if the system date is 2011-11-11.

Returns the maximum time in the current date dimension.

Note:

This formula can only be used on the dashboard.

FORMAT(TODATE(MAX_AGG(DATETONUMBER(Date))),"YYYY-MM-dd   HH:mm:ss")

Data Type Conversion

Converts the character string of the date type to the time type.

TODATE() can convert any date-type data to a time type yyyy-MM-dd.

TODATE("2011/11/11")

If the system date is November 11, 2011, the formula returns 2011-11-11.

Converts time-type data to date type in other formats.

FORMAT()

FORMAT("2/15/2011","dd/MM/yyyy")

Returns 15/02/2011.

Data Type Conversion

Adds and subtracts the date.

DATEDELTA(TODAY(),-1)

Note:

-1 in the formula means subtracting one day. Besides, you can set the number here to positive, adding the corresponding number of days.

Returns the date one day before the current date. For example, if the system date is 2011-11-11, returns 2011-11-10.

Adds and subtracts the month.

MONTHDELTA(TODAY(),-1)

Returns the date one month before the current date. For example, if the system date is November 11, 2011, returns 2011-10-11.

Adds and subtracts the year.

YEARDELTA(TODAY(),-1)

Returns the date one year before the current date. If the system date is November 11, 2011, returns 2010-11-11.