Notion Answers

Question

I've come across a formula that gets the date from a title using a formula. The problem is I also need the full hour, but this formula's standard time is "12:00 AM".
It is a really creative way to do. Here's the initial formula (available too here):

dateAdd(dateAdd(dateAdd(dateSubtract(dateSubtract(dateSubtract(dateSubtract(dateSubtract(now(), year(now()), "years"), month(now()), "months"), date(now()) - 1, "days"), hour(now()), "hours"), minute(now()), "minutes"), toNumber(slice(prop("Name"), 6, 10)), "years"), toNumber(slice(prop("Name"), 0, 2)) - 1, "months"), toNumber(slice(prop("Name"), 3, 5)) - 1, "days")

I managed to slice out the date with this and similarly to the above formula, add it to "now". But I don't have a clue on how to get to the right hour and date (same as the row title):

dateAdd(now(), toNumber(slice(prop("Name"), 14)), "minutes")

Below is the link to the table I'm working on. I know this can be very challenging, but can you guys help me make the time useful too?
Formatting it into DD/MM/YYYY hh:mm would be a plus.
Thanks in advance!

https://www.notion.so/81408aba52ba40e2808d4f5348440e90?v=c93c288d45fe4f468684122622232d4e

Answer 1 · 2022-09-15T09:26:48+0000

0vote

polle Points78510

The formula is using the title itself to generate the date, so "01/11/2022", becomes January 11, 2022, but there is no time, that is why all of them show the default 12:00 AM time. It is just converting the text from one format to another, it is not getting an existing date, because it is not a Date property, it is text.

So as there is no time in the titles, you will not be able to have one unless you modify all the titles in the Notion database to include a time and then be able to continue splitting the title with a formula to get the time, which makes no sense.

It is easier to create a Date property and select the time there. But if you want to use that existing one, then the way to go is to add the time by hand to every single title in the database.

Makes sense?

I believe that you should consider another approach for what you are trying to achieve and sharing that information to understand your needs may let us share a better way to do it.

answered Sep 15, 2022 by polle (78.5k points)

1vote

nutsinbox commented Sep 15, 2022

Yess, I actually have the dates with the times in the same cell from a sheet. The problem is that when copying and pasting them into notion in a Date formatted column, it won't keep the time on them, only the dates.

So keeping them as the title and splitting it would be a great option, since I have many and many rows, and doing them by hand would be > very < time consuming.

I kept an example on the template I shared, but maybe I wasn't clear on my explanation, sorry! English is not my mother tongue.

09/14/2022 23:33:50 should return 09 = month, 14 = day, 2022 = year, 23 = hour, 33 = minutes, 50 = seconds (could be hidden, doesn't matter, I know notion can't handle seconds).

I'm trying to do it for moon phases and its changes. See if this helps you understand:

Sheet table

Thanks for helping again, I appeciate it!

commented Sep 15, 2022 by nutsinbox (290 points)

0vote

polle commented Sep 17, 2022

Notion does not accept dates like that, you have to enable include time and a new field appears for the time, you can't use the same field for date and time combined, those are two different fields to fill.

And as the date you are getting from the title is not a date, it is simple text, I suggest using a real date property with time.

With this I am also trying to explain, that even if you split and split and split a formula to get the "text" to display as you want, that will not be a date with time, it will simply be text.

commented Sep 17, 2022 by polle (78.5k points)

1vote

nutsinbox commented Sep 19, 2022

When you extract it the right way, it does understand it like a date, it even shows up in the calendar (see image below).
When copying from the sheet, even when the column is set as date with time, it doesn't paste anything at all. The only way I could think to solve this, would be really splitting the hour and minutes. But if it's not possible, I understand. Thanks for the help!

commented Sep 19, 2022 by nutsinbox (290 points)

0vote

polle commented Sep 20, 2022

It is not possible, but I'll try to extend this explaining a bit more.

When you copy and paste in Notion nothing gets pasted because of what I explained above, the format and fields are different.

You can understand what I said about the fields in the previous comment if you do the following.

1- Click the date cell to see the date chooser and paste your date with time in the field. You will get and see the error.

2- In the same date chooser activate include time. A new field shows up for the time.

With that you will understand perfectly why when you paste nothing happens, because you are pasting in 1 field what needs to be pasted in 2 different fields.

Hope this helps and make things more clear.

commented Sep 20, 2022 by polle (78.5k points)

Notion Answers

Register & Ask

Get answers

Vote and select answers

Question

Get date AND time from title

Please log in or register to add a comment.