March 8, 2023 - ConsiderationIll4765 Hello, Im trying to transform a text to date format. I found this formula below on the internet but its “mm/dd/yyyy” format, and I my text its “dd/mm/yyyy”, do someone know to make this change? Thanks!

Ok, one example. Text: 26/03/2023, to date 03/2023. I want a date that I can group by “mm/yyy” and sort this group by ascending. I tried also to slice the numbers from text and concatenate, but when I used this formula to group the sort didnt work (ex: 02/2024 coming before 05/2023), that’s because it was text, and the sort options was alphabetical

Quick answer

<aside> <img src="https://prod-files-secure.s3.us-west-2.amazonaws.com/9210293a-6b0f-4638-8e8d-3c40b40ed42b/b8366055-a446-4861-aa67-1e926814ca74/Invisible.png" alt="https://prod-files-secure.s3.us-west-2.amazonaws.com/9210293a-6b0f-4638-8e8d-3c40b40ed42b/b8366055-a446-4861-aa67-1e926814ca74/Invisible.png" width="40px" /> You can group by "mm/yyyy", you just need to create this format but you wont be able to sort the groups. That's what you said, alphabetical order wont work. Whatever, if you really want to create "mm/yyyy" groups here is the formula.

replace(prop("Text"), "(\\d{2}).(\\d{2}).(\\d{4})", "$2/$3")

What I recommend is to use the "yyyy/mm/dd" format if you can. The alphabetical order will correspond to the chronological order. If you want to sort date or group of date, put always the year first.

If you want to group (by year and month), group by "yyyy/mm" (and not mm/yyyy") then sort by date. For that, indeed, you need to convert your text by date (except if from start you use the "yyyy/mm/dd" format). I give you another formula for that (groups number 2 and 3 are just inverted):

replace(prop("Text"), "(\\d{2}).(\\d{2}).(\\d{4})", "$3/$2")

UPDATE : In fact, if you ****group by "yyyy/mm" you can avoid the creation of a date formula. The groups already sort the entries by years and months. To sort the entries inside a group (by day), the only thing left to do is to sort by Text.

The are several ways to recreate the date, here's one of my way of doing it, the simpler to understand :

We start from an easy date and not from "now()" from which we should remove the hours, minutes, days etc. The starting date is 01/01/1970 00:00 (aka timestamp(0)). This date is not the same everywhere in the world because of time differences. So my formula takes into account the time zone which can interfere in the creation of the date.

Then we add days (which can be found simply by toNumber(prop("Text")) - 1 . No need for slice() or replace().

We add months and then years :

dateAdd(dateAdd(dateAdd(fromTimestamp(toNumber(formatDate(fromTimestamp(0), "Z")) * -3.6e+6), toNumber(prop("Text")) - 1, "days"), toNumber(slice(prop("Text"), 3, 5)) - 1, "months"), toNumber(slice(prop("Text"), 6)) - 1970, "years")

And here is the Regex version :

dateAdd(dateAdd(dateAdd(fromTimestamp(toNumber(formatDate(fromTimestamp(0), "Z")) * -3.6e+6), toNumber(prop("Text")) - 1, "days"), toNumber(replace(prop("Text"), "(\\d{2}).(\\d{2}).(\\d{4})", "$2")) - 1, "months"), toNumber(replace(prop("Text"), "(\\d{2}).(\\d{2}).(\\d{4})", "$3")) - 1970, "years")

</aside>

Text to Date

By @Ouinx2 - **Buy me a Beer**