CODEWITHSUNDEEP
stringdatenotion
Tim Courtney
Tim Courtney

Reputation: 1

Trouble converting a text string to date in Notion

I'm trying to convert a text string into a date in Notion to build a birthday reminder system in my database.

Bigger picture, I want a database view with the birthdays that are coming up in the next month. Some people I know their actual date of birth, and for others I only know their birthday. I enter the birthdate for the people whose birth year I don't know as the current year.

The filters allow you to select a date relative to the present, but not a date whose anniversary is coming up. So to fix this, I am creating a field that reformats the birthdate as a date in the current year, and filtering based on that.

I successfully built the new date, but it is a text string. In order for the filters to work, it needs to be formatted as a date.

I tried formatDate but that gives me an error.

For example:

formatDate(concat(prop("Birthday"), ", ", prop("thisYear")), "MMMM D YYYY")

Gives error:

Type mismatch concat(prop("Birthday"), ", ", prop("thisYear")) is not a Date.

It appears formatDate only reformats an existing date, it doesn't convert a string into a date. I can't find a function that converts a string into a date. How would one do that?

Upvotes: 0

Views: 4676

Answers (1)

Nivienne89
Nivienne89

Reputation: 11

to convert the date you have in text format into a prop date, you have to start from 01 Jan 1970, this is the date Notion use to refer every single date into ms.

With this formula you will have 01 Jan 1970 from now:

dateAdd(dateSubtract(dateSubtract(dateSubtract(dateSubtract(dateSubtract(now(), year(now()) - 1970, "years"), month(now()), "months"), date(now()), "days"), hour(now()), "hours"), minute(now()), "minutes"), 1, "days")

Now you have to add to this date months, year and days you have into your birthday prop.

To do this you simply have to use he dateAdd formula, so you will have:

dateAdd(dateAdd(dateAdd(dateSubtract(dateSubtract(dateSubtract(dateSubtract(dateSubtract(now(), year(now()) - 1970, "years"), month(now()), "months"), date(now()), "days"), hour(now()), "hours"), minute(now()), "minutes"), prop("Giorni"), "days"), prop("Mese") - 1, "months"), prop("Anno") - 1970, "years")

You have to consider that in my example, prop("Giorni"), prop("Mese") and prop("Anno") are already numbers.

If you have them into a text field you first have to extract them with the slice formula and convert them into number with toNumber formula.

I hope I helped you, sorry for my English but I'm not native English speaker.

Bye

Upvotes: 1

Related Questions