Help Center

DATE

Formula Explanation

Convert a date string to a date value for time offset calculations.

Formula Syntax

date("date string", "matching time format")

date("date string") + time offset

  • Date string: For example, "2023-01-11 12:11:11".
  • Matching time format: Default is "yyyy-mm-dd hh:mm:ss"; "yyyy-ww" represents the ww-th week.
  • Time offset: In seconds, can be negative.
  • Parameters can also be data columns, i.e., the CODE at the top of each column in the table, e.g., AA1.
date("2022-03-17"); // Date
date("2023-01-11 12:11:11", "yyyy-MM-dd HH:mm:ss"); // Precise to seconds
date("2023-11", "yyyy-MM"); // 1st day of November at 0:00
date("2022-03", "yyyy-ww"); // 1st day of the 3rd week at 0:00

Usage Example

  1. Convert the time in column CG2 to week time and write the result into column CG3.
CG3 = date(cg2,"yyyy-ww")

date_ww

In CG3 column, the data represents the first day of the 3rd week in 2023, which is January 16, 2023, at 00:00.

  1. Add 1 minute to the date and time in column CH2, and write the result into column CH3.
CH2 = date(CH3) + 60

date_time

1 day = 86400 seconds

Valid time formats

yyyy-MM-dd HH:mm:ss

yyyy-MM-dd HH:mm

yyyy-MM-dd H:mm

yyyy-MM-dd HH

yyyy-MM-dd H

yyyy-MM-dd

yyyy-MM

yyyy-ww

ww

yyyy-M-d HH:mm:ss

yyyy-M-d HH:mm

yyyy-M-d H:mm

yyyy-M-d HH

yyyy-M-d H

yyyy-M-d

yyyy-M

yyyy

年/月/日 时/分/秒

yyyy/MM/dd HH:mm:ss

yyyy/MM/dd HH:mm

yyyy/MM/dd H:mm

yyyy/MM/dd HH

yyyy/MM/dd H

yyyy/MM/dd

yyyy/M/d HH:mm:ss

yyyy/M/d HH:mm

yyyy/M/d H:mm

yyyy/M/d HH

yyyy/M/d H

yyyy/M/d

yyyy/ww

ww

日/月/年 时/分/秒

dd/MM/yyyy HH:mm:ss

dd/MM/yyyy HH:mm

dd/MM/yyyy H:mm

dd/MM/yyyy HH

dd/MM/yyyy H

dd/MM/yyyy

d/M/yyyy HH:mm:ss

d/M/yyyy HH:mm

d/M/yyyy H:mm

d/M/yyyy HH

d/M/yyyy H

d/M/yyyy

MM/yyyy

M/yyyy

yyyy

Last Updated: