If you’ve ever had a date written within a text-based database property, you’ve probably experienced frustration that it is not actually recognized as a date or usable in the way that a date property is. For example, you would not be able to use a text-based date in a filter or to display dates in a calendar view.
However, with some formula magic, we can actually convert the text string into a “real” date that Notion recognizes. Here’s how it’s done.
Extract the year from the text
Using the substring() and replace() functions, we can remove all but numeric characters from the string, then pluck out only the last four digits. This gives us our year. We store this in a property called “Month”.
toNumber(substring(replaceAll(prop("Name"), "[^0-9]", ""), toNumber(length(replaceAll(prop("Name"), "[^0-9]", ""))) - 4))Extract the month from the text
Here we use the if() and contains() functions to check if the string contains characters that match the first three letters of the month name. We also check for lowercase variations. In addition, we can check if the date is written numerically (for example, MM/DD/YYY or MM-DD-YYYY), and if so use the slice() function to pluck out only the first two characters in the string.
if(contains(prop("Name"), "/") or contains(prop("Name"), "-"), toNumber(substring(prop("Name"), 0, 3)) - 1, if(contains(prop("Name"), "Dec") or contains(prop("Name"), "dec"), 11, if(contains(prop("Name"), "Nov") or contains(prop("Name"), "nov"), 10, if(contains(prop("Name"), "Oct") or contains(prop("Name"), "oct"), 9, if(contains(prop("Name"), "Sep") or contains(prop("Name"), "sep"), 8, if(contains(prop("Name"), "Aug") or contains(prop("Name"), "aug"), 7, if(contains(prop("Name"), "Jul") or contains(prop("Name"), "jul"), 6, if(contains(prop("Name"), "Jun") or contains(prop("Name"), "jun"), 5, if(contains(prop("Name"), "May") or contains(prop("Name"), "may"), 4, if(contains(prop("Name"), "Apr") or contains(prop("Name"), "apr"), 3, if(contains(prop("Name"), "Mar") or contains(prop("Name"), "mar"), 2, if(contains(prop("Name"), "Feb") or contains(prop("Name"), "feb"), 1, if(contains(prop("Name"), "Jan") or contains(prop("Name"), "jan"), 0, 0)))))))))))))Extract the day from the text
To get the day from the string, we again use replace() to remove any characters that are not numeric, and extract the digits up to the first space. In addition, we can check if the date is written numerically (for example, MM/DD/YYY or MM-DD-YYYY), and if so use the slice() function to pluck out only the third and fourth characters in the string.
if(contains(prop("Name"), "/") or contains(prop("Name"), "-"), toNumber(substring(replaceAll(prop("Name"), "[^0-9]", ""), 2, 4)), toNumber(replaceAll(prop("Name"), "[^0-9\\s]{1}", "")))Construct the date
Now that we have our year, month and day variables, we can begin to construct the actual date. The critical way that we can turn it into a date is by performing some math on the now() function, which outputs a date and timestamp for right now. Taking the date / timestamp for now, we then subtract using the dateSubtract() function to bring us back to the 1970 Unix Epoch starting point. Read more about why the dates start at Jan 1, 1970.
Once we have re-wound time back to 1970, then we can add back (using the dateAdd() function) the years, months and days that we extracted from the previous steps.
dateAdd(dateAdd(dateAdd(dateSubtract(dateSubtract(dateSubtract(dateSubtract(dateSubtract(now(), year(now()) - 1970, "years"), month(now()) - 1, "months"), date(now()) - 1, "days"), hour(now()), "hours"), minute(now()), "minutes"), prop("Year") - 1970, "years"), prop("Month"), "months"), prop("Day") - 1, "days")Because we started with now(), which is a date object, the end result is formatted as a date! The bonus is that now() already takes into consideration your home timezone, so we don’t have to perform any additional calculations for timezone offset (booyah).
All in one formula
If we want to perform all of these formulas within a single property, we can replace the references to prop(“Year”), prop(“Month”) and prop(“Day”) with the individual formulas above.
dateAdd(dateAdd(dateAdd(dateSubtract(dateSubtract(dateSubtract(dateSubtract(dateSubtract(now(), year(now()) - 1970, "years"), month(now()) - 1, "months"), date(now()) - 1, "days"), hour(now()), "hours"), minute(now()), "minutes"), toNumber(substring(replaceAll(prop("Name"), "[^0-9]", ""), toNumber(length(replaceAll(prop("Name"), "[^0-9]", ""))) - 4)) - 1970, "years"), if(contains(prop("Name"), "/") or contains(prop("Name"), "-"), toNumber(substring(prop("Name"), 0, 3)) - 1, if(contains(prop("Name"), "/") or contains(prop("Name"), "-"), toNumber(substring(prop("Name"), 0, 3)) - 1, if(contains(prop("Name"), "Dec") or contains(prop("Name"), "dec"), 11, if(contains(prop("Name"), "Nov") or contains(prop("Name"), "nov"), 10, if(contains(prop("Name"), "Oct") or contains(prop("Name"), "oct"), 9, if(contains(prop("Name"), "Sep") or contains(prop("Name"), "sep"), 8, if(contains(prop("Name"), "Aug") or contains(prop("Name"), "aug"), 7, if(contains(prop("Name"), "Jul") or contains(prop("Name"), "jul"), 6, if(contains(prop("Name"), "Jun") or contains(prop("Name"), "jun"), 5, if(contains(prop("Name"), "May") or contains(prop("Name"), "may"), 4, if(contains(prop("Name"), "Apr") or contains(prop("Name"), "apr"), 3, if(contains(prop("Name"), "Mar") or contains(prop("Name"), "mar"), 2, if(contains(prop("Name"), "Feb") or contains(prop("Name"), "feb"), 1, if(contains(prop("Name"), "Jan") or contains(prop("Name"), "jan"), 0, 0)))))))))))))), "months"), if(contains(prop("Name"), "/") or contains(prop("Name"), "-"), toNumber(substring(replaceAll(prop("Name"), "[^0-9]", ""), 2, 4)), toNumber(replaceAll(prop("Name"), "[^0-9\s]{1}", ""))) - 1, "days")
