READ_DATE() : convert text to a date (PARSEDATE)

The READ_DATE function reads a date written as text and converts it into a date TimeTonic can use. It is useful when your dates arrive as raw text (import, free text entry, concatenation) and are not recognised as dates.

The key point: the mode and the way you write the format go together. The classic mode expects an uppercase format (DD/MM/YYYY), the extended mode expects a lowercase format (d/m/Y). Mixing the two is the number one reason a formula returns nothing.

Syntax

The third parameter is optional: without it, the classic mode applies.

READ_DATE(text_value, "format")
READ_DATE(text_value, "format", "classic")
READ_DATE(text_value, "format", "extended")

# Parameter Description

1 text_value The text to read: a typed value, a text column, or the result of another function (CONCAT, SUBSTITUTE).

2 format How the date is written in the text, separators included. The way you write it depends on the mode.

3 mode (optional) classic (default) or extended. See the next section.

The two format modes

Mode	How to write the format	Example	When to use it
classic	Uppercase letters: DD, MM, YYYY	"DD/MM/YYYY"	Default mode, for a simple date. Both / and - separators work.
extended	Lowercase letters, PHP conventions: d, m, Y, H, i	"d/m/Y H:i"	To read a date that includes a time, or an unusual format.

Do not mix the two notations. "DD-MM-YYYY HH:mm" with the extended mode does not work, and neither does "d-m-Y" with the classic mode. The format must always follow the convention of the selected mode.

Format characters

Element	classic mode	extended mode
Day	DD	d
Month	MM	m
Year (4 digits)	YYYY	Y
Hour (24 h)	HH	H
Minutes	mm	i

The classic mode also recognises the following formats:

Examples

Read a simple date

The text 01-10-2021 becomes a usable date (01-10-2021 00:00).

READ_DATE("01-10-2021", "DD-MM-YYYY")

Read a date with a time

To read a time, use the extended mode and its lowercase notation. Enable the Time option on the output column to display the time.

READ_DATE("01/10/2021 10:22", "d/m/Y H:i", "extended")

When the text does not use the expected separators

A text such as 25-12-2024 10h50 separates the time with an h, while the format expects a :. Replace the separator with SUBSTITUTE before reading the date.

READ_DATE(SUBSTITUTE($Text, "h", ":"), "d-m-Y H:i", "extended")

Combine with another function

READ_DATE can be nested inside other date functions. Here, the date is rebuilt with CONCAT, then 10 days are added to it.

DATE_ADD(READ_DATE(CONCAT("10", "/10", "/2025"), "d/m/Y", "extended"), 10)

Your formula returns 0 or stays empty

A result of 0 means the date could not be read. Check these four points in order.

The format does not match the mode

In extended mode, write the format in lowercase (d/m/Y H:i). In classic mode, in uppercase (DD/MM/YYYY). This is the most frequent mistake.

The separators do not match the text

The format must reproduce the text exactly, separators included: 10h50 cannot be read with H:i. Fix the text with SUBSTITUTE.

The column output format is not Date

In the Options tab, set the output format to Date, and enable time if you are reading a time. With a Number output, the function displays a timestamp (for example 1633039200).

The text contains extra characters

Leading or trailing spaces, stray text: clean the value with TRIM before reading it.

Go further

FORMAT_DATE

The opposite operation: display a date in the format of your choice.

Read more →

Or continue with

Formula glossary

All available operators and functions, sorted by category.