Date manipulation

In this posting I would like to share a solution that was not so obvious to me, so maybe you will find this useful too.
I had asked a question while attending the last formula fitness meeting: when I try to group on a date field, it won’t group because (my) dates are entered (by a formula) as a date time field and therefore most dates in a table will not be exactly the same (date can be the same, but the time portion can be any time during the day).
The suggestion was to use a helper column with a ‘clean’ date (i.e. a date with a time code of 0:00:00), but it was not clear how to get such a date value from an existing date without using an elaborate formula replacing the time part of this date/time field.
It turns out there is a formula to do just that. In the helper column you use the formula: thisRow.LogDate.DateTimeTruncate('day') where LogDate is my column holding the date/time value.


Dear @joost_mineur,

As you mentioned, it’s a great investment to watch the formula fitness video’s as there are always some interesting approaches from where you can benefit.

:bulb: Pro tip: [for everybody learning]

Watch them on YouTube at some higher speed and bookmark the locations where comes something you find interesting.

Then when convenient you can watch at normal speed, slow speed, repeat, so you get the point and practice yourself without being afraid to make mistakes.

It’s a great feeling every time you uncover new possibilities and get more and more in control of your tools.

To my personal opinion a great initiative of Coda and of course the always positive attitude and open mindset of @maria and her guests

In yesterdays session, they made also clear that even for the more experienced users, sometimes it takes some “Playing around” to find the best solution


Thanks for the callout @Jean_Pierre_Traets! I love hosting these sessions.

Another pro tip for folks looking to learn, if you go to the Crowdcast channel you can skip right to where we answer questions by clicking the Ask a Question button on the bottom of the screen. It’s another great way to get to the meaty parts of the conversation. Also, let us know what other fitness sessions you’d like to see!