Allow MONTH() and DAY() to work with alphabetic sorting
S
Shayne Balding
One of my biggest frustrations with SmartSuite is the lack of ISO 8601 date formats and having to figure out workarounds for the date / time formulas that are available.
One workaround I've done is using something like:
IF(
MONTH([First Created]) < 10,
CONCAT(0, MONTH([First Created])),
TEXT(MONTH([First Created]))
)
But ends up being much longer when needing to do the same for MONTH, DAY, HOUR, MINUTE.
I suppose it might end up being a lot of trouble to mess with the existing MONTH(), DAY(), HOUR(), MINUTE() functions, so maybe some new functions with ISO 8601 format in mind would be a good alternative, or even a function where we could specify our own format would be nice. And making it an option to apply such a format as the default for an entire workspace, solution, or table would be nice too.
Something like:
DATE_FORMAT("yyyy-MM-dd HH:mm:ss")
But with a full complement of formatting options similar to what you would find in Powershell / .Net formatting like you can find here:
https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/get-date?view=powershell-7.4
All of these are output as strings rather than DateTime objects, so they are easily sortable as text.
A
Adam Johnston
Shayne Balding fully agree
Jon Darbyshire
Thank you for posting, Adam Johnston! I have a few more questions for you:
- Can you provide more context on how you currently use the MONTH() and DAY() functions and why the current return format is not meeting your needs?
- Are there specific scenarios or use cases where the proposed change would be particularly beneficial?
- Would you need similar changes to other date-related functions, or is your request specifically for the MONTH() and DAY() functions?
A
Adam Johnston
Jon Darbyshire
1, 2. Using DAY(), MONTH, and YEAR() to append a datestring to the start of a field allows use of alphabetical sorting to put records in date order. That's especially useful in a timeline view, because the Group function there only allows grouping by one field - so if we want to be able to identify the group as well as sort groups by a date (which is pretty common with timeline views), then we need to use a concatenation of the record title and a datestring.
- YEAR() is fine because it already works with alphabetical sorts.