Common Date Formula

Getting the Current Time

To get the current date (YYYY-MM-DD).

TODAY()

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

To get the current time (YYYY-MM-DD HH:MM:SS).

NOW()

Returns 2012-06-20 14:55:57 if the system date is June 20, 2012.

Getting the Year, Month, or Day of a Date

To get the year of a date.

YEAR("2011-11-11")

Returns 2011.

To get the month of a date.

MONTH("2011-11-11")

Returns 11.

To get the day of a date.

DAY("2011-11-11")

Returns 11.

To get the year, month, and day from the year, month, day, hour, minute, and second of a date.

TODATE(FORMAT(2023-06-13 00:01:27,"yyyy-MM-dd"))

Note:

The time field must be a numeric value or in date format.

Returns 2023-06-13 00:00:00.

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

To get the date of yesterday.

NOW()-1

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

To get the first day of a year.

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

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

To get the first day of a month.

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

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

To calculate the number of days between the current date and a specified date.

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

Returns 39 if the specified date is October 10, 2021 and the system date is November 9, 2021.

To get the week number of the current date.

WEEK(TODAY())

Returns 41 if the system date is October 10, 2021.

Getting a Specified Date Relative to the Current Month

To get the current month.

MONTH(TODAY())

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

To get the same date as two months ago.

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

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

To get the same date as last month.

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

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

To get the same date in the month before this month last year.

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

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

To get the last date of the current month.

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

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

To get the date two months from the current date.

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

Returns 2011-12-11 if the system date is October 11, 2011.

Data Type Conversion

To convert text-type data to date-type data.

TODATE("2011/11/11")

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

To convert text-type data to date-type data.

TODATE("Wed1/15/07","EEEMM/dd/yy")

Converts Wed1/15/07 to date format 2007-01-17 00:00:00.

To convert text-type data to date-type data.

TODATE("2021/02/07Wed","yyyy/MM/ddEEE")

Convert 2021/02/07Wed to date format 2021-02-03 00:00:00.

To convert text-type data to date-type data.

To convert year-month-day text-type data to date-type data.

TODATE("20200522","yyyyMMdd")

Converts 20200522 to date format 2020-05-22 00:00:00.

To convert text-type data to date-type data.

To convert year-month text-type to date-type data.

TODATE("202005","yyyyMM")

Converts 202005 to date format 2020-05-01 00:00:00.

To convert value-type data to date-type data.

TODATE(1621221876057)

Time-type (The original field is a 13-digit timestamp, accurate to milliseconds.)

Converts 1621221876057 to date format 2021-05-17 11:24:36.

TODATE(1621221876*1000)

Time-type (The original field is a 10-digit timestamp, accurate to seconds. 1 second = 1,000 milliseconds.)

Converts 1621221876 to 2021-05-17 11:24:36.

To convert year-month-day text-type data to year-month text-type data.

FORMAT(TODATE("2020-02-02"),"yyyyMM")

Converts 2020-02-02 to 202002.

Note:

If 2020-02-02 is date-type data, the TODATE() function is not needed. You can directly use FORMAT("2020-02-02","yyyyMM").

To convert date-type data to value-type data.

DATETONUMBER("2021-05-17")

(The original field is a 13-digit timestamp, accurate to milliseconds.)

Converts 2021-05-17 to value format 1,621,180,800,000.

(The original field is a 13-digit timestamp, accurate to milliseconds.) To convert value-type data to text-type data.

FORMAT(TODATE(1621221876057),"yyyy-MM-ddEEE")

Converts 162122187605 to 2021-05-17Mon.

To convert time-type data to other date formats.

FORMAT()

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

Returns 15/02/2011.

To process the year, month, and day fields into one date field.

DATE(2020,02,22)

Three fields:

Year: 2020

Month: 02

Day: 22

Returns the date-type data 2020-02-22 00:00:00.